September 22nd, 2003, 05:19 AM
Update/Select query to long to complete
i have an requirement to update 250 thousands of records comparing againt another table that have 10 million records
The query is like
Update TABLE1 set COLUMN1 = value
Where Exists ( Select 'X' from TABLE2
Where InStr(sfc_GetPkeyPattern(TABLE1.COLUMN2), '''' || TABLE2.COLUMNX ||'''',1)>0 )
-- The user defined function sfc_GetPkeyPattern retruns a string ( max of 100 char. length ) pattern based on the input values.
if i run this query it takes about 3/4 hrs some time not returing the control at all.
when i tried to execute the query using select with join it took 1 minute to return the result, if use select withs sub query it took the same time ie 3/4 hrs
is there any way to fine tune this update query / what's wrong in this?
Thanks & Regards
September 22nd, 2003, 07:03 PM
The only thing I can think of is that the subquery is having to run 250,000 times. If the join is faster, go with the join.
September 23rd, 2003, 02:57 AM