遇到一个奇怪现象,百思不得奇解sql是这样的:
第一个查询
with (树 a)
select count(*) from b where b.code = a.code and (b的过滤条件)
耗时0.x秒第二个查询
with (树 a)
select count(*) from c where c.code = a.code and (c的过滤条件)
耗时也是0.x秒
但这样写就出问题了
with (树 a)
select count(*) from b where b.code = a.code and (b的过滤条件)
union
select count(*) from c where c.code = a.code and (c的过滤条件)耗时 60+秒......表数据 a 和 b大的一个是在50万行而已
检查执行路径,似乎c表的全表检索耗时71%,但看不出什么问题来
第一个查询
with (树 a)
select count(*) from b where b.code = a.code and (b的过滤条件)
耗时0.x秒第二个查询
with (树 a)
select count(*) from c where c.code = a.code and (c的过滤条件)
耗时也是0.x秒
但这样写就出问题了
with (树 a)
select count(*) from b where b.code = a.code and (b的过滤条件)
union
select count(*) from c where c.code = a.code and (c的过滤条件)耗时 60+秒......表数据 a 和 b大的一个是在50万行而已
检查执行路径,似乎c表的全表检索耗时71%,但看不出什么问题来
会消除重复的
所以耗时
2、如果是union惹的祸,那换成union all 如何?
关键是union的只是两个数值而已,并没有多少结果集啊
with PDMBOMCTE (TopCode, ParentCode, ChildCode, ChildCount, iLayer) as (select Code as TopCode, Code as ParentCode, Code as ChildCode, ChildCount = 1, iLayer = 1 from PDM_Part where Code = @Code union all select PDMBOMCTE.TopCode, PDM_BOM.ParentCode, PDM_Bom.ChildCode, PDM_Bom.ChildCount * PDMBOMCTE.ChildCount as ChildCount, iLayer +1 from PDM_Bom inner join PDMBOMCTE on PDM_BOM.ParentCode = PDMBOMCTE.ChildCode where not exists (select code from PDM_Part where (parttype = 5) and code = PDM_BOM.ParentCode))
select count(code) as 'Num' from PDM_Part where (PartType = 1 or PartType = 2 ) and exists (select * from PDMBOMCTE where PDMBOMCTE.Childcode = PDM_Part.Code)第二个查询是
with PDMBOMCTE (TopCode, ParentCode, ChildCode, ChildCount, iLayer) as (select Code as TopCode, Code as ParentCode, Code as ChildCode, ChildCount = 1, iLayer = 1 from PDM_Part where Code = @Code union all select PDMBOMCTE.TopCode, PDM_BOM.ParentCode, PDM_Bom.ChildCode, PDM_Bom.ChildCount * PDMBOMCTE.ChildCount as ChildCount, iLayer +1 from PDM_Bom inner join PDMBOMCTE on PDM_BOM.ParentCode = PDMBOMCTE.ChildCode where not exists (select code from PDM_Part where (parttype = 5) and code = PDM_BOM.ParentCode))
select count(code) as 'Num' from PDM_Part where (PartType = 1 or PartType = 2 )
and exists (select * from sis_partlife201002 where producttype= '' and code = PDM_Part.code and (referencecount >= @over or (referencecount <= @lower and sumcount >= @ovsum))) and exists (select * from PDMBOMCTE where PDMBOMCTE.Childcode = PDM_Part.Code)合起来就是
with PDMBOMCTE (TopCode, ParentCode, ChildCode, ChildCount, iLayer) as (select Code as TopCode, Code as ParentCode, Code as ChildCode, ChildCount = 1, iLayer = 1 from PDM_Part where Code = @Code union all select PDMBOMCTE.TopCode, PDM_BOM.ParentCode, PDM_Bom.ChildCode, PDM_Bom.ChildCount * PDMBOMCTE.ChildCount as ChildCount, iLayer +1 from PDM_Bom inner join PDMBOMCTE on PDM_BOM.ParentCode = PDMBOMCTE.ChildCode where not exists (select code from PDM_Part where (parttype = 5) and code = PDM_BOM.ParentCode))
select count(code) as 'Num' from PDM_Part where (PartType = 1 or PartType = 2 ) and exists (select * from PDMBOMCTE where PDMBOMCTE.Childcode = PDM_Part.Code)
union
select count(code) as 'Num' from PDM_Part where (PartType = 1 or PartType = 2 )
and exists (select * from sis_partlife201002 where producttype= '' and code = PDM_Part.code and (referencecount >= @over or (referencecount <= @lower and sumcount >= @ovsum))) and exists (select * from PDMBOMCTE where PDMBOMCTE.Childcode = PDM_Part.Code)单独执行都很快,但union后就慢的可怕了,看计划似乎后面那个查询扫描过滤表的时间比较长,难道是(referencecount >= @over or (referencecount <= @lower and sumcount >= @ovsum)引起的?
其实这样的union我其他地方也有用的,但都没引起效率问题
你這樣複雜,然後結果集龐大的,,臨時表,,,又沒有做索引,,連接又多,,,自然問題就大了,,
推薦with查詢接到道臨時表裏,然後對臨時表做索引
再做你後續的查詢
但是单独查询时一点问题都没有
但不在union下没有问题呢