Query performance change

Question

Query performance change

twoeyed

SSC Veteran

Points: 266
More actions
October 23, 2012 at 4:27 pm

#264549

The following query was executing within 1 minutes for the past 2 years without any issues.
update
dbo.INT_AS_VENTE_TITRE
set
P_STATUS = 'R',
P_ERROR_CODE = 'E000026'
from
Interface a
INNER LOOP JOIN AnotherDb.dbo.Trans b ON
(convert(varchar, a.TICKET_NUM) = b.TICKET_NUM and
a.TXN_DT = b.TXN_DT)
where
a.P_STATUS <> 'R'
and b.TICKET_NUM is not null
and b.TXN_DT is not null
OPTION (maxdop 0)
Since last month, the query has been taking more than an hour everyday, with CPU load at 99-100%. Trans contains in excess of 200million lines and Interface contains around 200 000 lines.
The Trans table has had its clustered index rebuilt, and statistics are regularly updated. I created an additional index with row_id, txn_dt and ticket_num after running tuning advisor, but the statement is still having same behaviour, even after a dbcc freeproccache.
I am running out of ideas.
Please suggest reasons as to why this statement could have changed behaviour so much, and how I could resolve this issue.
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

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

David Webb-CDS SSCoach Points: 17398 More actions · Answer 1

It would help if you posted the DDL for the tables, with indexes and the actual execution plan of the update. Without those, the information you get back might be limited to suggestions which may not hit the mark.

And then again, I might be wrong ...
David Webb

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 2

twoeyed (10/23/2012)
The following query was executing within 1 minutes for the past 2 years without any issues.
update
dbo.INT_AS_VENTE_TITRE
set
P_STATUS = 'R',
P_ERROR_CODE = 'E000026'
from
Interface a
INNER LOOP JOIN AnotherDb.dbo.Trans b ON
(convert(varchar, a.TICKET_NUM) = b.TICKET_NUM and
a.TXN_DT = b.TXN_DT)
where
a.P_STATUS <> 'R'
and b.TICKET_NUM is not null
and b.TXN_DT is not null
OPTION (maxdop 0)
Since last month, the query has been taking more than an hour everyday, with CPU load at 99-100%. Trans contains in excess of 200million lines and Interface contains around 200 000 lines.
The Trans table has had its clustered index rebuilt, and statistics are regularly updated. I created an additional index with row_id, txn_dt and ticket_num after running tuning advisor, but the statement is still having same behaviour, even after a dbcc freeproccache.
I am running out of ideas.
Please suggest reasons as to why this statement could have changed behaviour so much, and how I could resolve this issue.
Thanks

I've seen this and similar hundreds of times. If you look carefully in Books Online, you'll never find an UPDATE there that looks like the one you have. You're updating 1 table from 2 other tables with no join between the table being updated and the source tables. Basically, you have a CROSS JOIN that SQL Server forgave up until the data got big enough to drive it nuts.

The target table of the update ABSOLUTELY MUST be in the FROM clause and properly joined to the other table to work. I wouldn't be suprised to see your times drop to just a couple of seconds or less if you were to write the update as I suggested.

And get rid of that LOOP join hint... it's part of the reason you weren't able to catch this error earlier.

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

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

Jeff's nailed it:

-- If *this* query returns any rows

SELECT 1

from Interface a

INNER JOIN AnotherDb.dbo.Trans b

ON (convert(varchar, a.TICKET_NUM) = b.TICKET_NUM and a.TXN_DT = b.TXN_DT)

where a.P_STATUS <> 'R'

-- and b.TICKET_NUM is not null

-- and b.TXN_DT is not null

OPTION (maxdop 0)

-- Then *this* unrestricted update will run, affecting every row

Update dbo.INT_AS_VENTE_TITRE

set P_STATUS = 'R', P_ERROR_CODE = 'E000026'

- which is probably not the intended behaviour.

^{“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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

I will also note you are joining on mis-matched datatypes (or doing an unnecessary convert), which is also suboptimal.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Gail Wanabee SSCrazy Eights Points: 8471 More actions · Answer 5

This observation may or may not be helpful.

I couldn't help but notice your WHERE clause:

"WHERE

a.P_STATUS <> 'R'

and b.TICKET_NUM is not null

and b.TXN_DT is not null"

All NOT comparisons cause scans instead of seeks. If you can find some way to avoid performing NOT comparisons, it would likely improve the performance of your query, if you have properly implemented supporting indexes. To what degree will depend on the sizes of your tables.

twoeyed SSC Veteran Points: 266 More actions · Answer 6

Thank you all for taking the time to reply and for your valuable inputs.

I have managed to reduce the execution to around 6 minutes through the ideas proposed, which is not bad at all.

Thank you Chris and David for the posting advice.

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

twoeyed (10/26/2012)
Thank you all for taking the time to reply and for your valuable inputs.
I have managed to reduce the execution to around 6 minutes through the ideas proposed, which is not bad at all.
Thank you Chris and David for the posting advice.

Hi

It's good practice to post your final solution - it provides useful feedback to those who may have helped you, may help others who stumble upon this thread, and in some cases encourages another round of improvement.

Cheers.

^{“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

DiverKas SSCrazy Points: 2049 More actions · Answer 8

Lee Crain (10/25/2012)
This observation may or may not be helpful.
I couldn't help but notice your WHERE clause:
"WHERE
a.P_STATUS <> 'R'
and b.TICKET_NUM is not null
and b.TXN_DT is not null"
All NOT comparisons cause scans instead of seeks. If you can find some way to avoid performing NOT comparisons, it would likely improve the performance of your query, if you have properly implemented supporting indexes. To what degree will depend on the sizes of your tables.

This may have been the case at one time, I do not believe this is the case any longer. It was once argued to use NOT EXISTS, but that would only matter if NULLS are considered. In some simple tests, I am getting the same query plan for a IN (criteria) as a NOT IN (criteria) on a 4 million row table. I think the optimizer in 2005 and up is doing an better job than the old version with regards to this.