4

I have a query that does a like statement on a column that stores fullpath locations of files on a computer.

Example

select * 
from table 
where fullpath like '%hi.exe'

Which never seems to use an index and is very slow compared to doing a fullpath = 'value' (obviously)

But my question is, is there any way or ideas on how to speed up query results or get it to use indexes?

I dont see the same slowness if the like is on the right side (ex select * from table where fullpath like 'hi%'

EDIT: Microsoft Sql server 2017 Standard Edition

2
  • In some databases there's an index type that will help with this (e.g. postgres trigram index.) I don't know whether sql server has anything like that. Commented Sep 3, 2023 at 18:41
  • @GlennWillen Unfortunately SQL Server does not natively, but it is possible to write your own code to mimic how trigrams work. But it's not necessarily a simple thing to do.
    – J.D.
    Commented Sep 6, 2023 at 20:45

3 Answers 3

9

FWIW, I don't think the previously provided duplicate questions were actually duplicate of this specific question and instead answered a more complex tangentially related question on how to improve searching with double wildcards (contains type of searches). Some of the answers probably can be applied here as well, but are overly complicated for this much easier problem, hence my vote to re-open the question. I tried locating a suitable duplicate with the simpler solution but could not find one.


Is there any way to improve performance on like searches that have the % to the left of the searched value?

Assuming you do strictly mean only starts-with type of searches (only leading wildcards) and not contains type of searches, then there are some fairly simple solutions.

One solution involves storing a copy of the column reversed (a computed column can help here) and then creating an index on the reversed column. Then you can reverse your wildcard order to make it an ends-with search against the reversed column. This would be logically equivalent and also be sargable, therefore allowing the index to be used in an efficient manner.

Example code:

-- Step 1: Add a computed column with the reversed column
ALTER TABLE YourTable
ADD fullpath_reversed AS REVERSE(fullpath);

-- Step 2: Index the newly created reversed column 
CREATE NONCLUSTERED INDEX IX_YourTable_fullpath_reversed ON YourTable (fullpath_reversed);

-- Step 3: Query the newly created column efficiently 
select * 
from YourTable 
where fullpath_reversed like 'exe.ih%';

Note you shouldn't use SELECT * in any non-adhoc code, especially when you're concerned about performance. Only list out the columns you actually care about for that particular query. It's more performant both from reading less data than necessary and potentially resulting in a better query plan, with indexes chosen, and index operations used.

10
  • 1
    I've seen the same answer online else where too so I'll have to take it into consideration and try it. Thanks for the info though and reiterating that as a solution that could work.
    – Steven M
    Commented Sep 1, 2023 at 18:25
  • To slightly modify what you said, you have to make the computed column "PERSISTED" if you want to build an index on it. Also not sure yet how this behaves with bulk inserts into the table. While not using a persisted will instead make it like a view where the table is calculated at the time of doing selects which could cause slowness and also again cant be used within an index. Change below
    – Steven M
    Commented Sep 6, 2023 at 18:46
  • -- Step 1: Add a computed column with the reversed column ALTER TABLE YourTable ADD fullpath_reversed AS REVERSE(fullpath) PERSISTED; -- Step 2: Index the newly created reversed column CREATE NONCLUSTERED INDEX IX_YourTable_fullpath_reversed ON YourTable (fullpath_reversed); -- Step 3: Query the newly created column efficiently select * from YourTable where fullpath_reversed like 'exe.ih%';
    – Steven M
    Commented Sep 6, 2023 at 18:46
  • @stevenmock That's not true. An index can be created on a non-persisted computed column. See this dbfiddle.uk for an example.
    – J.D.
    Commented Sep 6, 2023 at 20:43
  • If for some reason you wanted to persist it anyway, bulk inserts would likely be fine for such a trivial expression unless you're inserting hundreds of millions of rows at a time in a single insert statement.
    – J.D.
    Commented Sep 6, 2023 at 20:49
6

If you typically search for file names, store them separately instead of embedded in fullpath.

Might be better to store path, file name, and maybe extension separately depending on the queries.

Will use less space than a reversed copy of the fullpath.

2
  • I was going to write same thing. Usually this the practice
    – KumarHarsh
    Commented Sep 4, 2023 at 5:38
  • This is for sure a good solution as well. And I mentioned as such to the developers and bosses. But that code change is more so on their end and not something I can control.
    – Steven M
    Commented Sep 7, 2023 at 14:43
1

Maintain a separate column with less width for filename only.Also if you multiple file extension type then you can maintain tinyint flag in separate column.

It is only one time task during insert/update proc.

This way you can avoid Like operator altogether.

SARGable is only one of the factor for optimizer for using Index

Another main factor is column width.Less the column width,there will be less number of index page.So optimizer will have to read less page,so more the chance of using Index.

Optimizer often make good enough plan and cost effective plan.In some case even if Index is not use but still plan is good enough.

Do not make Index on column of datatype varchar with more width.If table contain million of rows later then this index get wasted and might take longer to execute even small query which return less number of rows

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.