| Author |
 Topic  |
|
|
Auris
Starting Member
United Kingdom
7 Posts |
 Posted - 04/10/2012 : 08:42:02
|
Hi there
i have a fairly simple query
SELECT DISTINCT col1, col2, col3, col4, col5, col6, col7 FROM MyData
query 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
Flowing Fount of Yak Knowledge
Germany
8514 Posts |
Posted - 04/10/2012 : 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
United Kingdom
7 Posts |
Posted - 04/10/2012 : 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
Constraint Violating Yak Guru
476 Posts |
Posted - 04/10/2012 : 11:36:02
|
Umm...
Do you really produce a report with 3.6 million lines? |
|
|
| |
Topic |
|
optimizing disctinct query
