SQL Server Performance

Update Query optimization needed

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by pkv100, Jan 26, 2009.

  1. pkv100 New Member

    Hi,I want to optimize this update statementUPDATE Tab_PTSET PT_Marital_Status = UP.C8,SP_SSN = UP.C9,PT_F_CODE = UP.C10, MODIFYBY_ID = 1 , MODIFYBY_DATE = GETDATE()FROM(SELECT PT_MARITAL_STATUS AS C8,SP_N AS C9, PT_FMP AS C10FROM Tab_CH TMPWHERE OHI_FF_ID = 43 AND EXISTS (SELECT 1 FROM Tab_PT WHERE PT_SSN = TMP.PT_N)AND Reference_Number NOT IN (SELECT MIN(REFERENCE_NUMBER) FROM Tab_CHWHERE OHI_FF_ID = 43GROUP BY PT_N)) UPWHERE PT_SSN = UP.C1No of RecordsTab_PT : 27000Tab_CH : 509Inner query execution time : 1 secupdate query execution time : 80 ( I can not afford more then 5 secs)I even tried optimization tool and create all indexed and updated the statistics also.kindly helpThanks
  2. TommCatt New Member

    The subqueries are killing you. Here is an update statement thatmight help but you didn;t include script to create some test tables andpopulate them with some representative data so there is no way I cantest it. Walk through the code to verify it does what it should (and inparticular joins on the proper criteria) and to get familiar witheverything that is happening.
    update pt
    set PT_Marital_Status = ch.C8,
    SP_SSN = ch.C9,
    PT_F_CODE = ch.C10,
    MODIFYBY_ID = 1,
    MODIFYBY_DATE = GetDate()
    from Tab_PT pt
    join Tab_CH ch
    on pt.PT_SSN = ch.PT_FMP
    and ch.OHI_FF_IF = 43
    join(
    select PT_N, Min( Reference_Number )
    from Tab_CH
    where OHI_FF_ID = 43
    group by PT_N
    )x( PNum, MinRef )
    on ch.PT_N = x.PNum
    and ch.Reference_Number <> x.MinRef
    There is also some indexing that might help but you also don't tell us how your tables are indexed.

Share This Page