SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Slow running stored procedure


Slow running stored procedure

Author
Message
harshada_joshi
harshada_joshi
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 0
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.
anthony.green
anthony.green
SSC Guru
SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)

Group: General Forum Members
Points: 101334 Visits: 8698
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?



How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


John Mitchell-245523
John Mitchell-245523
SSC Guru
SSC Guru (132K reputation)SSC Guru (132K reputation)SSC Guru (132K reputation)SSC Guru (132K reputation)SSC Guru (132K reputation)SSC Guru (132K reputation)SSC Guru (132K reputation)SSC Guru (132K reputation)

Group: General Forum Members
Points: 132128 Visits: 19225
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
ChrisM@Work
SSC Guru
SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)

Group: General Forum Members
Points: 165572 Visits: 21618
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
Exploring Recursive CTEs by Example Dwain Camps
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search