#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    1
    Rep Power
    0

    Question Update/Select query to long to complete


    Hi,

    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
    Ram
  2. #2
  3. No Profile Picture
    Moderator =(8^(|)
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Feb 2002
    Location
    Sacramento, CA
    Posts
    1,710
    Rep Power
    14
    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.
  4. #3
  5. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    539
    Does it use indexes?

IMN logo majestic logo threadwatch logo seochat tools logo