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 |
|
Auris
Starting Member
7 Posts |
 Posted - 2012-04-10 : 08:42:02
|
| Hi therei have a fairly simple querySELECT DISTINCT col1, col2, col3, col4, col5, col6, col7 FROM MyDataquery returs 3.6 mln records, thats for reporting, it completes in about 2min 45seconds which is awful. So how can i optimize it ? as you can see the distinct is used on 7 columns, i have tryed to create unique non clustered index that has all 7 columns, that reduces the execution to 1 min 45s but still it is a lot, and i take a look at the execution plan for some reason even with an index in place it does index scan, not index seek. Please advice? app is timeouting 2/3 of the time i need to figure this out how to improve the performance. Urgent!Thank you guys.Life is hard. It's even harder if you're stupid. |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-04-10 : 11:00:44
|
without a where clause there is no need for an index seek...because you want to get all data there is nothing to seek.the problem is the DISTINCT.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
Auris
Starting Member
7 Posts |
Posted - 2012-04-10 : 11:27:02
|
ok thanks for the ideas, i have rewritten the entire stored procedure that generated the data for the report, not its way better and yeah you are right the distinct really do suck with that much data. Thanks for quick response.quote:
Originally posted by webfred
without a where clause there is no need for an index seek...because you want to get all data there is nothing to seek.the problem is the DISTINCT.
No, you're never too old to Yak'n'Roll if you're too young to die.
Life is hard. It's even harder if you're stupid. |
|
|
|
Ifor
Aged Yak Warrior
700 Posts |
Posted - 2012-04-10 : 11:36:02
|
| Umm...Do you really produce a report with 3.6 million lines? |
|
|
|
|
|
|
|
|
optimizing disctinct query