Problem with performance of query,DTS,jobs on SQL server 2005 after migration from 2000

  • Instead of concatenating them, do a left outer join on each column, and "where field is null" type statement in the Where clause.

    Might look something like:

    Update JV

    Set Paid_Flag = 1

    from JV_INVOICE jv

    left outer join V_INVOICE_CSS_TEMP v

    on jv.SOURCE_SYS_ID = v.SOURCE_SYS_ID

    and jv.ACC_NO = v.ACC_NO

    and jv.INV_NO = v.INV_NO

    where jv.source_sys_id = 'CSS'

    and v.ID is null

    You'll have to plug in the correct column name where I have "v.ID is null". That will almost certainly out-perform any concatenation solution.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Viewing post 16 (of 15 total)

You must be logged in to reply to this topic. Login to reply