Conditional Index Usage
-
Hi, I have a stored procedure that is basically as follows: SELECT ColumnA, ColumnB, ColumnC FROM TableA WHERE (@ValueA IS NULL OR ColumnA = @ValueA) AND (@ValueB IS NULL OR ColumnB = @ValueB) AND (@ValueC IS NULL OR ColumnC = @ValueC) So each of the input values can either have a value or is null. If it has a value it has to be included in the filter, otherwise there will not be a filter on that column. Let's say that I have 3 indexes: 1) ColumnA, 2) ColumnA and ColumnB, 3) ColumnA and ColumnC Now on the first run of this sproc, if let's say columnA and ColumnB have values, it will use index 2. On the next runs it will continue using index 2, even if now ColumnA and ColumnC have values (thus actually it should have used index 3). I know that one can tell SQL what index to use using WITH(INDEX XXXX) but can the use of an index be conditioned, or do i have to write 3 different stored procedures?
-
Hi, I have a stored procedure that is basically as follows: SELECT ColumnA, ColumnB, ColumnC FROM TableA WHERE (@ValueA IS NULL OR ColumnA = @ValueA) AND (@ValueB IS NULL OR ColumnB = @ValueB) AND (@ValueC IS NULL OR ColumnC = @ValueC) So each of the input values can either have a value or is null. If it has a value it has to be included in the filter, otherwise there will not be a filter on that column. Let's say that I have 3 indexes: 1) ColumnA, 2) ColumnA and ColumnB, 3) ColumnA and ColumnC Now on the first run of this sproc, if let's say columnA and ColumnB have values, it will use index 2. On the next runs it will continue using index 2, even if now ColumnA and ColumnC have values (thus actually it should have used index 3). I know that one can tell SQL what index to use using WITH(INDEX XXXX) but can the use of an index be conditioned, or do i have to write 3 different stored procedures?
Probably best to use dynamic sql but read this first http://www.sommarskog.se/dynamic\_sql.html
-
Probably best to use dynamic sql but read this first http://www.sommarskog.se/dynamic\_sql.html
-
Hi, I have a stored procedure that is basically as follows: SELECT ColumnA, ColumnB, ColumnC FROM TableA WHERE (@ValueA IS NULL OR ColumnA = @ValueA) AND (@ValueB IS NULL OR ColumnB = @ValueB) AND (@ValueC IS NULL OR ColumnC = @ValueC) So each of the input values can either have a value or is null. If it has a value it has to be included in the filter, otherwise there will not be a filter on that column. Let's say that I have 3 indexes: 1) ColumnA, 2) ColumnA and ColumnB, 3) ColumnA and ColumnC Now on the first run of this sproc, if let's say columnA and ColumnB have values, it will use index 2. On the next runs it will continue using index 2, even if now ColumnA and ColumnC have values (thus actually it should have used index 3). I know that one can tell SQL what index to use using WITH(INDEX XXXX) but can the use of an index be conditioned, or do i have to write 3 different stored procedures?
What I have been taught and understand about SQL Server is this: Let it decide on what indexes to use on the basis that 'SQL Server knows best'. Sometimes SQL Server will decide to do a table scan; as in some cases this is faster than what appears to be the correct index. There will be others out there who know a lot more than me about this. So basically set your indexes up and trust SQL to pick the most appropriate use of indexes (after all the database engine is designed precisely for this reason). I was taught not to use the
gnjunge wrote:
WITH(INDEX XXXX)
for this reason. Regards Guy
You always pass failure on the way to success.
-
What I have been taught and understand about SQL Server is this: Let it decide on what indexes to use on the basis that 'SQL Server knows best'. Sometimes SQL Server will decide to do a table scan; as in some cases this is faster than what appears to be the correct index. There will be others out there who know a lot more than me about this. So basically set your indexes up and trust SQL to pick the most appropriate use of indexes (after all the database engine is designed precisely for this reason). I was taught not to use the
gnjunge wrote:
WITH(INDEX XXXX)
for this reason. Regards Guy
You always pass failure on the way to success.
You are partially right that SQL chooses the right index. But since this is a "conditional" where clause, the first time the sproc runs, SQL chooses the index that is right for that condition. But when the condition changes SQL stays with that same index. (which slows down the sproc) When I cause the sproc to recompile, and use as first run the second condition it chooses the second index as its index, and stays with that index, even when the condition changes. (thus running slow when the first condition happens).
-
You are partially right that SQL chooses the right index. But since this is a "conditional" where clause, the first time the sproc runs, SQL chooses the index that is right for that condition. But when the condition changes SQL stays with that same index. (which slows down the sproc) When I cause the sproc to recompile, and use as first run the second condition it chooses the second index as its index, and stays with that index, even when the condition changes. (thus running slow when the first condition happens).
You can recompile the stored procedure on each run Clickety.
You always pass failure on the way to success.
-
You can recompile the stored procedure on each run Clickety.
You always pass failure on the way to success.
-
Good solution , cause it says that in SQL 2005 I can do statement-level recompilation which fits my needs. Thanks.
-
Good solution , cause it says that in SQL 2005 I can do statement-level recompilation which fits my needs. Thanks.
You're welcome. :) Regards Guy
You always pass failure on the way to success.
-
I wouldnt get into the habit of doing that though. On a complex statement it will take a fair bit of time to analyze the query and build the plan. If you use sp_executesql then SQL Server will cache the plan associated with that statement.
-
I wouldnt get into the habit of doing that though. On a complex statement it will take a fair bit of time to analyze the query and build the plan. If you use sp_executesql then SQL Server will cache the plan associated with that statement.