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


Tunning Sproc for Web Application


Tunning Sproc for Web Application

Author
Message
gaganks
gaganks
SSC Journeyman
SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)

Group: General Forum Members
Points: 89 Visits: 555
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)
David Benoit
David Benoit
SSCrazy Eights
SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)

Group: General Forum Members
Points: 8070 Visits: 3650
Can you post the execution plan as an attachment? Thanks.

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)

Group: General Forum Members
Points: 99049 Visits: 33014
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Mohit K. Gupta
Mohit K. Gupta
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3188 Visits: 1089
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 Smile.

---

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


How to ask for help .. Read Best Practices here.
Attachments
SlowQuerySSC.txt (27 views, 65.00 KB)
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)

Group: General Forum Members
Points: 99049 Visits: 33014
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 Smile.



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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)

Group: General Forum Members
Points: 99049 Visits: 33014
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
gaganks
gaganks
SSC Journeyman
SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)

Group: General Forum Members
Points: 89 Visits: 555
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,
Attachments
Book2.xls (32 views, 59.00 KB)
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)

Group: General Forum Members
Points: 99049 Visits: 33014
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
David Benoit
David Benoit
SSCrazy Eights
SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)

Group: General Forum Members
Points: 8070 Visits: 3650
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

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
gaganks
gaganks
SSC Journeyman
SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)

Group: General Forum Members
Points: 89 Visits: 555
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,
Attachments
WS1.zip (15 views, 22.00 KB)
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