Craig February 2016

Querying a Self Joining table seems slow

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

Answers


Joe Philllips February 2016

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)

Post Status

Asked in February 2016
Viewed 1,054 times
Voted 9
Answered 1 times

Search




Leave an answer