| Author |
Topic |
|
ciaran
Starting Member
40 Posts |
 Posted - 2005-02-16 : 06:55:23
|
| Hi all, I have been asked to carry out some optimization on a number of stored procedures which appear to be performing badly. I am an intermediate sql programmer but I have never profiled a stored procedure before. I was wondering if anyone has recently read any good articles on the procedure or have any thoughts on the best way to approach this task. Looking briefly at the code there are a number of nested sub queries that I believe are causing the problem but I want to get some factual data before I go restructuring the code. |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-02-16 : 06:58:02
|
well the execution plan is your best friend.chop the sproc into chunks that you can test easily and optimize them.if you get stuck post here, we'll help.Go with the flow & have fun! Else fight the flow  |
 |
|
|
ciaran
Starting Member
40 Posts |
Posted - 2005-02-16 : 11:41:47
|
| Cool.Thats what I need |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2005-02-16 : 12:39:16
|
...and always look for possible optimizations in the existing code and tune your indexes properly. As one evolves as a developer there are always newer and better ways to do things. A few examples can be to go from cursors to set-based queries, moving from temp-tables to table variables, using locking hints (especially WITH (NOLOCK)) where applicable, derived tables...plenty of things to consider when doing query-tuning. Also when doing optimizations a few features that could come in handy are "Query cost relative to the batch" (unbelivably cool), "set statistics IO" (I hear that 2-4 logical reads for each scan count is where you should be at) and "set statistics time" (the last one I rarely use myself). And keep in mind that a smaller execution plan doesn't always mean better performance..."Query cost relative to the batch" will tell you if the new query is better than the old one. I knew *nothing* about optimizing queries up until about 6-7 months ago and now it's all I think about. Totally awesome what this piece of software can do  EDIT: And I owe it all to sqlteam.com!!! My life would suck without it...--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-02-16 : 12:52:55
|
| although "Query cost relative to the batch" isn't always accurate... i've seen numbers: 622%, 165%, etc... total 100%.Go with the flow & have fun! Else fight the flow |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2005-02-16 : 13:02:26
|
| I've also had some obscure numbers but I've read somehwere that if you keep your statistics updated it should give you a correct result. But you can't trust it blindly...--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
ciaran
Starting Member
40 Posts |
Posted - 2005-02-17 : 05:41:46
|
| Sorry guys you lost me with "Query cost relative to the batch" |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-02-17 : 05:45:46
|
| well if you look at the execution plan for you query you'll see that each operation has some percentage underneath it.that's operation cost relative to the query. you can have more queries in a batch so each query has a relative cost to the whole batch. any clearer?Go with the flow & have fun! Else fight the flow |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2005-02-17 : 05:55:54
|
| It's simple...you put two queries that do the same thing but in different ways in the same QA-window and hit the execution-plan button. Both plans show up but on top of each of them it will show you "Query cost relative to the batch". The top one might say 23% and the bottom one 77%, which tells you that the bottom one is way heavier for the database to perform. But you need to update statistics...just run "UPDATE STATISTICS tablename" for every table in your database and you should be fine...--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
ciaran
Starting Member
40 Posts |
Posted - 2005-02-18 : 09:30:35
|
| Gotcha. Thanks |
|
|
|
|
profile stored procedure