5

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.

7
  • 2
    While not able to directly pinpoint an issue, you could look at the actual execution plan (and/or share it so others can look at what your DBMS is actually doing for this query). You can get a visualisation that's shareable through Paste the Plan. Alternatively which can impact performance are your statistics, which might need to be refreshed or are otherwise hampering performance. Grabbing a link from a related question Wait Satistics
    – D Kramer
    Commented Dec 9, 2021 at 9:11
  • 2
    Related: dba.stackexchange.com/questions/46917/…
    – Paul White
    Commented Dec 9, 2021 at 9:15
  • Another thing that comes to mind seeing your query is: are you using local variables? Cause if you do, you might not get a seek on userId even if it's really selective. Check this great answer from Erik Darling dba.stackexchange.com/a/206817/106520 Commented Dec 9, 2021 at 10:43
  • A trigram index will help, but I don't know how to make one in MSSQL.
    – AndreKR
    Commented Dec 10, 2021 at 1:01
  • 1
    @AndreKR Here's an example for trigrams.
    – vonPryz
    Commented Dec 10, 2021 at 9:16

1 Answer 1

17

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.

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.