Querying a Self Joining table seems slow

Question

I have a table which holds a hierarchical list of locations. A location has a parent location, and a location have multiple siblings.

CREATE TABLE [dbo].[Location]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [ParentID] [int] NULL,
    [LocationTypeID] [int] NOT NULL,
    [Description] [varchar](100) NOT NULL,
    [Deleted] [datetime] NULL,
    [CreatedDate] [datetime] NOT NULL,
    [CreatedUserID] [int] NOT NULL,
    [ModifiedDate] [datetime] NULL,
    [ModifiedUserID] [int] NULL,
    [Version] [timestamp] NOT NULL,

    CONSTRAINT [pk_location] 
    PRIMARY KEY CLUSTERED ([ID] ASC)
            WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,  
                  IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                  ALLOW_PAGE_LOCKS = ON) 
) 
GO

ALTER TABLE [dbo].[Location] WITH CHECK 
   ADD CONSTRAINT [fk_location_location] 
   FOREIGN KEY([ParentID]) REFERENCES [dbo].[Location] ([ID])
GO

When I query the data, I created a view to simplify the output:

WITH MyLocation AS 
( 
    SELECT 
        A.ParentID, A.ID,
        Description,
        0 AS 'Level',
        CAST(A.Description AS VARCHAR(512)) AS SORT_PATH
    FROM     
        Location A
    WHERE    
        A.ParentID IS NULL

    UNION ALL

    SELECT 
        C.ParentID, C.ID,
        C.Description,
        Level + 1,
        CAST(SORT_PATH + '//' + C.Description AS VARCHAR(512)) AS SORT_PATH
    FROM 
        Location C
    INNER JOIN 
        MyLocation ON MyLocation.ID = C.ParentID
)
SELECT  
    ParentID, ID, Level,
    SORT_PATH,
    ML.Description AS DISPLAY_PATH
FROM   
    MyLocation AS ML 

But then I select from this view, for example:

SELECT * 
FROM MyView 
WHERE ID = 367

I get around a 400ms response time, and the table only has 750 rows. This may be due to the query requiring the whole load of data to be viewed, and then select only the item I want, but the speed still seems a bit slow.

Is there some indexing that can be applied to improve the performance?

Below is the execution plan. There's a costly 55% cost clustered index scan... and some 29% Concatenation thing happening. Can these be assisted by adding indexing? Maybe remove the null-able 'ParentID' (Which indicates the root)?

I have added a new index on ParentID using the following code:

CREATE INDEX ix_location_parentids
ON Location (ParentID)

The time taken to execute on average has dropped by 20ms to around 370ms. Below is the updated query execution plan after the addition of the index.

Answer 1

You need an index on ParentID because of your WHERE condition and your JOIN condition.

After that you can try to add all the columns as "included columns" on the parent_id index. This means it won't even have to do a seek on the clustered index.

CREATE INDEX ix_location_parentids
ON Location (ParentID)
INCLUDE (ParentID, ID, Description, Level)