The following query was executing within 1 minutes for the past 2 years without any issues.
P_STATUS = 'R',
P_ERROR_CODE = 'E000026'
INNER LOOP JOIN AnotherDb.dbo.Trans b ON
(convert(varchar, a.TICKET_NUM) = b.TICKET_NUM and
a.TXN_DT = b.TXN_DT)
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.
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.
is pronounced ree-bar and is a Modenism for R
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Helpful Links:
How to post code problemsHow to post performance problemsForum FAQs