Re: Need SQL efficiency help

My first thought is that update is not terribly speedy when using a subquery - so try breaking the query out of the update - pop a result set into a temp table and then update directly from the temp table - if you were running XPS you could use the UPDATE JOIN phrase. What percentage of the table is updated? If this is a high enough number (> 60%ish) you might consider building the updated records in that temp table and then... select a.*, b.some_non_null_column from outer old_tab a, temp_tab b where whatever condition into temp t2 create new_table (mirror image of old table) insert into new table select * from t2 where some_non_null_column isnull { gets you the unupdated ones} insert into new table select * from temp_tab { gets the updated ones } drop old_table rename new_table to old_table This seems like a lot of work to go through, but can bring a 2 hour update operation down to a 10 minute operation, just imagine if you used the high performance loader to the final two moves of data there - would decrease the time factor by another 50% or so. Hmmm, I'll have to benchmark this, figure out where the breakpoints really are, of course I have SO much time for this sort of thing... good luck j. "Rich Newell" <richn@northv.com> on 03/23/98 02:22:34 PM Please respond to "Rich Newell" <richn@northv.com> To: informix-list@iiug.org cc: (bcc: Jack Parker/Boston50/Epsilon) Subject: Need SQL efficiency help Could someone help me with the following SQL?? I wish to make it more efficient update f_daily SET close_price = ((select close_price from f_daily_new where instrument matches "QP*" and stamp between "01/29/98" and "01/31/98" and f_daily_new.stamp = f_daily.stamp and f_daily_new.instrument = f_daily.instrument )) where f_daily.instrument matches "QP*" and f_daily.stamp between "01/29/98" and "01/31/98" and f_daily.instrument in ( select instrument from f_daily_new) and f_daily.stamp in (select stamp from f_daily_new) Thanks Rich