Re: Correlated Subqueries

dwalker@randalls.com (Daniel Walker) wrote: >We have had some real crazy instances with some of our programs that >do selects within selects. We are running Informix Version 5.5 on >IBM/RS6000 and the ONLINE_5.0 files state that correlated subqueries >create a temporary table for every row that comes out from the >dominant table. I have seen programs running on my machine that >generate HUGE log entries in the transaction logs. What I see is that >for every row, there is 8 entries in the transaction log. Creating >temporary table (BLDCL) , challoc, ptextend, chfree, free, erase, etc. >This goes on for 100000 record query, it generates over 8 MILLION log >records. Has anyone else run into this and what better ways is their >around this overhead.. > We have also observed this problem with 5.02. Informix will not recognise it as a bug. Some correlated subqueries can be rewritten using a join instead for example select part_name from parts p where exists ( select * from supplier_part sp where p.part_name = sp.part_name and supplier_name = 'John Smith' ) Equivalent query rewritten using join and distinct select distinct part_name from parts p, supplier_part sp where p.part_name = sp.part_name and supplier_name = 'John Smith' Note that you have to use distinct to eliminate the duplicates. Some of the queries can be also rewritten using temp tables. Use the temp table to store result of the subquery and then join the temp table back to the outer table. I recall seeing something about in the FAQ and also in SQL Tutorial. Rafal Czernaiwaski - rafal@magna.com.au Dataspace Consulting Pty Ltd