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 |
|
cognos79
Posting Yak Master
241 Posts |
 Posted - 2008-11-18 : 15:21:50
|
| I have a table with 13 million records. right now it doesnt have any indexes. I am attaching here the query. could you guide me how to decide what columns should be indexed on. this query is taking 10 mins to execute. select sum(Hrs)from dbo.xxxwhere year(excused_dt) = 2005 and mngt_cd = 'K' and person_cd = 1 and RTRIM(CD) IN ('','00','10','12','15','17','20','21','22') |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2008-11-18 : 15:49:11
|
| well it would sort of depend of the selectivity of those columns, but here are a few tips:1. If excused_dt had an index on it, your query couldn't take afvatage of it because of the YEAR function. Instead change to: excused_dt >= '20050101' AND excused_dt < '20060101'2. What data type is CD? Again, by applying a function (RTRIM) you are making ti so SQL cannot use an index on that column. If you can avoid using the trim function then sql can take advantage of an index.3. Given my comments above I'd look at making an index with the following fields: excused_dt, mngt_cd, person_cd, CD and HrsYou can tweak the order of the columns in the index if you that one has a very high selectivity as compared to another column. |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-11-18 : 15:52:05
|
[code]select sum(x.Hrs)from dbo.xxx AS xinner join ( SELECT '' AS cd UNION ALL SELECT '00' UNION ALL SELECT '10' UNION ALL SELECT '12' UNION ALL SELECT '15' UNION ALL SELECT '17' UNION ALL SELECT '20' UNION ALL SELECT '21' UNION ALL SELECT '22' ) AS u ON u.CD = x.CDwhere x.excused_dt >= '20050101' AND x.excused_dt < '20060101' and x.mngt_cd = 'K' and x.person_cd = 1[/code]Clustered index over excused_dt.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
|
|
|
|
|
query tuning