Power By 专否社区

Creating a Non Clustered Index with a DateTime column as key

Go To StackoverFlow.com

1

I am working on optimizing a query which is used very frequently on our system. The where clause is similar to 

WHERE J.Visibility > 11 
and J.isactive='true' 
and J.isdeleted='false' 
AND (
       CutOffDate > '2012-04-05 00:00:00.000' 
       OR J.CreatedOn > '2011-10-08 00:00:00.000'
    ) 
AND J.Country = 'India' 
AND J.City='Bangalore' 
AND (J.Type > 0 AND J.Type < 230)  
AND J.Category in (20)

Already a NC index(Index1) with City, Type, Visibility, IsActive, IsDeleted existed. I created a new NC index with the above fields but adding CreatedOn, CutOffDate and Category in the beginning as well.

So the keys on new Index (Index2) were City, Category, CreatedOn, CutOffDate, Type, Visibility etc. CreatedOn and CutOffDate were both in descending order. However when I ran the query, the actual execution plan still did an Index Scan of the Index1 rather than Index2. Given the DateTime conditions I would have thought Index2 would be the better choice and would result in Index seek. But that did not happen.

While investigating why this could be happening, I came across this MS article and now I am wondering if indexes with datetime need to be created like mentioned in the article. I did not find this technique of creating indexes with datetime mentioned anywhere else when I searched on Google, thus wondering what do others do?

2012-04-05 21:26
by shashi

2

What is the exact index definition? An index on (City, Category, CreatedOn, CutOffDate, Type, Visibility) cannot be used for your query. In fact, given that you have a criteria that uses an inequality comparison (>) on two fields and and OR condition between them there is no index that could possibly help with the datetime part of the comparison. The article you link has absolutely no relevance for your problem.

For us to propose a good index you would have to tell us exactly the definition of the table, the exact query you use and the cardinality (number of distinct values) in each column.

2012-04-05 21:49
by Remus Rusanu
Well that explains why Index1 was chosen over Index2 and that I was sniffing the wrong trail. But would removing one of the fields and the OR condition help in using a datetime index? The condition on the where clauses vary based on choices user makes in the app. I really won't be able to give all the details here. However, there are over 250,000 rows in the table and CreatedOn > Date leads to around 40,000 rows, which is why I wanted to use index which has CreatedOn. Moreover, http://stackoverflow.com/questions/10028225/which-where-condition-on-datetime-will-be-faster is change i did for tha - shashi 2012-04-05 22:14
Also, would Category in (20,30,40) qualify as equality comparison with OR conditions use an index which has Category - shashi 2012-04-05 22:18
An inequality comparison on a single column may benefit from an index. But is a treacherous path, because of the index tipping point. Most often time series type of data use the time value (eg. CreatedOn) as the leftmost key of the clustered index becuase the vast majority of the queries request data from a specific time interval (ie. range scan - Remus Rusanu 2012-04-05 22:54
Ads