SQLSERVER Union效率难题求解

BurningM 2010-02-24 09:24:56
遇到一个奇怪现象，百思不得奇解

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%，但看不出什么问题来
...全文
442 19 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
19 条回复
切换为时间正序
请发表友善的回复…
发表回复
BurningM 2010-02-25
  • 打赏
  • 举报
 回复
试了下，在referencecount上加了个索引，果然查询效率高了很多
但不在union下没有问题呢
东那个升 2010-02-25
  • 打赏
  • 举报
 回复
循环多，去重多。。。。。
BurningM 2010-02-25
  • 打赏
  • 举报
 回复
code上是有索引的，该加的索引都加了

with的那个表（产生树结构），数据量很小的，不会引起大问题

关键是partlife201002这个表，引起union后第2个查询非常慢
但是单独查询时一点问题都没有
starseeker7 2010-02-25
  • 打赏
  • 举报
 回复
而且基本上with就是後臺做個臨時表出來出來幫你處理問題，
你這樣複雜，然後結果集龐大的，，臨時表，，，又沒有做索引，，連接又多，，，自然問題就大了，，
推薦with查詢接到道臨時表裏，然後對臨時表做索引
再做你後續的查詢
starseeker7 2010-02-25
  • 打赏
  • 举报
 回复
o 代碼把腦殼看暈了。。。你也稍微縮進一下啊
留給老大們解決吧 - -
feixianxxx 2010-02-25
  • 打赏
  • 举报
 回复
有没在code referencecount字段加索引
BurningM 2010-02-25
  • 打赏
  • 举报
 回复
就是第二个查询，没有referencecount的索引效率也很搞，但和第一个查询union后，没有这个索引效率大减
feixianxxx 2010-02-25
  • 打赏
  • 举报
 回复
引用 17 楼 burningm 的回复:
试了下，在referencecount上加了个索引，果然查询效率高了很多
但不在union下没有问题呢
你什么意思呢？
feixianxxx 2010-02-24
  • 打赏
  • 举报
 回复
引用 7 楼 guguda2008 的回复:
看一下计划

鸭子 现在是 高手了
流氓兔 2010-02-24
  • 打赏
  • 举报
 回复
顶！
待测试
。。。。。。。。。。。。。。。。。。。。
guguda2008 2010-02-24
  • 打赏
  • 举报
 回复
看一下计划
黄_瓜 2010-02-24
  • 打赏
  • 举报
 回复
引用 5 楼 burningm 的回复:
union all一样的
关键是union的只是两个数值而已，并没有多少结果集啊

不会，帮顶
BurningM 2010-02-24
  • 打赏
  • 举报
 回复
union all一样的
关键是union的只是两个数值而已，并没有多少结果集啊
黄_瓜 2010-02-24
  • 打赏
  • 举报
 回复
1、得到的结果对吗？
2、如果是union惹的祸，那换成union all 如何？
ws_hgo 2010-02-24
  • 打赏
  • 举报
 回复
union
会消除重复的
所以耗时
东那个升 2010-02-24
  • 打赏
  • 举报
 回复
相当于多了一步DISTINCT 运算
东那个升 2010-02-24
  • 打赏
  • 举报
 回复
因为你是UNION，所以当中有了去重的运算，所以耗时.
BurningM 2010-02-24
  • 打赏
  • 举报
 回复
第一个查询是
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我其他地方也有用的，但都没引起效率问题
东那个升 2010-02-24
  • 打赏
  • 举报
 回复
最好把你的这串代码贴出来看看
MS-SQL Server 基础类 - SQL语句
 
基础类 - SQL语句

基础类 - SQL语句

转载请标明出处： http://blog.csdn.net/hz/archive/2006/03/01/613376.asp 本文来自 CSDN 博客。x
sql server 怎么跨库查询？
如何判断字符的大小写 ?
如何得到动态 SQL 中取得的值 ?
如何定义动态游标 ?
已知：当前时间 ( 截取 yyyy-mm 目标 1 查询进销存表当中距离当前时间连续 3 个月内进货数量 =0...<
SQL进阶技巧：当表查询结果为空时，如何输出NULL值？
一般来说我们通常需要对数据中存在的NULL值进行转换处理，往往利用NVL()函数或coalesce()函数进行转换，但在有的需求中往往需要NULL值进行占位，比如获取数据为空时返回NULL值，当查询结果为空时，如何用NULL值输出呢？（2）实战中总结的SQL技巧，帮助SQLBOY 在SQL语言上有质的飞越，无论你应对业务难题及面试都会游刃有余【如果您觉得本文还不错，对你有帮助，那么不妨可以关注一下我的数字化建设实践之路专栏，这里的内容会更精彩。本题的求解不难，问题关键点在于当为空表的时候如何输出NULL？
天塌了！！！SQL竟也可以做预测分析？| 商品零售额的预测
天塌了！！！SQL竟也可以做预测分析？| 商品零售额的预测
数据库帖子收集
这样的数据列表在存储过程中应该怎么样选择得到？ 请问造成SQL2000服务不能启动的原因有哪些？ sql server中有無類似于if 的函數,即像這樣select if(cancel=1,'cancel','') from sales 这样怎么返回呀！！！！记录集的返回。。 谁能跟我说说期初库存怎么样的一个业务逻辑!都要处理哪些内容!需要每月进...
疑难问题

22,300

社区成员

121,734

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章