CODEWITHSUNDEEP
sql
bluesky
bluesky

Reputation: 646

optimizing update statements

I am currently trying to optimize sql code. I would like to know if there is an alternate way of writing these statements, because it seems take a good deal of time to complete. 

Update #TMP---updates  webid when its null in tmp table
Set #TMP.webid_Val='NOT COMPLIANT'
Where  #TMP.webid is null

Update #TMP---updates  PID when its null in tmp table
Set #TMP.PID_Val='NOT COMPLIANT'
Where  #TMP.Pid is null

Update #TMP---Shifts multiple fob situations into storewide
Set #TMP.GMM ='Storewide'
Where  #TMP.gmm like '%, %';

Update #TMP-----Shifts marketing into multiple fob situation
Set #TMP.GMM ='Storewide'
Where  #TMP.gmm like 'Marketing%'


Update #TMP
Set #TMP.OVERALL_Val='NOT COMPLIANT'
Where  #TMP.webid is null

This does have over 22,000 entries.

Upvotes: 1

Views: 102

Answers (2)

pstrjds
pstrjds

Reputation: 17428

The very first piece I see is that you can combine these two update statements:

Update #TMP---updates  webid when its null in tmp table
Set #TMP.webid_Val='NOT COMPLIANT'
Where  #TMP.webid is null

Update #TMP
Set #TMP.OVERALL_Val='NOT COMPLIANT'
Where  #TMP.webid is null

Into:

Update #TMP---updates  webid when its null in tmp table
Set #TMP.webid_Val='NOT COMPLIANT',
    #TMP.OVERALL_Val='NOT COMPLIANT'
Where  #TMP.webid is null

You could combine the two GMM updates into the following:

Update #TMP---Shifts multiple fob situations into storewide
Set #TMP.GMM ='Storewide'
Where  LEFT(#TMP.gmm, 9) = 'Marketing'
OR #TMP.gmm like '%, %';

Performing the LEFT as opposed to the LIKE matching should be a little bit more performant (note: not sure about that, you would have to test it to verify).

Upvotes: 1

UnhandledExcepSean
UnhandledExcepSean

Reputation: 12804

Not positive that this will be faster as it would depend on the data, but a single update statement might perform best.

Update #TMP
Set #TMP.webid_Val=
        CASE
            WHEN #TMP.webid is null THEN 'NOT COMPLIANT'
            ELSE #TMP.webid_Val
        END
     ,#TMP.PID_Val=
        CASE
            WHEN #TMP.Pid is null THEN 'NOT COMPLIANT'
            ELSE #TMP.PID_Val
        END
     ,#TMP.GMM=
        CASE
            WHEN (#TMP.GMM like '%, %' OR #TMP.gmm like 'Marketing%') THEN 'Storewide'
            ELSE #TMP.GMM
        END
    ,#TMP.OVERALL_Val=
        CASE
            WHEN (#TMP.webid is null) THEN 'NOT COMPLIANT'
            ELSE #TMP.OVERALL_Val
        END
WHERE #TMP.webid is null
OR #TMP.Pid is null
OR #TMP.gmm like '%, %'
OR #TMP.gmm like 'Marketing%'

Upvotes: 3

Related Questions