September 24, 2019 at 3:26 pm
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
September 24, 2019 at 3:57 pm
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
September 24, 2019 at 4:26 pm
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.
September 24, 2019 at 4:52 pm
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.
September 24, 2019 at 4:55 pm
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".
September 24, 2019 at 7:07 pm
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
September 24, 2019 at 9:25 pm
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
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply