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 |
|
sql-lover
Yak Posting Veteran
99 Posts |
 Posted - 2012-07-03 : 10:35:22
|
This is the 1st time I face this issue, which I find interesting.I do need to optimize the following query but using a WHERE clause is not an option: SELECT EmployeeName, LocationName FROM #appot ORDER BY LocationName, TotalHours DESC It is a temporary table. It's definition as temporary, can't be changed.I tried using few Index designs, including a Cluster one, but it keeps doing a scan (an Index scan this time instead of a table scan)Any advice? Can someone post or suggest a possible Index for this query that can make it run faster or use an Index Seek instead?Thanks, |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-07-03 : 10:46:57
|
| [code]CREATE INDEX ordering ON #appot(LocationName ASC, TotalHours DESC) INCLUDE (EmployeeName)[/code]Easiest way to optimize is not to do ORDER BY. If this is for a report, let the report do the ordering. If it's an application with an API that can sort, let the app do the sorting. |
 |
|
|
sql-lover
Yak Posting Veteran
99 Posts |
Posted - 2012-07-03 : 10:50:33
|
quote:
Originally posted by robvolk
CREATE INDEX ordering ON #appot(LocationName ASC, TotalHours DESC) INCLUDE (EmployeeName) Easiest way to optimize is not to do ORDER BY. If this is for a report, let the report do the ordering. If it's an application with an API that can sort, let the app do the sorting.
Thanks for reply.It is a software that calls a SP (with that slow query inside) and then generates the report.The design you suggested looks like removed the SORT operator, which is good. Still an Index scan. But got the feeling will be faster than a regular table scan.It is my understanding that the temp table won't have too many records. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-07-03 : 10:55:55
|
| Without a WHERE clause you'll get a scan. Scans are not always bad. If you had to find all the Smiths or Volks in the phone book, a seek would be better. But if you wanted to find all the Johns or Roberts, a scan would be better (unless you reindex the phone book by first name). |
|
|
|
sql-lover
Yak Posting Veteran
99 Posts |
Posted - 2012-07-03 : 14:05:06
|
quote:
Originally posted by robvolk
Without a WHERE clause you'll get a scan. Scans are not always bad. If you had to find all the Smiths or Volks in the phone book, a seek would be better. But if you wanted to find all the Johns or Roberts, a scan would be better (unless you reindex the phone book by first name).
True. |
|
|
|
|
|
|
|
|
How to optimize a query with no filter