March 9, 2009 at 12:09 pm
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)
March 9, 2009 at 12:40 pm
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
March 9, 2009 at 12:50 pm
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 9, 2009 at 3:52 pm
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[/url].
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:[/font]
March 10, 2009 at 4:51 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 10, 2009 at 5:00 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 10, 2009 at 12:16 pm
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,
March 10, 2009 at 12:34 pm
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 10, 2009 at 12:44 pm
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
March 11, 2009 at 9:28 am
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,
March 12, 2009 at 5:39 am
Hi,
I just got chance to look at these plans .. All were with the last part of the query because of the parameters passed in:
1) You need to modify your uk_user_name index to make it a covering Index on Portal_User.
- You can do this by dropping index and adding a new index.
- In new index add INCLUDE (BUSINESS_UNIT)
- This will make it so SQL Server does not have to do a Key_Look up in Worstcase Secnario.sqlplan.
2) No changes in WS1.sqlplan but...
- If you are selecting all the case id from form do you need to do a join? Or your form table can contain more case ID then just what is in case table?
- Why do this? You are joining the two tables in your main select any how? And not filtering this here.
- if you take this away you are now free from doing another join later in the statement.
3) No changes WS4.sqlplan.
2) WS2.sqlplan:
- Is FieldValue table a catch all table? (i.e. instead of having a look up table for each of your fields like region, cust_legal_name, isattachment, anti_mon_rev, etc.)
- Statements like (SELECT ID FROM FIELD_TYPE WHERE FIELD_NM = 'REGION' ) are only returning one ID value correct? I would capture them at the start of the procedure into variable and then use it in my select. So having to avoid doing so many nested joins (not that nested joins bad ;-)). It will turn into a index seek/scan depending on the selectivity.
- In your Case table is there a clustered key? If so what field is clustered? Because it is causing multiple Hash Joins which can be expensive.
- What is your Field_ID in FieldValue sorted by (aka the cluster key)?
hmmm thats it for now I'll dig more when I have some more time *_*.
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
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:[/font]
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply