Remove sub-string from WHERE clause

Question

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?

Answer 1

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?

Answer 2

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

Answer 3

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.

Answer 4

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.