CTE SQL Server查询的性能问题
我们有一个具有父子关系的表,表示深层树结构。CTE SQL Server查询的性能问题
我们正在使用CTE查看数据,但性能较差(请参阅下面的代码和执行计划)。
有没有什么办法可以提高性能?
WITH cte (ParentJobTypeId, Id) AS
(
SELECT
Id, Id
FROM
dbo.JobTypes
UNION ALL
SELECT
e.Id, cte.Id
FROM
cte
INNER JOIN
dbo.JobTypes AS e ON e.ParentJobTypeId = cte.ParentJobTypeId
)
SELECT
ISNULL(Id, 0) AS ParentJobTypeId,
ISNULL(ParentJobTypeId, 0) AS Id
FROM
cte
使用范围的键的一个简单的例子。正如我前面提到的,等级分别为127K点和一些部分,其中深15级
CTE的构建,让我们假设票数结果将被存储在一个表中(索引为好)
Declare @Table table(ID int,ParentID int,[Status] varchar(50))
Insert @Table values
(1,101,'Pending'),
(2,101,'Complete'),
(3,101,'Complete'),
(4,102,'Complete'),
(101,null,null),
(102,null,null)
;With cteOH (ID,ParentID,Lvl,Seq)
as (
Select ID,ParentID,Lvl=1,cast(Format(ID,'000000') + '/' as varchar(500)) from @Table where ParentID is null
Union All
Select h.ID,h.ParentID,cteOH.Lvl+1,Seq=cast(cteOH.Seq + Format(h.ID,'000000') + '/' as varchar(500)) From @Table h INNER JOIN cteOH ON h.ParentID = cteOH.ID
),
cteR1 as (Select ID,Seq,R1=Row_Number() over (Order by Seq) From cteOH),
cteR2 as (Select A.ID,R2 = max(B.R1) From cteOH A Join cteR1 B on (B.Seq Like A.Seq+'%') Group By A.ID)
Select B.R1
,C.R2
,A.Lvl
,A.ID
,A.ParentID
Into #TempHier
From cteOH A
Join cteR1 B on (A.ID=B.ID)
Join cteR2 C on (A.ID=C.ID)
Select * from #TempHier
Select H.R1
,H.R2
,H.Lvl
,H.ID
,H.ParentID
,Total = count(*)
,Complete = sum(case when D.Status = 'Complete' then 1 else 0 end)
,Pending = sum(case when D.Status = 'Pending' then 1 else 0 end)
,PctCmpl = format(sum(case when D.Status = 'Complete' then 1.0 else 0.0 end)/count(*),'##0.00%')
From #TempHier H
Join (Select _R1=B.R1,A.* From @Table A Join #TempHier B on A.ID=B.ID) D on D._R1 between H.R1 and H.R2
Group By H.R1
,H.R2
,H.Lvl
,H.ID
,H.ParentID
Order By 1
返回现在在#Temp表中的更高层。注意R1和R2,我称这些为范围键。数据(没有递归)可以通过这些键
R1 R2 Lvl ID ParentID
1 4 1 101 NULL
2 2 2 1 101
3 3 2 2 101
4 4 2 3 101
5 6 1 102 NULL
6 6 2 4 102
非常简单的例子来选择和聚合:说明了滚动数据向上票数。
R1 R2 Lvl ID ParentID Total Complete Pending PctCmpl
1 4 1 101 NULL 4 2 1 50.00%
2 2 2 1 101 1 0 1 0.00%
3 3 2 2 101 1 1 0 100.00%
4 4 2 3 101 1 1 0 100.00%
5 6 1 102 NULL 2 1 0 50.00%
6 6 2 4 102 1 1 0 100.00%
的范围按键的真正的美,是如果你知道一个ID,你知道它的存在(所有后代和祖先)。
一个127K点生命需要12秒才能建立。我不能说770K –
如果有帮助,这里是一个更强大的示例http://*.com/questions/37954697/sql-server-hierarchy-with-parent-id-and-child-id/37992828# 37992828 –
谢谢。 Total Complete Pending PctCmpl以及R1和R2代表什么? (对于愚蠢的问题抱歉) – Burt
我的意思是,多少层次深? – Lamak
树由用户创建,所以它是未知的我猜大约5-9。 – Burt
700k点层次结构?它多久改变一次? –