How can I improve the performance of where clause with LIKE %abc?

Question

I had below select statement that turned out very slow (41s)

SELECT DISTINCT a.Doc_Resource_ID 
       ,a.Parent_ID 
       ,a.Logical_Path 
       ,a.lvl 
       ,CAST(SUBSTRING(a.Security_Level, 21, 1) AS TINYINT)
  FROM Doc_ACL_Detail_D a   
 WHERE a.UserId = @I_vUserId 
   AND @I_vParentId = 0 
    OR (Logical_Path LIKE(CASE 
                             WHEN @I_vParentId>0 THEN '%-'+LTRIM(STR(@I_vParentId)) 
                          END)
    OR Logical_Path LIKE(CASE 
                            WHEN @I_vParentId>0 THEN '%-'+LTRIM(STR(@I_vParentId))+'-%' 
                         END ))
OPTION (MAXRECURSION 5000)

I already created a non-clustered index for table Doc_ACL_Detail_D but the execution plan is still Table Scan.

The index that I created :

    CREATE NONCLUSTERED INDEX idx_Doc_ACL_Detail_D_UserId_Logical_Path 
       ON [dbo].[Doc_ACL_Detail_D] ([UserId],[Logical_Path])
  INCLUDE (Parent_ID,Doc_Resource_ID,lvl,Security_Level)

Could someone please help me to optimize this statement. Thanks a lots.

Answer 1

No index can help with this data as it stands.
The value you are looking for in embedded somewhere inside the field, not at the start of a field (where an Index could help). Because the database doesn't know what's at the start of the field, it can't navigate to the right bits of the index to get to the records you want.

Reversing the value and indexing those values would help where the value is at the end of the field, but still won't help where the value is stuck in the middle, somewhere.

To me, this screams of having "too much" data in this field. You're using string manipulation functions to extract part of the value, which says to me that the value you actually want is "wrapped" in "noise". Getting rid of that noise might be a far better option.

select * 
from Folders ; 

+----+--------+------+
| id | parent | path | 
+----+--------+------+
|  1 |  NULL  | abc  | The folder /abc 
|  2 |      1 | def  |            /abc/def 
|  3 |      2 | ghi  |            /abc/def/ghi 
+----+--------+------+

Much easier to traverse this structure recursively.

Also, consider removing the distinct.
All too often, I see distinct used as a "sticking plaster" to cover up an underlying problem with the data structures themselves or with a query, for example "duplicate" rows that appear in the results (usually caused by bad Joins) but "using distinct makes them go away". Getting rid of those "duplicates" can be a huge drain on the database ("select distinct a,b,c,d" can perform as badly as "group by a,b,c,d order by a,b,c,d"), so it's better to fix the root cause.