Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
lappin
Posting Yak Master
182 Posts |
 Posted - 2011-06-07 : 10:33:34
|
| Hi,I have a complicated query which runs in 4 seconds. I've been asked to modify it (add an additional column). The procedure has multiple joins etc. The execution time is now 112 seconds. I've implemented query analyser's suggestions and added an index on the additional column, but it hasn't made any improvement. So I want to compare the execution plans.This is my question: does anyone know of any tools to show differences in the plans, or any good formatting tools to print them out. The xml dumped into Word was 200 pages long. And I tried printing the graphics for quick comparison but it is very unwieldy. Print screen is also very awkward. |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-06-07 : 11:02:17
|
| My guess is that you had an index that was covering and you just need to add the new column (include) to that index.Have a look at the indexes on that table and you can probably guess which one it is.Adding an index on that column probably won't help.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
lappin
Posting Yak Master
182 Posts |
Posted - 2011-06-07 : 11:48:49
|
| It's a while since I did this procedure. I thought I'd used an indexed view as a covering index but I don't seem to have. I will try that because I can't find any covering index. Out of interest - I recently read that SQL Server can use a number of indexes like tables and do joins between them - so I thought an index on the additional column would have been used in this way, but it does not seem so. |
|
|
|
lappin
Posting Yak Master
182 Posts |
Posted - 2011-06-08 : 07:56:12
|
| Got it sorted - the proc joins a number of views which themselves contain views / tables - I added the additional column lower down in the nesting, and it runs at about the same speed. |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-06-08 : 08:28:33
|
| >> I recently read that SQL Server can use a number of indexes like tables and do joins between them It can do but often doesn't - a covering index is always better than using multiuple indexes then lookups and merging.>> the proc joins a number of views which themselves contain views / tables and now you know why this is a bad idea.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-06-08 : 08:55:41
|
| About the tool-part of the question: I've never used it myself but I hear that Plan Explorer from sqlsentry is supposed to be pretty decent. It's free as well so it could be worth a try... (I have nothing to do with them)- LumbagoMy blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/ |
|
|
|
lappin
Posting Yak Master
182 Posts |
Posted - 2011-06-10 : 05:38:38
|
quote:
>> the proc joins a number of views which themselves contain views / tables and now you know why this is a bad idea.
yes I see your point - but doing it this way keeps things in managable sizes and lets me re-use code. If I was to try to put it all in a single proc it would be almost impossible (for me) to maintain. |
|
|
|
|
|
|
execution plan comparison