Sql-server – Which columns need index in recursive table and soft delete

index-tuningrecursivesql server

I have a Category table like below :

enter image description here

I have a recursive relationship on this Category table. I use soft delete in my project. I have 2000 rows in Category table. When I want to show data in a UI dropdown it takes about 3 minutes.

Which columns need an index to improve query speed?

Table definition:

 CREATE TABLE [dbo].[Category](
    [CategoryId] [uniqueidentifier] NOT NULL,
    [CategoryCode] [int] IDENTITY(1,1) NOT NULL,
    [CategoryName] [nvarchar](400) NOT NULL,
    [ParentId] [uniqueidentifier] NULL,
    [DisplayOrder] [int] NOT NULL,
    [Description] [nvarchar](max) NULL,
    [IsActive] [bit] NOT NULL,
    [IsShowOnMenu] [bit] NOT NULL,
    [AttachmentId] [uniqueidentifier] NULL,
    [Depth] [int] NOT NULL,
    [Path] [nvarchar](max) NULL,
    [IsDeleted] [bit] NOT NULL,

 CONSTRAINT [PK_dbo.Category] PRIMARY KEY CLUSTERED 
(
    [CategoryId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)

My query is in C#. I used C# statment to generate show all category in dropdown.
My query is:

exec sp_executesql N'SELECT 
[Project1].[DisplayOrder] AS [DisplayOrder], 
[Project1].[CategoryId] AS [CategoryId], 
[Project1].[CategoryName] AS [CategoryName]
FROM ( SELECT 
    [Extent1].[CategoryId] AS [CategoryId], 
    [Extent1].[CategoryName] AS [CategoryName], 
    [Extent1].[DisplayOrder] AS [DisplayOrder]
    FROM [dbo].[Category] AS [Extent1]
    WHERE (([Extent1].[IsDeleted] = @DynamicFilterParam_1) OR (@DynamicFilterParam_2 IS NOT NULL)) AND ([Extent1].[ParentId] = @p__linq__0)
)  AS [Project1]
ORDER BY [Project1].[DisplayOrder] ASC',N'@DynamicFilterParam_1 bit,@DynamicFilterParam_2 bit,@p__linq__0 uniqueidentifier',@DynamicFilterParam_1=0,@DynamicFilterParam_2=NULL,@p__linq__0='8d8dd739-a132-e3ef-5443-159ecf8adc44'

Above query runs for each item, because I need to get all children.

Best Answer

Try this index for some noticeable improvement:

CREATE NONCLUSTERED INDEX [CatIndex1] ON [dbo].[Category]
(
    [ParentID] ASC
)
INCLUDE (   [CategoryID],
    [CategoryName],
    [DisplayORder])

I am interested in the time reduction. Please let me know your findings.

p.s. consider sorting only once with the ORDER BY if you're running it multiple times. Perhaps put data into a C# DataTable or SQL temp table and sort at the very end of all retrieval operations.

Related Question