Trying to make a like % % statement sargable

1

Hi
On a column we get rows that look like " ref=113001:amt=1125 " or just " 113001 "
The issue here is that I have to query those rows and I am using a like '%113001%' .
Is there a way to make this not to use the double % .
If i replace all the characters with null will this make any difference?
Thanks.

2

Only way I can think of is to "split" values like "ref=113001:amt=1125" into each individual value, and store them in a Child Table. You can then do

SELECT Col1, Col2
FROM MyTable AS T
WHERE EXISTS
(
    SELECT *
    FROM MyChildTable AS C
    WHERE C.SomeID = T.SomeID
          AND C.MyRef = '113001'
)

even if you have to do

          AND C.MyRef LIKE '113001%'

that will still be pretty efficient, so long as you don't have a leading "%" such as LIKE '%113001%'

1 Like
3

Something I forgot is that I only need the " ref " part of the filed.
So when you say split and store, you mean split the values and store them in a temp table or a regular one?
Or it does not make any difference?

So I need to spit, insert to a table and query the table as you showed?

Thanks.

4

AH.
I forgot.

Doesn't splitting the values and storing them takes time?
So if i insert to a table and then query, won't I impact performance?
Is this preferred over the non sargable query?
Thanks again.

5

Hi.
Also, wouldn't this help, or it will make it worse?
(TP.PaymentCardTransRefCode is the ref=...etc number, PS.PaymentCardTransRefCode is the "clean" number)

substring(LEFT(SUBSTRING(TP.PaymentCardTransRefCode, PATINDEX('%[0-9.-]%', TP.PaymentCardTransRefCode), 8000),
           PATINDEX('%[^0-9.-]%', SUBSTRING(TP.PaymentCardTransRefCode, PATINDEX('%[0-9.-]%', TP.PaymentCardTransRefCode), 8000) + 'X') -1),0,7)= substring(PS.PaymentCardTransRefCode,7,6)

PaymentCardTransRefCode is cut down as it has leading zeros and a "1" before the actual value (000001567891)

7

It would have to be a permanent table. So when the REF column value changes then the rows in the Child Table need to be Inserted / Updated so that they match.

In most cases the moment you use a Function in a JOIN or WHERE clause then it won't be sargable - same thing with LIKE '%xxx'

1 Like
8

Actually - you don't necessarily need a permanent table. You may be able to create an indexed view or create a computed column and persist that column. It will depend on how you are able to parse the value(s) that you need...

I would try a persisted computed column and if that didn't work then try an indexed view. Either way - this will update the values in the column(s) as the data is updated/inserted and update the index associated with the column which can then be searched very quickly.

1 Like
9

Hi all.
I was thinking that even if I create a new table to split the field, wouldn't I need to use a "like" or "substring" ?
If you mean splitting while a new row is inserted then this has been suggested to the company that creates the software but we haven't heard of them in a while.

Also same goes with the computed column. Not allowed to change the table but even if I couldn't , I would again have to split or use like , so a non sargable insert would have been created.

Thanks.

10

If you know the exact Ref that you want to match, then no.

In your O/P you said the data might be "ref=113001:amt=1125" and you were looking for LIKE '%113001%' and i assumed you wanted an exact match on 113001

If most of the time you want an exact match, or a Starts With match, then the child-table will perform well.

if you always want a "contains" match then not much you can do to speed it up.

I should get on with fixing the problem without their help then ... you can "fix" the problem in a separate database. Ideally you would want to create the Child Table using a trigger on the main table, but if you don't want to create a trigger in THAT database then you could use a scheduled job instead. The search data will only be as good as the time that most recently ran - but that could be every few minutes / hours, whatever you need.

it would help a lot, if you have to do this with a scheduled task, if the main table has an Updated_DateTime column - so you could just process rows that have changed "since last time", particularly if the table is big.

1 Like
11

We are trying to figure out if we should get this going or wait for the 3rd party company.

Thanks.