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?
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%'
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?
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?
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)
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'
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.
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.
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.
We are trying to figure out if we should get this going or wait for the 3rd party company.