Optimize database schema / indexes for faster query result when using LIKE and EXISTS clauses

Based on:

SELECT sp.SiteID_i 
  FROM SitePath_t sp 
 WHERE EXISTS ( SELECT * 
              FROM [dbo].[SitePath_T] usp 
                 , [dbo].[UserSiteRight_T] uusr 
             WHERE uusr.SiteID_i = usp.SiteID_i 
               AND uusr.UserID_i = 2484 
               AND usp.Path_v LIKE sp.Path_v+'%' ) 

(which is just fine based on the fact that you're doing a Semi Join).

It's focussing (rightly) on the uusr table first, to find the records for that user. It's already doing a CIX Seek on that, which is good. From there, it's finding the corresponding records in usp according to the SiteID_i fields.

So next consider the fact that it wants to find the Sites by SiteID_i, and what kind of join you want this to be.

How about a Merge Join? That would be nice, but requires the data to be sorted on both sides. That's fine if the indexes are in the right order...

...and after that, you want to be finding stuff based on the Path. So how about:

CREATE INDEX ix_UUSR on [dbo].[UserSiteRight_T] (UserID_i, SiteID_i);
CREATE INDEX ix_usp on [dbo].[SitePath_T] (SiteID_i) INCLUDE (Path_v);

And then another index on SitePath_T that finds the SiteIDs you want:

CREATE INDEX ix_sp on [dbo].[SitePath_T] (Path_v) INCLUDE (SiteID_i);

There may be a Nested Loop used on this final one, but that's hopefully not too bad. The thing that's going to impact your system will be the first two indexes, which should let you see a Merge Join between the two tables in your EXISTS clause.