Query runs slow sometimes and sometimes runs superfast (2 minutes vs 2 seconds)

Question

Post reply

Query runs slow sometimes and sometimes runs superfast (2 minutes vs 2 seconds)

skb 44459

SSCarpal Tunnel

Points: 4768
More actions
November 5, 2021 at 2:30 pm

#3948353
This query runs very slow at times and very fast at time.
What could cause this behavior?
i checked the slowness is coming from auditLog table. Its a big table as it stores audit records.
But still cant figure out why its very slow at times.

select * from
(
SELECT coitem.[ship_site]
,custaddr.[name]
,coitem.[co_num]
,coitem.[co_line]
,coitem.[item]
,coitem.[description]
,coitem.[qty_ordered]
,coitem.[due_date]
,(coitem.[Price] * coitem.qty_ordered ) AS ExtPrice
,co.[taken_by]
,co.[order_date]
,ISNULL(AuditLog.CreateDate, SalesSignOff.CreateDate ) AS SalesEnggSignOffDate
,item.family_code
,item.Uf_EngRelease AS EngReleaseStatus
,coitem.Uf_COLinePlanningNotes
FROM coitem(NOLOCK)
JOIN co (NOLOCK) ON co.co_num = coitem.co_num
JOIN custaddr (NOLOCK) ON custaddr.cust_num = co.cust_num AND custaddr.cust_seq = 0
JOIN item (NOLOCK) ON item.item = coitem.item
LEFT OUTER JOIN dbo.AuditLog (NOLOCK) ON dbo.AuditLog.RecordDate > co.CreateDate AND MessageType = 10474 AND KeyValue = (co.co_num + '-' + CAST(coitem.co_line AS VARCHAR(10)) + '-0' ) AND NewValue = item.item AND OldValue LIKE 'new%'
LEFT OUTER JOIN dbo.AuditLog (NOLOCK) SalesSignOff
ON SalesSignOff.RecordDate >= co.CreateDate
AND SalesSignOff.MessageType = 10081
AND SalesSignOff.KeyValue = co.co_num
AND SalesSignOff.OldValue = 'P' AND SalesSignOff.NewValue = 'O'
WHERE co.stat = 'O' AND coitem.stat = 'P' and coitem.ship_site = 'Oshk'
) aa
- This topic was modified 3 years, 1 month ago by skb 44459.

Viewing 7 posts - 1 through 6 (of 6 total)

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

skb 44459 SSCarpal Tunnel Points: 4768 More actions · Answer 1

i checked the slowness is coming from auditLog table. Its a big table as it stores audit records.

ScottPletcher SSC Guru Points: 101119 More actions · Answer 2

What indexes do you have on that table?

Looks like an index on ( MessageType, NewValue, KeyValue, RecordDate ) INCLUDE ( CreateDate, OldValue )

might be useful for this query.

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".

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 3

Good info above.

Depending on plans as well, you could have parameter sniffing if parameters change. Or a plan could get ejected and a new one come in. If you have query store, you should be able to see if plans change.

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

skb 44459 wrote:

But still cant figure out why its very slow at times.

It's probably because the triggers that feed it are written incorrectly as some form of dynamic SQL that materialize the INSERTED and DELETED tables. I've run into this exact Audit table dozens of times and use to have the url from the article from this very forum that suggested it on the bulls-eye of a dart board.

Check the triggers that feed the audit table and see if I'm right.

The other part of the issue is may be that the data you're looking for is no longer in cache and so it has to load the data to query it.

The worse part about the "columnar" audit tables is that any non-clustered indexes on them are going to be nearly as big as the clustered indexes and they're going to need to be maintained in order to get any speed out of them because they're going to fragment like the dickens.

My other recommendation is to not use a join to the audit table along with all the other joins. Store the data from the other tables into a Temp Table and then go lookup the data you need from the audit table. Divide'n'Conquer methods can help quite a bit here. Not always, but frequently.

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

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 5

This is not going to allow index usage in the join: AND KeyValue = (co.co_num + '-' + CAST(coitem.co_line AS VARCHAR(10)) + '-0' )

Further to @JeffModen's idea - create a temp table from the other tables, combining co.co_num, coitem.co_line into a pre-defined KeyValue column in the temp table, then join using that predefined column.

I also noticed that you are joining to AuditLog twice - and in both cases you have an open-ended range for RecordDate. It might be a good idea to figure out the date range you want to search for matching records.

Looking at what you are pulling - it might be a better option to use OUTER APPLY to get the audit records. If you are expecting only a single row to be returned from the audit log for each LEFT OUTER JOIN - then a TOP 1 with an ORDER BY on the RecordedDate might even be better...not sure though.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

ScottPletcher SSC Guru Points: 101119 More actions · Answer 6

Jeffrey Williams wrote:

This is not going to allow index usage in the join: AND KeyValue = (co.co_num + '-' + CAST(coitem.co_line AS VARCHAR(10)) + '-0' )

SQL can use an index for the KeyValue column, it just can't do an index seek for it. SQL can still add a predicate to compare that column as long as the column appears somewhere in the index.

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".