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

In article <01bd47f3$ab35d9a0$55392bd1@reprogle>, Matt Reprogle <reprogle@iquest.net> writes >I have been having problems with a select statement of the type: > >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 If bigtable has n rows this runs the subquery n times. Also it scans every row in BIGTABLE!!! No indexeson BIG TABLE are used. >In one case I looked at, the subquery returns just 13 unique values in >subsecond time, yet it took almost 7 minutes for the main query to >complete. > >On the other hand, if I write out the result of the subquery explicitly, >such as: > >select col1, col2 >from bigtable >where col1 in ('A','B','C','D','E','F','G','H','I','J','K','L','M');> This will use the index on col1.. >the query completes in less than 2 seconds. > >I guess I had the mistaken assumption that the main query treated the >subquery result like an explicit list of the form ('val1','val2',...). > >What could cause the huge performance difference between the two query >forms? > >I am on 7.23 and Solaris 2.5.1, Sun E3000. Try select col1, col2 from bigtable,temp_list_table where bigtable.col1 = temp_list_table.key i.e. do a join not a corelated subquery!! -- 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