Improve the performance of a "reverse" LIKE query

Question

I have a query that looks similar to this:

SELECT  CustomerId
FROM    Customer cust
WHERE   'SoseJost75G' LIKE cust.ClientCustomerId + '%' -- ClientCustomerId is SoseJost 

The jist of what this does is, I get a value from the customer that is my ClientCustomerId but with an unknown number of extra chars attached to the end.

So in my example, the customer gives me SoseJost75G but my database only has SoseJost (without the 75G on the end.)

My query works works. But it takes over a minute to run. That is because it can't use the index that is on ClientCustomerId.

Does anyone know a way to improve the performance of this kind of query?

Answer 1

You might try something like this:

DECLARE @var VARCHAR(100)='SoseJost75G';

WITH pre_selected AS
(SELECT * FROM Customer WHERE ClientCustomerId  LIKE LEFT(@var,6) + '%')
SELECT * 
FROM pre_selected WHERE @var LIKE ClientCustomerId +'%';

With a LIKE with fix start-search an existing index on ClientCustomerId will be used.

With a CTE you never know exactly, which order of execution will take place, but - in some quick test - the optimizer chose first to reduce the set to a tiny rest and perform the heavy search as second step.

If the order of execution is not the way you expect this, you might insert the result of the first CTE-query into a declared variable (only the column with the ID) and then continue with this tiny table...

Something like this

DECLARE @var VARCHAR(100)='SoseJost75G';

DECLARE @CustIDs TABLE(ClientCustomerID VARCHAR(100));
INSERT INTO @CustIDs(ClientCustomerID)
SELECT ClientCustomerID FROM Customer WHERE ClientCustomerId LIKE LEFT(@var,6) + '%';

--Use this with an IN-clause then
SELECT ClientCustomerId 
FROM @CustIDs WHERE @var LIKE ClientCustomerID +'%'