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.
ColumnAA
? Also, why do you have aWITH (NOLOCK)
in there?