Slow running stored procedure

Question

Slow running stored procedure

harshada_joshi

SSC Enthusiast

Points: 196
More actions
November 27, 2017 at 8:39 pm

#326519

I am using SQL server R2.
I have stored procedure which takes too much time to execute.
I have posted estimated plane here,
https://www.brentozar.com/pastetheplan/?id=rk6Z6MKxf
Can anyone help me to find out why my stored procedure is slow.
--------------
We have AWS server for testing. Same stored procedure doesn't take much time on our testing server (same Sql server 2008 R2) or our local server (SQL server 2012).
But our clients server it runs too slow.

Viewing 4 posts - 1 through 3 (of 3 total)

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

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

harshada_joshi - Monday, November 27, 2017 8:39 PM
I am using SQL server R2.
I have stored procedure which takes too much time to execute.
I have posted estimated plane here,
https://www.brentozar.com/pastetheplan/?id=rk6Z6MKxf
Can anyone help me to find out why my stored procedure is slow.
--------------
We have AWS server for testing. Same stored procedure doesn't take much time on our testing server (same Sql server 2008 R2) or our local server (SQL server 2012).
But our clients server it runs too slow.

Welcome to SSC Harshada

The estimated plan is no good to anyone, we need the actual plan to see what is actually going on. Please take a read of the 3rd link in my signature for help on posting performance problems.

Also can you confirm what index and statistic maintenance is done on your clients server?

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

How (or why) do you have a stored procedure that includes CREATE FUNCTION statements?!

I'd start by searching this site for string splitter functions. The ones in your codes will run very slow on long strings or large result sets. I'd also consider how the code is laid out, because wading through that isn't a pleasant experience and is likely to discourage people from helping you.

John

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

harshada_joshi - Monday, November 27, 2017 8:39 PM
I am using SQL server R2.
I have stored procedure which takes too much time to execute.
I have posted estimated plane here,
https://www.brentozar.com/pastetheplan/?id=rk6Z6MKxf
Can anyone help me to find out why my stored procedure is slow.
--------------
We have AWS server for testing. Same stored procedure doesn't take much time on our testing server (same Sql server 2008 R2) or our local server (SQL server 2012).
But our clients server it runs too slow.

You have a date range filter which uses crazy "alternative" logic. Change it to something more standard and it's likely to be SARGable (Google it). Does this - '4/27/17' - get passed in as a variable? You want to end up with something like this:
WHERE (@CompareDate IS NULL OR Schedule.StartDate >= @CompareDate) AND (@CompareDate IS NULL OR Schedule.EndDate < DATEADD(DAY,1,@CompareDate))

You have another crazy filter in your WHERE clause which is based on the variable @ChecklistTypeID. I think it can be changed to this:
WHERE (@ChecklistTypeID IS NULL AND Checklist.AudittypeID = 20) OR (Checklist.ChecklistTypeID = @ChecklistTypeID)

Whether or not you're planning to show your code to the world, it's always good to have it nicely formatted and commented for readability.

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