Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a stored procedure that is using the SUBSTRING function in the WHERE clause.

SELECT DISTINCT
   ColumnBB,
   ColumnCC
FROM TableAA WITH (NOLOCK)
WHERE SUBSTRING(ColumnAA, 1, 17) = @VariableA;

How can I prevent an index scan for this query and make it execute quickly?

share|improve this question
1  
Do you have an index on ColumnAA? Also, why do you have a WITH (NOLOCK) in there? –  Nick Chammas Mar 15 '12 at 19:30
    
Why do you want to avoid (=prevent) an index scan? –  a_horse_with_no_name Mar 15 '12 at 21:12
    
Garry, did any of the answers here solve your problem? –  Nick Chammas Jun 8 '12 at 16:58

4 Answers 4

If you can't modify the schema per db2's answer, then try:

WHERE 
       ColumnAA LIKE @VariableA + '%' 
   AND LEN(@VariableA) >= 17          -- safety check

Note that if @VariableA is shorter than 17 characters, then you may match unwanted strings using just a LIKE. For example, 'The quick brown fox jumped over the lazy dog.' is LIKE 'The%', but that doesn't satisfy the requirements of your original SUBSTRING query.

Aaron also brings up a good point that the DISTINCT may be the ultimate culprit. Why do you have it in there? Is it something that should be replaced with an appropriate unique index?

share|improve this answer

Are the substring ranges always the same? If so, you could turn that into a computed column, and index it.

share|improve this answer
    
The substring value is always the same. I cannot make any index or column changes to the DB. I need to be able to handle in the TSQL. –  Garry B Mar 15 '12 at 19:19

Replace it with like and see how it goes:

SELECT DISTINCT
   ColumnBB,
   ColumnCC
FROM TableAA (NOLOCK)
WHERE ColumnAA like @VariableA + '%'

like is faster than substring in this case (proof).

The newer predicate is not semantically the same as the old one, but as far as your requirements go the results should be as expected given you provide correct @VariableA values.

share|improve this answer
1  
You can add the substring as a secondary check if needed. The like should use the index, and then the substring would be applied. –  BillThor Mar 16 '12 at 1:48
2  
@NickChammas - I can't reproduce this difference when running against a string variable in a loop SELECT @c = CASE WHEN SUBSTRING(@String,1,1) = 'J' THEN 1 END so seems it might something specific to a predicate against a column. –  Martin Smith Mar 16 '12 at 18:47
3  
@ErikE - I just tested that one. LIKE was twice slower –  Martin Smith Mar 16 '12 at 20:22
2  
@ErikE - Complete test results from my end here. I take it that you've tried something similar already? –  Martin Smith Mar 16 '12 at 22:10
1  
@Martin I've never actually tested any of this. It was just a gut feeling based on the code required to accomplish the two different ways. I don't like to see global assertions that contain unproven assumptions. I try to be careful about that. :) –  ErikE Mar 16 '12 at 23:47

I suspect the scan is caused by the DISTINCT (and the resulting sort). Does it go away without the DISTINCT? Wonder if you can fool the optimizer into dealing with that separately, e.g.

;WITH x AS 
(
  -- the suggestion I gave you on twitter:
  SELECT ColumnBB, ColumnCC
  FROM dbo.TableAA
  WHERE ColumnAA LIKE @VariableA + '%'
)
SELECT ColumnBB, ColumnCC 
FROM x
GROUP BY ColumnBB, ColumnCC;

I think the optimizer will see through this and won't necessarily short-circuit, but it could be worth a try.

If you want to improve performance, your best bet is to be open to schema changes. E.g. the clustered index scan may also be prevented if you add ColumnCC as an INCLUDE column to the index on ColumnBB. That will almost certainly help this query, but it may or may not be better for other queries.

share|improve this answer
    
You mean add both CC and BB as includes to the index on AA, right? Or, if the DISTINCT is necessary, add CC and BB as part of the key to the index on AA. –  Nick Chammas Mar 15 '12 at 20:27
    
Yep, sorry. Would have kept better track with real column names I suspect. –  Aaron Bertrand Mar 15 '12 at 20:34

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.