0

I'm having some problems with the following Query:

SELECT  v.idnum
       ,v.full_name
       ,convert(varbinary(max),s.signature)  as Sig
FROM         AppDB.dbo.v_People1 AS v INNER JOIN
                      OtherDB.dbo.Signatures AS s ON v.idnum = s.idnum

UNION

SELECT  v.idnum
   , v.full_name
   , convert(varbinary(max), s.signature) as Sig
FROM         AppDB.dbo.v_People2 AS v INNER JOIN
                      AppDB.dbo.Signatures AS s ON v.idnum = s.idnum

When i run them each of the queries separately without the union they execute quickly (<5 seconds), but when i run it using the union it's taking forever to execute (infact all it says is executing. I haven't seen it run successfully)

In OtherDB.dbo.Signatures the signature field is a varbinary(max) and in AppDB.dbo.Signatures the field is an image which is why i am using the convert expression. Does anyone know what the problem might be and how i can go about fixing it?

Thanks

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
zSynopsis
  • 4,854
  • 21
  • 69
  • 106

1 Answers1

1

I wonder if it's because you are using a UNION instead of a UNION ALL. UNION by itself will remove duplicate rows between the data sets. This may be causing a delay on your blob field. See if there is a difference using UNION ALL.

Brettski
  • 19,351
  • 15
  • 74
  • 97
  • This fixed it. Thanks! Why would checking to see if a row was duplicate make it so much slower though? There were only 40 rows within the first query and around 10 rows within the second – zSynopsis Dec 17 '09 at 17:46
  • 1
    It has to do with your blob field, they are not really comparable (can't use a DISTINCT on them either), so it made sense the UNION would choke on it. I can look around for a more technical explanation. – Brettski Dec 17 '09 at 17:56