2

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?

  • 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
5

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?

4

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

  • 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
4

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.

1

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.

  • 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

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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