November 17, 2011 at 11:22 pm
The key part here being the "equality". Since (as far as I know) NULLs are considered NON Equality this would explain why the index is not being picked up.
Bingo! You got it. Use Filtered Index if you want to compare on NULL (via index).
WHERE <filter_predicate>
Creates a filtered index by specifying which rows to include in the index. The filtered index must be a nonclustered index on a table. Creates filtered statistics for the data rows in the filtered index.
The filter predicate uses simple comparison logic and cannot reference a computed column, a UDT column, a spatial data type column, or a hierarchyID data type column. Comparisons using NULL literals are not allowed with the comparison operators. Use the IS NULL and IS NOT NULL operators instead.
Here are some examples of filter predicates for the Production.BillOfMaterials table:
WHERE StartDate > '20040101' AND EndDate <= '20040630'
WHERE ComponentID IN (533, 324, 753)
WHERE StartDate IN ('20040404', '20040905') AND EndDate IS NOT NULL
November 18, 2011 at 4:27 am
p-nut (11/17/2011)
agiangone (11/17/2011)
Ninja's_RGR'us (11/17/2011)
My view of index hints is simply that to use it you must somehow think that you're smarter than all the guys at ms combined and all the trillions of tests they made before choosing that path.You might have a case exception, or not.
We can't help in any way without seeing both "good" and "bad" plans. The actual plans, not the estimates.
I feel the same way about hints and that is way I'm trying to understand why the optimizer is behaving as it does in this case.
However, if you read the article posted a couple of posts above (The Tipping point) you see that even someone like Kimberly Tripp is not totally against using them. After all, the same smart people at MS decided to give the users the option to use a hint...
I agree completely, but I think Ninja will too after you mentioned minutes versus milliseconds for the execution time. I think we all know that the optimizer will not be perfect, that's why the hints exist.
Jared
I still don't agree.
I work on reports that process millions of rows in 10-50 tables and it runs in seconds on a freakishly small server.
It takes work, but I never need hints to get there.
Still can't make a call without seing the plans.
This argument (at this early stage) is like saying nolock is the magic go fast button.
It works UNTILL a scan is actually a better plan.
If you REALLLLLLLLLLLLLLLLLLLLLLLLLLLLLLY insist on the hint then you need 2 versions of the code.
#1 for small amount of rows where a seek makes sense
#2 for larger scans.
2 guesses on how that's done!
November 18, 2011 at 9:00 am
Dev (11/17/2011)
The key part here being the "equality". Since (as far as I know) NULLs are considered NON Equality this would explain why the index is not being picked up.
Bingo! You got it. Use Filtered Index if you want to compare on NULL (via index).
WHERE <filter_predicate>
Creates a filtered index by specifying which rows to include in the index. The filtered index must be a nonclustered index on a table. Creates filtered statistics for the data rows in the filtered index.
The filter predicate uses simple comparison logic and cannot reference a computed column, a UDT column, a spatial data type column, or a hierarchyID data type column. Comparisons using NULL literals are not allowed with the comparison operators. Use the IS NULL and IS NOT NULL operators instead.
Here are some examples of filter predicates for the Production.BillOfMaterials table:
WHERE StartDate > '20040101' AND EndDate <= '20040630'WHERE ComponentID IN (533, 324, 753)
WHERE StartDate IN ('20040404', '20040905') AND EndDate IS NOT NULL
Ah! That is exactly what I have done and sure enough the index is being picked up with no hints (someone here will be happy...).
Additionally the index is very light and it works all the times.
If I could only eliminate all the "SET ANSI_PADDING OFF" from the code I would be able to created filtered indexes in production...but that is a different topic.
Until next time, thank you all for your great insight.
Andrea
November 18, 2011 at 9:04 am
Your milage will vary, but I think I've been 'round the block enough to make that call 😉
November 18, 2011 at 9:06 am
Ninja's_RGR'us (11/18/2011)
Your milage will vary, but I think I've been 'round the block enough to make that call 😉
I know I look young on my avatar but I'm a tad bit older than that :hehe:
November 19, 2011 at 6:59 am
Ah! That is exactly what I have done and sure enough the index is being picked up with no hints (someone here will be happy...).
It's not only Remi (correct me if I am guessing wrong name Ninja), many guys here have the same opinion (including me). So you made all of us happy. 😀
November 19, 2011 at 8:53 am
Yes it's Remi.
Viewing 7 posts - 31 through 36 (of 36 total)
You must be logged in to reply to this topic. Login to reply