Update statement is very slow recently

Question

Update statement is very slow recently

amyfgehring

Old Hand

Points: 388
More actions
September 24, 2019 at 3:26 pm

#3682965
Hello. About 2 weeks ago, a process that took about 5-10 minutes started to take longer than 5 hours. I have narrowed down the really slow part to an update statement that uses three views. When I run the statement as a SELECT is runs in about 8 seconds. But as an update it just keeps going. I added "WITH(NOLOCK)" to the query, and while that sped it up as a SELECT statement, it hasn't helped as an UPDATE. We checked and no new indexes or triggers have been added lately. If anyone has any ideas I'd appreciate it.
```
      UPDATE R
      SET SSTATUSCODE = 'PIF'
      FROM RESURGENT_STATUS_CODE R  WITH(NOLOCK)
JOIN MAIN..CLAIM_CLOSE_CODE_VIEW C  WITH(NOLOCK) ON R.NCLAIMID = C.NCLAIMID 
LEFT JOIN MAIN..CLAIM_LAST_NSF_VIEW N WITH(NOLOCK) ON N.NCLAIMID = R.NCLAIMID 
LEFT JOIN MAIN..CLAIM_LAST_REVERSAL_VIEW RV WITH(NOLOCK)ON RV.NCLAIMID = R.NCLAIMID
      WHERE R.SSTATUSCODE IS NULL 
AND C.SCMTCODE = 'CZPIF' 
AND (N.NCLAIMID IS NULL OR N.DTLASTNSF < C.DTCREATEDON) 
AND (RV.NCLAIMID IS NULL OR CAST (RV.DTLASTREVERSAL AS DATE) <= CAST (C.DTCREATEDON AS DATE)
```
Thank you,

Amy

Viewing 7 posts - 1 through 6 (of 6 total)

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

drew.allen SSC Guru Points: 76985 More actions · Answer 1

WITH(NOLOCK) is not a magic "go faster" code. It reads dirty data. You almost certainly don't want to read dirty data when making an update.

Beyond that, there's not a whole lot of advice that we can give here. We don't know the definitions of your views, so we can't optimize those. You haven't included the execution plan (actual rather than estimated), so we can't even see where the issue might be. We don't know what indexes you have in place or how or whether they are being used. We simply don't have enough data.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

amyfgehring Old Hand Points: 388 More actions · Answer 2

My coworker is a big believer in WITH (NOLOCK) and insisted I put them in every query. I'll have to tell him what you said.

Anyway, I have made some progress. Thank you for at least looking. I knew there was little anyone could do but was hoping for something magical. I just created an index. No magic.

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 3

This is equivalent SQL to yours, but instead of checking the left join column in the WHERE to be NULL or equal to something else, you might as well check them in the ON.

UPDATE R
   SET SSTATUSCODE = 'PIF'
  FROM RESURGENT_STATUS_CODE R
 INNER JOIN MAIN..CLAIM_CLOSE_CODE_VIEW C
         ON R.NCLAIMID = C.NCLAIMID 
        AND C.SCMTCODE = 'CZPIF' 
  LEFT JOIN MAIN..CLAIM_LAST_NSF_VIEW N
         ON N.NCLAIMID = R.NCLAIMID 
        AND N.DTLASTNSF < C.DTCREATEDON
  LEFT JOIN MAIN..CLAIM_LAST_REVERSAL_VIEW RV
         ON RV.NCLAIMID = R.NCLAIMID
        AND CAST(RV.DTLASTREVERSAL AS DATE) <= CAST(C.DTCREATEDON AS DATE)
 WHERE R.SSTATUSCODE IS NULL

It would be helpful if you can include the execution plan and the DDL of the tables.

ScottPletcher SSC Guru Points: 101098 More actions · Answer 4

One should always try to avoid a function on a table column being used for lookup. Therefore, for the last JOIN, do this instead:

  LEFT JOIN MAIN..CLAIM_LAST_REVERSAL_VIEW RV WITH(NOLOCK)
         ON RV.NCLAIMID = R.NCLAIMID
        AND RV.DTLASTREVERSAL < DATEADD(DAY, 1, CAST(C.DTCREATEDON AS DATE))

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

drew.allen SSC Guru Points: 76985 More actions · Answer 5

ScottPletcher wrote:

One should always try to avoid a function on a table column being used for lookup. Therefore, for the last JOIN, do this instead:
  LEFT JOIN MAIN..CLAIM_LAST_REVERSAL_VIEW RV WITH(NOLOCK)
         ON RV.NCLAIMID = R.NCLAIMID
        AND RV.DTLASTREVERSAL < DATEADD(DAY, 1, CAST(C.DTCREATEDON AS DATE))

This is generally true, but CASTing a DATETIME field to DATE is still SARGable. Of course, this does not apply if the field is (N)(VAR)CHAR instead of DATETIME.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 6

drew.allen wrote:

ScottPletcher wrote:
One should always try to avoid a function on a table column being used for lookup. Therefore, for the last JOIN, do this instead:
  LEFT JOIN MAIN..CLAIM_LAST_REVERSAL_VIEW RV WITH(NOLOCK)
         ON RV.NCLAIMID = R.NCLAIMID
        AND RV.DTLASTREVERSAL < DATEADD(DAY, 1, CAST(C.DTCREATEDON AS DATE))
This is generally true, but CASTing a DATETIME field to DATE is still SARGable. Of course, this does not apply if the field is (N)(VAR)CHAR instead of DATETIME.
Drew

Even though it is SARGable, there have been several articles that do demonstrate that it's slower. It's not nearly as bad as a non SARGable query, but it is slower from what I've read.

Disclaimer: I've not tried it myself because I won't write queries that even look non SARGable if I can help it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)