栏目表递归执行效率问题，获取一个目录的所有子目录目前需要50毫秒左右，要怎么建立索引速度会更快，或者优化存储过程

[孔方发发]

/****** Object:  Table [dbo].[Categories]    Script Date: 01/01/2016 22:14:52 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Categories](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[KindID] [int] NULL,
	[CategoryName] [nvarchar](100) NOT NULL,
	[ParentID] [int] NULL,
	[Url] [nvarchar](500) NULL,
	[Type] [nvarchar](20) NULL,
	[Description] [nvarchar](500) NULL,
	[ViewOrder] [int] NOT NULL,
	[IsDetach] [bit] NULL,
	[IsDeleted] [bit] NOT NULL,
	[LastModifiedOnDate] [datetime] NULL,
	[CreatedByUserID] [int] NOT NULL,
	[CreatedOnDate] [datetime] NULL,
	[LastModifiedByUserID] [int] NULL,
	[ViewID] [int] NULL,
	[DepartmentID] [int] NULL,
	[Code] [nvarchar](4) NULL,
	[PreviewID] [int] NULL,
	[Visible] [bit] NULL,
 CONSTRAINT [PK_Categories] PRIMARY KEY NONCLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]



ALTER FUNCTION [dbo].[GetChildCategories](@ParentID varchar(4000))

 RETURNS  @RESULT_TABLE TABLE(ID int,ParentID int) 
 
AS
BEGIN 
		
		WITH ChildCategories(ID,ParentID ) as
		(
		  SELECT  c.ID , c.ParentID  
		  FROM dbo.Categories c	 
		  WHERE   c.IsDeleted=0	  
		  AND (CHARINDEX(',0,',','+@ParentID+',')>0 and  c.ParentID IS NULL
		  OR     CHARINDEX(',' + cast(ParentID as varchar) + ',', ',' + @ParentID + ',') > 0 	) 
		  --这里是树的根节点
		  UNION ALL --定点成员和递归成员之间必须使用union all
		  SELECT #t.ID,#t.ParentID 
		  FROM  dbo.Categories #t INNER JOIN ChildCategories cc on #t.ParentID=cc.ID		  	 
		) 		
		
		INSERT INTO @RESULT_TABLE(ID,ParentID)   	 
		SELECT distinct(ID),ParentID  from ChildCategories 
		UNION 
		select ID,ParentID   from dbo.Categories where charindex(',' + cast(ID as varchar) + ',', ',' + @ParentID + ',') > 0
						 
		
	  RETURN
END

[孔方发发]

我的cagegories 表有15000左右记录

[Tiger_Zhao]

添加一个字符串 path 字段，把父、子、孙的id用逗号串联起来。
数节点关系变动的时候更新一下。
其实大多数时候是不变的，保存下来可以加快检索啊。
用一个 like 条件就可以得到结果的。