March 28, 2014 at 10:56 am
Hi,
I have a table with clustered index on that. I have only 5 columns in that table. Execution plan is showing that Index scan occurred. Please let me know what are the cause of the Index scan how can we change that to index seek?
I am giving that kind of similar query below
SELECT @ProductID= ProductID FROM Product WITH (NOLOCK) WHERE SalesID= '@salesId' and Product = 'Clothes '
Thanks,
Ramana
March 28, 2014 at 11:26 am
ramana3327 (3/28/2014)
Hi,I have a table with clustered index on that. I have only 5 columns in that table. Execution plan is showing that Index scan occurred. Please let me know what are the cause of the Index scan how can we change that to index seek?
I am giving that kind of similar query below
SELECT @ProductID= ProductID FROM Product WITH (NOLOCK) WHERE SalesID= '@salesId' and Product = 'Clothes '
Thanks,
Ramana
You really haven't provided enough information. Please look at the link referring to Jeff Moden in my signature to help us help you. That being said, I assume that since this is a product table, that the productid is the clustered key. Clustered index contains all of your data. So, since you are filtering on SalesID and it is not the first predicate in the clustered key (also assuming you have no other indexes), it has to scan the clustered index (data) to find what you are looking for. One solution (Assuming it makes sense on all levels), is to add a non-clustered index on Product(SalesID, Product) and include the productID column.
Jared
CE - Microsoft
March 28, 2014 at 11:27 am
First, don't use NOLOCK. It's a poor practice.
Second, an index scan occurs when the query optimizer thinks that this is the fastest way to find data. Without an index that works better, a scan occurs. If you want to avoid a scan, you'll likely need some index on Product or SalesID. Also, your SalesID value looks wrong. Are you somehow building dynamic SQL?
March 28, 2014 at 2:51 pm
SELECT @ProductID= ProductID FROM Product WITH (NOLOCK) WHERE SalesID= '@salesId' and Product = 'Clothes '
Apart from not being SARGable, this query puzzles me, would you ever have a SalesID value of "@salesId"? or Product = "Clothes " with a trailing space? :rolleyes:
As Steve has already mentioned, the dirty read / nolock, I'm not going to overstate the obvious there, but still ask the question, why using this hint? This is something that is justifiable, only if there is a specific problem, not as a blanket protection!
Back to the question, the optimizer may deem an index scan to be less work than other options. This can happen if the table is small, indexes are fragmented or not applicable and so on. To fully work out 1) is it bad 2) can it be fixed, one has to have more information, as Jared said earlier.
Hope this helps.
March 28, 2014 at 2:55 pm
Eirikur Eiriksson (3/28/2014)
SELECT @ProductID= ProductID FROM Product WITH (NOLOCK) WHERE SalesID= '@salesId' and Product = 'Clothes '
Apart from not being SARGable...
All the predicates in that query are SARGable. They're all direct comparisons of a column to an expression (a nonsensical one in one case)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 28, 2014 at 3:11 pm
GilaMonster (3/28/2014)
Eirikur Eiriksson (3/28/2014)
SELECT @ProductID= ProductID FROM Product WITH (NOLOCK) WHERE SalesID= '@salesId' and Product = 'Clothes '
Apart from not being SARGable...
All the predicates in that query are SARGable. They're all direct comparisons of a column to an expression (a nonsensical one in one case)
I may have wrongly jumped to a conclusion here, correct me if I'm wrong, but I think the statement is not SARGable because of Data Type Precedence (MSN: When an operator combines two expressions of different data types, the rules for data type precedence specify that the data type with the lower precedence is converted to the data type with the higher precedence. If the conversion is not a supported implicit conversion, an error is returned. When both operand expressions have the same data type, the result of the operation has that data type.)
My train of thought was that an "id" value would be numeric.
If I am wrong, then sorry;-)
March 28, 2014 at 3:25 pm
INT has a higher precedence than string, the string will convert to int, so if that is an int column (which we don't know), then the query will be essentially
SELECT @ProductID= ProductID FROM Product WITH (NOLOCK) WHERE SalesID= CONVERT_IMPLICIT(INT, '@salesId') and Product = 'Clothes '
Which is SARGable and going to fail with a conversion error.
If you don't believe me, try running this:
IF ('This is a string' = 1)
print 'True'
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 28, 2014 at 3:48 pm
GilaMonster (3/28/2014)
INT has a higher precedence than string, the string will convert to int, so if that is an int column (which we don't know), then the query will be essentially
SELECT @ProductID= ProductID FROM Product WITH (NOLOCK) WHERE SalesID= CONVERT_IMPLICIT(INT, '@salesId') and Product = 'Clothes 'Which is SARGable and going to fail with a conversion error.
If you don't believe me, try running this:
IF ('This is a string' = 1)print 'True'
Guess I'm guilty here:-P neither do I know the data type of the column nor the variable (being passed as char), have to learn to think before talk....:w00t:
Still leaves the original questions unanswered.
Thanks Gail for correcting my wrong her!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply