May 6, 2009 at 12:01 pm
Why sql server does not utilize the index on a column if the column is used in a function in where clause. e.g WHERE COALESCE(Id, 0)
Is it possible to force sql server to use the index even if the column is used in a function??
(modifying the where clause is not an option in my scenario)
May 6, 2009 at 12:04 pm
Why can't you modify the query?
May 6, 2009 at 12:09 pm
You can for SQL Server to use a particular index by using 'Index Hints'
For e.g. Select * from Tab_Name WITH (INDEX(Index_lala))
WHERE ColName = 'yoyo'
Sql server might not be using Index because either your statistics r not updated or Sql Server thinks that the query plan generated by using your Index will create additional resources.(Bad query plan)
May 6, 2009 at 12:16 pm
Lynn Pettis (5/6/2009)
Why can't you modify the query?
becuase it is embeded in a third party dll, which i can not modify.
May 6, 2009 at 12:18 pm
Do an online search for "SARGABLE" with regard to indexes and where clauses. There are some very good articles on the subject. I believe the coalesce will prevent an index seek, because of what those articles go over.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 6, 2009 at 12:18 pm
Mayank Khatri (5/6/2009)
You can for SQL Server to use a particular index by using 'Index Hints'For e.g. Select * from Tab_Name WITH (INDEX(Index_lala))
WHERE ColName = 'yoyo'
)
sorry i should have said, "modifying query is not an option becuase it is embeded in a dll, and we donot have its source code"
Sql server might not be using Index because either your statistics r not updated or Sql Server thinks that the query plan generated by using your Index will create additional resources.(Bad query plan)
Statistics are up to date, but its a behaviour of sql server that it will never utilize index if the column is in funtion.
May 6, 2009 at 12:35 pm
mmhhh... you can't touch the code and SQL Server does not supports function based indexes; that moves your issue from the "problem" to the "feature" category -most probably you can do nothing.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 6, 2009 at 12:37 pm
Yes, this is correct, for most case if a search condition wraps a function around a column, then SQL cannot use an index on that column to resolve that search-condition (could still use it for others though).
Trying to force it with a query hint will either do nothing or make it worse. (so don't so that!)
The reason is simple: the index was built from the physical values from that column, but a function on that column produces non-reversible values that are different from the original values stored in the index. The only way that SQL has of resolving something like:
WHERE @par1 = SQUARE(col1)
Is to apply the function to the column value of every candidate record (i.e., Scan). The general 'fix' is for the human involved (that's us) to rewrite the condition in inverse form, like so:
WHERE SQRT(@par1) = col1
Why can't SQL Server do this for you? Because for the vast majority of cases, this inverse transformation is not strictly correct and could produce different results. For instance, the change that I made above actually eliminates half of the possible matches (the negative values), but as "the human" I can posit that that is OK, becuase col1 will never be negative, which is something that SQL Server cannot know.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 6, 2009 at 12:46 pm
Thanks guys, i guess i have to live with it, but thanks for the explanations.
May 6, 2009 at 1:06 pm
Sorry, I can't think of a good way around it either.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply