Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query performance change Expand / Collapse
Author
Message
Posted Tuesday, October 23, 2012 4:27 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 3:04 AM
Points: 20, Visits: 45
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
Post #1376278
Posted Tuesday, October 23, 2012 5:02 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 4:43 PM
Points: 897, Visits: 7,109
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
Post #1376282
Posted Tuesday, October 23, 2012 8:02 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:17 AM
Points: 36,800, Visits: 31,261
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1376295
Posted Wednesday, October 24, 2012 5:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:56 AM
Points: 7,137, Visits: 13,527
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1376419
Posted Thursday, October 25, 2012 10:30 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 2:57 PM
Points: 4,321, Visits: 6,115
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 at GMail
Post #1377120
Posted Thursday, October 25, 2012 2:14 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 4:35 PM
Points: 259, Visits: 1,086
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.
Post #1377283
Posted Friday, October 26, 2012 1:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 3:04 AM
Points: 20, Visits: 45
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.
Post #1377417
Posted Friday, October 26, 2012 7:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:56 AM
Points: 7,137, Visits: 13,527
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1377560
Posted Friday, October 26, 2012 1:13 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 16, 2013 8:28 AM
Points: 249, Visits: 460
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.
Post #1377805
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse