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 12»»

Tunning Sproc for Web Application Expand / Collapse
Author
Message
Posted Monday, March 9, 2009 12:09 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 25, 2014 8:10 AM
Points: 39, Visits: 528
Hi All,

I have an issue where my sproc needs to be tunned. Now its executed within 54 sec and feteched 24,000 records. The Application team complaints that the time taken to generate the Web page gets delayed.

They want the sproc to take the same time as it was earlier.(in past it was taking 25 secs.now that there has been a Data increase)

Can someone give ideas as to how to tune this Sproc ..

All Indexs (clustered and nonclusted are in place and fine in the exceution plan)

Kindly Advice ASAP.

Many Thanks,
*************************************

Plz find the attachment in below post(Mohit's attachement using Redgate Tool)
Post #671838
Posted Monday, March 9, 2009 12:40 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 5:04 PM
Points: 2,107, Visits: 3,581
Can you post the execution plan as an attachment? Thanks.

David

@SQLTentmaker
SQL Tentmaker
“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Post #671877
Posted Monday, March 9, 2009 12:50 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 12:27 PM
Points: 15,541, Visits: 27,919
Wow. I'm amazed that runs in 54 seconds.

Without reading every single line, the one thing I'm noticing is, you don't really have the concept of JOINS down. Take this:

SELECT ID  FROM PORTAL_USER WHERE ID IN  
(SELECT DESCENDANT_USER_ID FROM PORTAL_USER_HIERARCHY WHERE
ANCESTOR_USER_ID IN
(SELECT TO_USER_ID FROM PORTAL_USER_RELATIONSHIPS
WHERE FROM_USER_ID IN
(SELECT DESCENDANT_USER_ID FROM PORTAL_USER_HIERARCHY WHERE ANCESTOR_USER_ID IN

(SELECT ID FROM PORTAL_USER WHERE [USER_NAME] = @USER_NAME)) --AND RELATION_TYPE_ID= 2
)
--AND
--LEVEL_NMBR>0
)

) )--AND IS_OUTSTANDING = 0 changed because sales user could not see saved requests defect id 968


You'd be better off with something like this, which is much more readable as well as more likely to get good indexes

...SELECT  ID
FROM PORTAL_USER pu
JOIN PORTAL_USER_HIERARCHY puh
ON pu.ID = puh.DESCENDANT_USER_ID
JOIN PORTAL_USER_RELATIONSHIPS pur
ON puh.ANCESTOR_USER_ID = pur.TO_USER_ID
...

The same thing for all those sub-SELECTS within the WHERE clause.

Do you have an actual execution plan for the query? That will show you where you're getting table or index scans instead of seeks. This can be caused by the code (entirely possible) or by improper indexes.

Also, since you say it has degraded over time, have you updated statistics on these tables and defragmented the indexes?


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #671886
Posted Monday, March 9, 2009 3:52 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: Thursday, July 24, 2014 4:22 PM
Points: 942, Visits: 1,062
Hey ..

People here on SSC are great in helping, but when posting such large SQL code blog. It makes it difficult for them to help you. Strongly recommend you reformat the code and attach the SQL file ...

I just ran your code through RedGate Refactor tool .. hopefully someone can help you now :).



---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN.
Microsoft FTE - SQL Server PFE

* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing.


How to ask for help .. Read Best Practices here.


  Post Attachments 
SlowQuerySSC.txt (17 views, 65.37 KB)
Post #672016
Posted Tuesday, March 10, 2009 4:51 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 12:27 PM
Points: 15,541, Visits: 27,919
Mohit (3/9/2009)
Hey ..

People here on SSC are great in helping, but when posting such large SQL code blog. It makes it difficult for them to help you. Strongly recommend you reformat the code and attach the SQL file ...

I just ran your code through RedGate Refactor tool .. hopefully someone can help you now :).



Thanks Mohit. Very good point. I love Red Gate & their tools.

Anyway, thanks to Mohit's work, I noticed that you're joining the tables in the final select, but you're not using ANSI 92 joins.

Three more issues jump out. First, you're working through the data in a non-set based approach. Loading data into temporary tables and then joining temporary tables together for output by it's nature is moving the data around multiple times. The trick would be to bring the data together in a single select statement. Also, you're using table variables and you said you're dealing with thousands of rows. Table variables don't have statistics, so that's going to slow down the query quite a bit. You'd be better off, if you had to stick with this approach, using temporary tables, #temp, instead of table variables, @temp. Finally, with the IF statements breaking up the execution of the query so much, you're going to get recompiles every time this thing executes. That's adding to the overhead and time of the query. A better approach is to create a wrapper procedure that does all the flow control IF statements that then calls to other procedures from within it.

To really see what's happening though, you need to post the actual execution plan (not estimated). Can you do this?


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #672284
Posted Tuesday, March 10, 2009 5:00 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 12:27 PM
Points: 15,541, Visits: 27,919
I suspect using OPEN TABLE is the issue. If you go out & bounce around in Google, there are a lot of people experiencing problems with it. I couldn't find anything that said whether or not it uses a different connection than the TSQL query window, but I think it must.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #672289
Posted Tuesday, March 10, 2009 12:16 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 25, 2014 8:10 AM
Points: 39, Visits: 528
Hi Mohit/David/Grant,

Thanks all for the kind help.
Mohit i will take care in future post in a better format,help people understand.


I have the execution plan as attched. But no idea how to analyse the plan.

Thanks again,


  Post Attachments 
Book2.xls (8 views, 59.00 KB)
Post #672683
Posted Tuesday, March 10, 2009 12:34 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 12:27 PM
Points: 15,541, Visits: 27,919
Can you create a graphical execution plan? An actual plan please, not estimated. Save it to a file as .sqlplan and then zip that attach it to a message. If confused here's a video.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #672692
Posted Tuesday, March 10, 2009 12:44 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 5:04 PM
Points: 2,107, Visits: 3,581
Nice link Grant!

Gaganks - Wondering if you would be willing to edit your original post and remove the really long script that is in there and just reference the attachment that Mohit supplied. It would make coming to this post a lot less painful.

Thanks.


David

@SQLTentmaker
SQL Tentmaker
“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Post #672703
Posted Wednesday, March 11, 2009 9:28 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 25, 2014 8:10 AM
Points: 39, Visits: 528
Hi All,

I have the actual Exec plan attached.

This the worst case (With max.data load condition)

(i.e
DECLARE @return_value int

EXEC @return_value = [dbo].[pr_Case_Get_Cases]

@USER_NAME = N'ashahi',

@USER_ROLE = N'pom',

@BUSINESS_UNIT = N'bmg',

@OPENONLY = 0

SELECT 'Return Value' = @return_value
)
Thanks,


  Post Attachments 
WS1.zip (8 views, 22.19 KB)
Post #673383
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse