Hi.
I have a little gap here.
You say that "if "dtm IS NULL", that row is selected, regardless of the ShowTime value."
But we have used an index on dtm, so shouldn't it be used?
Sorry but I can't really grasp the implementation here.
To put it another way.
If we do:
Select Bookingnumber from EOrderReminderConsent where dtm is null.
Would this do a scan? We have an index on dtm and on bookingNumber.
An index may or may not be used, based on how many rows SQL estimates will use the index and if the index contains all the columns needed by the query.
If possible, it's always best to limit by the clustering key(s) if you can, because SQL has a very strong preference for using clus key(s).
Hmmm.
So doing the above:
Select Bookingnumber from EOrderReminderConsent where dtm is null
might result in and index scan regardless that we have indexed dtm?
And that may be a correct behavior?
Thanks.
It's what I am implying, that a row count might result in a scan that may be better than a seek at that query.
Especially if you use clustered index.
So this:
select id from EOrderReminderConsent
will produce a scan because it reads straightforward, but this:
select id from EOrderReminderConsent
where ShowTime <= DATEADD(MINUTE, 145, GETDATE()) and ShowTime > GETDATE()
will produce a seek because it "cuts" the page straightforwardness so it sees an index and tries to implement it?
thanks and sorry for the strange questions but for me, indexing is a very hard and expanded field
Another Question.
We are currently used the clustered index on Id and showtime.
Since the showtime will get values that are of 0 to 45 minutes period, is it preferred to remove the showtime from the clustered index and add it to a non clustered with a 0 to 45 datetime?
Is this better or worse?
Thanks.
Hmm.
Actually it seems I cannot filter with expressions (like DATEADD).
You're contradicting yourself over and over, I can't keep up. You say the table is clustered on id, then you say it's clustered on ShowTime, then it's clustered on both. I give up.
Clustering is critical for best performance. A table is clustered (mostly) by the first column in the clus index, that's what determines whether or not seeks can be done. So, if a table is clustered in ( Id, Col2 ), we say it's clustered on Id or on Id and Col2, but we do not say just that the table is "clustered on Col2".
Good luck with your project.
2 Likes
Hi.
Sorry if I made you lose your patience.
I was actually doing some test, that is why I was using different indexes.
The main index is the one that has the ID and showtime clustered.
I was wondering if by removing the showtime from the clustered index and using it with a filter in a non cluster will impact performance up or down.
Again sorry if I made you upset.
If you query by ShowTime, that should be the first column in the clustered index. Forget about highly-generic "rules" about "narrow, ever-increasing, blah blah blah".
If you (almost) always query by ShowTime, then:
ShowTime should be the first column in the clustered index on that table, period.
1 Like
Hi.
Ok thanks.
But I guess if a column was not so important as to be unique in a clustered index then we could filter it?
So if showtime was not as important as to be a clustered index (with id + showtime) and was a secondary column that just needed a non cluster index, then we could filter it? Or it does not make a difference?
If so, from what I can see I cannot use expressions on the filter index. I was using "dateadd" and it was not supported.
Again this is theoretically as the showtime will be used as a clustered index. I am just talking about IF we wanted to index a date column then using a filter on a clustered index, will make a difference in performance or not.
Theoretically, a nonclus index can help. But the result has to be a very small percentage of total row (really pages) for SQL to actually use it. It can (rarely) be as high as 10% of the total rows still use the nonclus index, or it can be as low as less than 1%. And it's rather hard to tell which it will be without really understanding what's going on.
Even when SQL does use the nonclus index, you risk what's called the "tipping point". Your nonclus index works fine for a month, or even 6 months. Then the result set gets larger, and SQL suddenly decides "I can't use the nonclus index anymore, it's too much overhead, so I'll just scan the whole table instead." You never know when that will hit.
That's why it's better, if possible, to use a clus index for very common searches.
I had that issue you are talking about in some thread we where discussing about a year ago.
The result set was doing scans from a date and before.
Thanks for all the valuable info. Sorry if I tired you but indexes are very complicated for me if you peal the relatively straightforward outer surface. Very interesting but very complicated 