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.