Improve LIKE Reverse Request Performance

Question

I have a query similar to this:

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

The bottom line is what it is, I get from the client that it is mine ClientCustomerId, but with an unknown number of additional characters attached to the end.

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

My request is working. But it will take a minute to run. This is due to the fact that it cannot use the index that is on ClientCustomerId.

Does anyone know how to improve the performance of this type of query?

Answer 1

You can 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 +'%';

LIKE start-search ClientCustomerId.

CTE , , - - .

, CTE- ( ), ...

-

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 +'%'

Answer 2

, ( ). select, .

DECLARE @customerIdSubstring varchar(255) = 'SoseJost75G'
DECLARE @customerIdSubstringLength INT
DECLARE @results TABLE 
(
    CustomerId varchar(255)
)


DECLARE @FoundResults BIT = 0;

WHILE (@FoundResults = 0)
BEGIN 

    INSERT INTO @results (CustomerId)
    SELECT  CustomerId
    FROM    Customer cust
    WHERE   CustomerId = @customerIdSubstring 


    SELECT @FoundResults = CASE 
                               WHEN EXISTS(SELECT * FROM @results) THEN CAST(1 AS BIT)
                               ELSE CAST(0 AS BIT)
                           END

    SET @customerIdSubstringLength = LEN(@customerIdSubstring)

    -- We don't want to match on fewer than 3 chars.  (May not be correct at that point.)
    IF (@customerIdSubstringLength < 3)
        BREAK;

    SET @customerIdSubstring = LEFT(@customerIdSubstring, @customerIdSubstringLength - 1)
END 

SELECT CustomerId
FROM @results

, . Inpractice, 3-6 . , 3-6 , 1 1 .

- "LIKE" . ( , , SanJos, , SanJost.)

Answer 3

ClientCustomerId, . , :

WHERE ClientCustomerId like left('SoseJost75G', 4) + '%'

.

AND ClientCustomerId <= 'SoseJost75G' and ClientCustomerId

.

:

SELECT CustomerId
FROM Customer cust
WHERE ClientCustomerId like left('SoseJost75G', 4) + '%'
AND ClientCustomerId <= 'SoseJost75G' and ClientCustomerId;

BTW:

ClientCustomerId = left('SoseJost75G', length(ClientCustomerId))

, .

Answer 4

, . , :

DECLARE @customerIdSubstring varchar(255) = 'SoseJost75G'
DECLARE @results TABLE 
(
    CustomerId varchar(255)
)

DECLARE @FoundResults BIT = 0
DECLARE @customerIdSubstringLength INT = LEN(@customerIdSubstring)

WHILE (@FoundResults = 0 AND @customerIdSubstringLength >= 3)
BEGIN 
    INSERT INTO @results
    SELECT  CustomerId
    FROM    Customer
    WHERE   CustomerId = @customerIdSubstring

    -- Make @FoundResults = 1 if there at least one record
    SELECT TOP 1 @FoundResults = 1 FROM @results

    SET @customerIdSubstringLength = @customerIdSubstringLength - 1
    SET @customerIdSubstring = LEFT(@customerIdSubstring, @customerIdSubstringLength)
END

SELECT CustomerId
FROM @results

, ID , , , . @customerIdSubstring :

DECLARE @customerIdSubstring varchar(255) = 'SoseJost75G'
DECLARE @customerIdFound varchar(255)

DECLARE @customerIdSubstringLength INT = LEN(@customerIdSubstring)

WHILE (@customerIdFound IS NULL AND @customerIdSubstringLength >= 3)
BEGIN 
    SELECT  @customerIdFound = CustomerId
    FROM    Customer
    WHERE   CustomerId = LEFT(@customerIdSubstring, @customerIdSubstringLength)

    SET @customerIdSubstringLength = @customerIdSubstringLength - 1
END

SELECT @customerIdFound

Answer 5

, , .

SARG =

, , .

LIKE % . , % . , LEFT ([Column], 4) + '%' WHERE . , SARG .

[COLUMN] LIKE 'abc%' -> sargable
[COLUMN] LIKE '%abc' -> not sargable
[COLUMN] LIKE LEFT('ABCDE', 4) -> not sargable

, . ETL-Porcess . . .

, , .