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 |
|
cornall
Posting Yak Master
148 Posts |
 Posted - 2009-01-19 : 09:48:17
|
| Hi,I have a query that ran on 2000 in 35 seconds and in 2005 takes 19 mins!In the execution plan it says Hash Match (Flow Distinct) takes up 99% of the execution time/resource.Can anyone point me in the right direction with optimising this query!Cheers D |
|
|
cornall
Posting Yak Master
148 Posts |
Posted - 2009-01-19 : 09:49:55
|
| I now get Sort (Distinct Sort) Cost 100% on 2005. |
 |
|
|
cornall
Posting Yak Master
148 Posts |
Posted - 2009-01-19 : 09:52:14
|
| Ok getting a bit further...In 2000 the query returns unsorted and in 2005 the query is sorted. I guess I will have to check all my joins and see if one has a sort!??! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-19 : 09:53:33
|
| are you using any windowing functions or any other operator which internally causes a sort? |
|
|
|
cornall
Posting Yak Master
148 Posts |
Posted - 2009-01-19 : 09:57:47
|
| I didn't write the query unfortunatly so I will need to look at all the views it joins upon. Is there any way to find what is causing the sort? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-19 : 10:40:25
|
| just hover over sort icon and see details about column and predicates |
|
|
|
cornall
Posting Yak Master
148 Posts |
Posted - 2009-01-19 : 11:29:35
|
| It says it is sorted by a whole bunch of expressions.Exp 1545 etc etc. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-01-19 : 15:05:30
|
Post a link to the saved exeution plan (*.sqlplan)
E 12°55'05.63"N 56°04'39.26" |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
Query Optimization