Re: Performance question

>>>>> "markp7832" == markp7832 <markp7832_at_my-deja.com> writes:

    markp7832> I also work with Oracle and it is my understanding that
    markp7832> the diffence between an exists clause and an in clause
    markp7832> is that the exists sub-query is fired once for every
    markp7832> row returned in the main query while the in clause
    markp7832> sub-query is executed once for the statement.

It is a couple of years since I worked with Oracle, but I suspect that the "in" also has to be fired for each row of the main query if it is correlated. In fact if it isn't, it will return the wrong result set. Oracle may have some good optimisation that allows non-correlated sub-queries to be fired only once, that would certainly be nice. However, wouldn't this also be true of the "exists" clause???

In Sybase, the "exists" sub-clause returns true as soon as it finds a match. So for an "in" Sybase will return the complete set of rows for the sub-query and then check to see if the parent column is "in" the returned set, the "exists" clause returns as soon as the first one is located. Clearly, if the result set of the sub-query is large, the "exists" is going to be much faster. If there is only one row, the timing is negligible. The timing is also pretty similar if no rows are returned for the sub-query, as you might expect.

-- 
David Owen     Midsomer Consultants Inc.      dowen_at_midsomer.org