update query is running very slow

Question

update query is running very slow

bobrooney.81

SSC-Addicted

Points: 468
More actions
February 20, 2020 at 10:14 am
Go to Answer
#3727300

HI All,
I am new to sql tuning. I have this below update statement which is taking more than an 1 hour. Why it is taking long time or is there a way to speed things up?
UPDATE [dbo].[Test_tbl]
set SQL_DETAIL='SELECT ROWID_OBJECT, HUB_STATE_IND, MDM_ID, SEQ_NM, MSP_END_CUST_NM, SEQ_TYP_CD, OST_NM, ALIAS_NM, STATUS_CD, DUNS_NMBR, C3_DUNS, BO_CLASS_CODE, C6, C6_NM2, C6_NM3, LEGAL_NM, EVENT_TYP, PROCESS_STATUS, ADDR_LN1, ADDR_LN2, ADDR_LN3, ADDR_LN4, CITY, STATE_CD, STATE_NM, COUNTY, COUNTRY_CD, COUNTRY_NM, REGION, POSTAL_CD, POSTAL_EXT_CD, ADDR_CLEANSE_DESC, ADDR_STATUS_CD, ACCOUNT.WEBSITE, ACCOUNT.ID FROM TAB1 INNER JOIN TAB2 ACCOUNT ON ROWID_OBJECT = C_B_ACCOUNT.SEQ_ID WHERE C3 <> ''Lead'' AND C3 <> ''C6-Customer'' AND C_B_ACCOUNT.IS_MERGED IS NULL AND C_B_ACCOUNT.SURF_ID IS NOT NULL AND C3 <> ''Entity'' AND C3 <> ''Vendor'' AND C4 <> ''Inactive'' AND C3 <> ''Inactive-Former Customer'' AND C2 <> ''0'' AND C1 = ''Organization'' '
where [ROWID]='ABC.1jQR6 '
GO
Here is some notes
================
> no blocking
> Table has only 256 rows
> there is an non-clsutered idx created on the filtered column
> below is the table structure
CREATE TABLE [dbo].[Test_tbl]
(
[ROWID] [nchar](14) NOT NULL,
[SQL_DETAIL] [nvarchar](4000) NULL,
[SEQ] [bigint] NOT NULL,
[CREATOR] [nvarchar](50) NULL,
[CREATE_DATE] [datetime2](7) NULL ,
[UPDATED_BY] [nvarchar](50) NULL ,
[LAST_UPDATE_DATE] [datetime2](7) NULL
)
GO
CREATE NONCLUSTERED INDEX [NI_Test_tbl_ROWID] ON [dbo].[Test_tbl]
(
[ROWID] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
Estimated plan

Viewing 15 posts - 1 through 15 (of 20 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Since SQL_DETAIL and ROWID are defined as nchar and nvarchar, you might try putting an N in front of the strings in your query. I can't see that the absence thereof is causing the slowness, however. What wait types are you seeing while the query is running? Has this happened only once, or every time? What is the original value of SQL_DETAIL for the row in question? Could it be that you're waiting for the data file to grow to accommodate the new data?

John

bobrooney.81 SSC-Addicted Points: 468 More actions · Answer 2

Hi John,

Thank you for the response. I haven't captured the wait type information One more thing is, the query finished its execution after 1 hr 45 mins approx.

My doubt was that, there is only 1 qualifying based on the filter and why it was taking so long time.

Regards,

Bob

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

A picture of a plan isn't a plan, all the good stuff is in the properties.

However, assuming that estimated plan is correct, and you're getting a clean index seek, then it's likely the issue may simply be I/O throughput. If you can, capture the actual plan (extended events is your buddy here) to see how that compares to the estimated plan, especially in terms of row counts. Then, focus on I/O. A single row update should be relatively painless. Emphasis on should. Unless you're just moving a ton of data around, in which case, hardware may be the solution, not query tuning.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

I know that it won't seem like it should make any difference but try the following and see if it does...

UPDATE tt

set SQL_DETAIL='SELECT ROWID_OBJECT, HUB_STATE_IND, MDM_ID, SEQ_NM, MSP_END_CUST_NM, SEQ_TYP_CD, OST_NM, ALIAS_NM, STATUS_CD, DUNS_NMBR, C3_DUNS, BO_CLASS_CODE, C6, C6_NM2, C6_NM3, LEGAL_NM, EVENT_TYP, PROCESS_STATUS, ADDR_LN1, ADDR_LN2, ADDR_LN3, ADDR_LN4, CITY, STATE_CD, STATE_NM, COUNTY, COUNTRY_CD, COUNTRY_NM, REGION, POSTAL_CD, POSTAL_EXT_CD, ADDR_CLEANSE_DESC, ADDR_STATUS_CD, ACCOUNT.WEBSITE, ACCOUNT.ID FROM TAB1 INNER JOIN TAB2 ACCOUNT ON ROWID_OBJECT = C_B_ACCOUNT.SEQ_ID WHERE C3 <> ''Lead'' AND C3 <> ''C6-Customer'' AND C_B_ACCOUNT.IS_MERGED IS NULL AND C_B_ACCOUNT.SURF_ID IS NOT NULL AND C3 <> ''Entity'' AND C3 <> ''Vendor'' AND C4 <> ''Inactive'' AND C3 <> ''Inactive-Former Customer'' AND C2 <> ''0'' AND C1 = ''Organization'' '

FROM [dbo].[Test_tbl] tt

where [ROWID]='ABC.1jQR6 '

--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)

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 5

since I couldn't see the bottom of the plan, very stupid question - are there any triggers on the table?

MVDBA

bobrooney.81 SSC-Addicted Points: 468 More actions · Answer 6

bobrooney.81

SSC-Addicted

Points: 468

February 21, 2020 at 4:23 am

#3727544

No Triggers. But its a Heap Tbl.

bobrooney.81 SSC-Addicted Points: 468 More actions · Answer 7

Jeff Moden wrote:

I know that it won't seem like it should make any difference but try the following and see if it does...
UPDATE tt
set SQL_DETAIL='SELECT ROWID_OBJECT, HUB_STATE_IND, MDM_ID, SEQ_NM, MSP_END_CUST_NM, SEQ_TYP_CD, OST_NM, ALIAS_NM, STATUS_CD, DUNS_NMBR, C3_DUNS, BO_CLASS_CODE, C6, C6_NM2, C6_NM3, LEGAL_NM, EVENT_TYP, PROCESS_STATUS, ADDR_LN1, ADDR_LN2, ADDR_LN3, ADDR_LN4, CITY, STATE_CD, STATE_NM, COUNTY, COUNTRY_CD, COUNTRY_NM, REGION, POSTAL_CD, POSTAL_EXT_CD, ADDR_CLEANSE_DESC, ADDR_STATUS_CD, ACCOUNT.WEBSITE, ACCOUNT.ID FROM TAB1 INNER JOIN TAB2 ACCOUNT ON ROWID_OBJECT = C_B_ACCOUNT.SEQ_ID WHERE C3 <> ''Lead'' AND C3 <> ''C6-Customer'' AND C_B_ACCOUNT.IS_MERGED IS NULL AND C_B_ACCOUNT.SURF_ID IS NOT NULL AND C3 <> ''Entity'' AND C3 <> ''Vendor'' AND C4 <> ''Inactive'' AND C3 <> ''Inactive-Former Customer'' AND C2 <> ''0'' AND C1 = ''Organization'' '
FROM [dbo].[Test_tbl] tt
where [ROWID]='ABC.1jQR6 '

Will there any kind of performance benefits if we use table aliasing ?

bobrooney.81 SSC-Addicted Points: 468 More actions · Answer 8

Grant Fritchey wrote:

A picture of a plan isn't a plan, all the good stuff is in the properties.
However, assuming that estimated plan is correct, and you're getting a clean index seek, then it's likely the issue may simply be I/O throughput. If you can, capture the actual plan (extended events is your buddy here) to see how that compares to the estimated plan, especially in terms of row counts. Then, focus on I/O. A single row update should be relatively painless. Emphasis on should. Unless you're just moving a ton of data around, in which case, hardware may be the solution, not query tuning.

Hi Grant,

I want to know little bit about I/O throughput ? What is it? How do we measure it. how to tell it is good or bad?

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 9

looks like this is Grant's winning argument to Extended events. , but I will advise that you need to know your hardware setup before you measure disk I/O and whether it is read or write...

is it possible that there is another application on the server that is stealing your I/O? - sometimes people put sage or exchange on a sql server (never recommended)

MVDBA

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 10

For I/O, here are a couple of articles on how to track it and understand it. I'd still recommend getting a copy of the execution plan plus runtime metrics (aka, actual plan) which you can capture using Extended Events.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

How big is this 256-row table? What's the pattern of usage?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

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

My guess is that there is another process reading the table with a lock on it and running all the SQL commands contained in the SQL_DETAIL column. Your UPDATE statement might be waiting for the job to finish.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 13

bobrooney.81 wrote:

Jeff Moden wrote:
I know that it won't seem like it should make any difference but try the following and see if it does...
UPDATE tt
set SQL_DETAIL='SELECT ROWID_OBJECT, HUB_STATE_IND, MDM_ID, SEQ_NM, MSP_END_CUST_NM, SEQ_TYP_CD, OST_NM, ALIAS_NM, STATUS_CD, DUNS_NMBR, C3_DUNS, BO_CLASS_CODE, C6, C6_NM2, C6_NM3, LEGAL_NM, EVENT_TYP, PROCESS_STATUS, ADDR_LN1, ADDR_LN2, ADDR_LN3, ADDR_LN4, CITY, STATE_CD, STATE_NM, COUNTY, COUNTRY_CD, COUNTRY_NM, REGION, POSTAL_CD, POSTAL_EXT_CD, ADDR_CLEANSE_DESC, ADDR_STATUS_CD, ACCOUNT.WEBSITE, ACCOUNT.ID FROM TAB1 INNER JOIN TAB2 ACCOUNT ON ROWID_OBJECT = C_B_ACCOUNT.SEQ_ID WHERE C3 <> ''Lead'' AND C3 <> ''C6-Customer'' AND C_B_ACCOUNT.IS_MERGED IS NULL AND C_B_ACCOUNT.SURF_ID IS NOT NULL AND C3 <> ''Entity'' AND C3 <> ''Vendor'' AND C4 <> ''Inactive'' AND C3 <> ''Inactive-Former Customer'' AND C2 <> ''0'' AND C1 = ''Organization'' '
FROM [dbo].[Test_tbl] tt
where [ROWID]='ABC.1jQR6 '
Will there any kind of performance benefits if we use table aliasing ?

I wouldn't have suggested it if I didn't believe so. And, no, it's not a panacea but try it and see if it helps. It costs you nearly nothing to try and, so, if it doesn't work, you've lost virtually nothing. If it does work, you have a solution to your problem.

It normally works the best for solving the problem of "illegal updates". This is where the target table of a JOINed update is not included in the FROM clause. In most cases, I use such aliasing even in simple, non-joined UPDATEs just because it's the "consistent" habit I've formed. I have no explanation as to why it it will sometimes work a seeming miracle for performance when it's not used to fix an "illegal update". For "illegal updates", it fixes the problem of "Halloweening" even though there are claims that work tables prevent them.

--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)

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 14

I fell foul of this recently and jeff's trick cured my issue.. worth a try and although it's not required by ansi 92 syntax it is a good practice to use

MVDBA