Re: SELECT subquery much slower than IN ( list...)???

In article <35061d0f.4753268@news.monmouth.com>, David Kosenko <dkosenko@monmouth.com> writes >David Williams wrote: > >+ Matt Reprogle wrote: >+>select col1, col2 >+>from bigtable >+>where col1 in >+> (select key from temp_list_table); >+> >+ Foreach row in big table >+ get value of col1 (A) >+ run the subquery and get the results (B) >+ check if a in B >+ end foreach >[edited] > >This is absolutely incorrect. Later in your response you tell >this poster to avoid a coorelated subquery - which this query >is not. A correlated subquery would have a where clause in >the subselect that included an expression referencing a column >from the "master" table. > Ok, I was tired... Look just do a proper join, such subqueries are really just a join anyway. I suspect the optimizer is just crap at subqueries.. >The way the query, as written, would work is: > Execute subquery into result set > For each row in big table > get value of col1 (A), test if it is in result set > end foreach > >The subquery as written is functionally the same as a literal >set. Why is it taking so much longer? A couple of ideas: > >It was stated that the subselect returns 13 or so unique values, >yet the subquery does not specify UNIQUE values. So rather >that testing against a set with 13 members, it is testing against a >set of X members, where X is the number of rows in the temp table >queried in the subselect. Since an IN is reduced to a series of ORs >by the optimizer, you wind up with a lot more conditions to test. The >optimizer will not eliminate a comparison to a literal string even if it >happens to be redundant, as it does not KNOW it is redundant. >Another possibility depends on whether or not the big table is >fragmented by expression or not. If it is, the use of a literal set would >allow the optimizer to do fragmentation elimination. With the subselect, >since the set members are not derived until runtime, it cannot include >fragment elimination in the plan, and thus all fragments would be scanned. >This could result in a significant difference in I/O, and thus query time. > -- David Williams Maintainer of the Informix FAQ Primary site (Beta Version) http://www.smooth1.demon.co.uk Official site http://www.iiug.org/techinfo/faq/faq_top.html I see you standin', Standin' on your own, It's such a lonely place for you, For you to be If you need a shoulder, Or if you need a friend, I'll be here standing, Until the bitter end... So don't chastise me Or think I, I mean you harm... All I ever wanted Was for you To know that I care