I have 3 main types of documents in my application: Purchase Orders
(POs), Order Acknowledgements (OAs), and Invoices.  To list all "open"
documents, I constructed the following SQL UNION statement:

 SELECT CAST('PO: ' AS CHAR(15)) AS DocType,
        po.DocNum, po.VendCode AS ContactCode,
        po.VendCompany AS Company, po.DocDate
 FROM POHeaders po WHERE po.DocStatus = 'Open'

 UNION

 SELECT CAST('OA: ' AS CHAR(15)) AS DocType,
        oa.DocNum, oa.CustCode AS ContactCode,
        oa.CustCompany AS Company, oa.DocDate
 FROM OrderAckHeaders oa WHERE oa.DocStatus = 'Open'

 UNION

 SELECT CAST('Invoice: ' AS CHAR(15)) AS DocType,
        inv.DocNum, inv.ContactCode,
        inv.CompanyName AS Company, inv.DocDate
 FROM InvoiceHeaders inv WHERE inv.DocStatus = 'Open'


Each table only has a few thousand rows and this query brings back a
total of 6,320 open documents.

Executed individually, each sub-select statement returns in less than
half a second.  But UNIONed together, they take over 200 seconds!  And
this is against a local database.

Is there a better way to structure the query?  I added a "DocStatus"
index on each table and tried it again, but it didn't help.  I ran the
query about 3 times to make sure I didn't hit a random slow moment.

I also looked at the SQL Manual for UNIONs and didn't see anything
additional that I should be doing. I'm surprised that joining three
nearly instantaneous queries would slow it down this significantly.

Any ideas?

--
David Cornelius
CorneliusConcepts.com

David,


<< Each table only has a few thousand rows and this query brings back a
total of 6,320 open documents.

Executed individually, each sub-select statement returns in less than half
a second.  But UNIONed together, they take over 200 seconds!  And this is
against a local database. >>

If you're returning sensitive result sets for the individual SELECT
statement test, then you can't really compare the two.  However, if you
could send me the data that you're using (database catalog also, please), I
can take a look and tell you what is going on.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

> If you're returning sensitive result sets for the individual SELECT
> statement test, then you can't really compare the two.  However, if you
> could send me the data that you're using (database catalog also, please),
> I can take a look and tell you what is going on.

Thanks for looking at this--I've sent the file to you.

I'm just now learning about "sensitive result sets."  I've never
specifically declared or checked the sensitivity of result sets.  In my
original post, the UNION statement I made was simply a concatenation of the
individual SQL SELECT statements I had used in my test--I didn't do anything
different or declare a cursor or anything.  Perhaps I should?

--
David Cornelius
CorneliusConcepts.com

David,

<< Thanks for looking at this--I've sent the file to you. >>

The issue is that the UNION operator is going to need some further
optimization.  Right now it's brute-forcing it, and the result is quite a
few row reads during the UNION.

<< I'm just now learning about "sensitive result sets."  I've never
specifically declared or checked the sensitivity of result sets.  In my
original post, the UNION statement I made was simply a concatenation of the
individual SQL SELECT statements I had used in my test--I didn't do anything
different or declare a cursor or anything.  Perhaps I should? >>

No - a UNION result set is always insensitive, so it doesn't make any
difference.

--
Tim Young
Elevate Software
www.elevatesoft.com

> The issue is that the UNION operator is going to need some further
> optimization.  Right now it's brute-forcing it, and the result is
> quite a few row reads during the UNION.

OK--no problem.  It was easily broken up and displayed separately.

> << I'm just now learning about "sensitive result sets."  I've never
> specifically declared or checked the sensitivity of result sets.  In
> my original post, the UNION statement I made was simply a
> concatenation of the individual SQL SELECT statements I had used in
> my test--I didn't do anything different or declare a cursor or
> anything.  Perhaps I should? >>
>
> No - a UNION result set is always insensitive, so it doesn't make any
> difference.

OK--thanks.


--
David Cornelius
CorneliusConcepts.com