--- In firebird-support@yahoogroups.com, Robert martin <rob@...> wrote:

>
> Hi
>
> I am wanting to do two queries to see it an 'ItemRef' is in use in
> either of two ways. I know I can run two separate queries or I can run
> one query which is a UNION of the two (See below). Which will give me
> better performance, running two simple queries or one more complex

query.

>
> Please note that I am assuming most of the time this query will return
> no results so I don't anticipate a big saving from having to run only
> one of the two simple queries.
>
>
> SELECT FIRST 1 sb.StkBinRef
> FROM Stockbin sb
> JOIN Item i ON i.ItemTypeRf = sb.ItemTypeRf
> WHERE sb."Active" = 'T'
> AND i.ItemRef = 34
> UNION
> SELECT FIRST 1 sbi.StkBinRef
> FROM StockbinItem sbi
> JOIN StockBin sb2 ON sb2.StkBinRef = sbi.StkBinRef
> WHERE sb2."Active" = 'T'
> AND sbi.ItemRef = 34

UNION does a 'DISTINCT' operation on the two sets of data that have
been brought together. Not that sorting two records is going to be a
measurable overhead, but UNION ALL avoids this sorting.

The direct answer to your question is that there is more overhead if
you issue it as two separate queries (more round trips to the server).
If you convert it to a stored procedure, the plan gets cached, so it
becomes even cheaper.

Adam