SQLSERVER Union效率难题求解

2010-02-24 09:24:56 504 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

项目实战——sqlserver 使用With as 提高sql语句的可维护性和运行效率所谓的共用表达式，有没有点像一般编程语言中的全局常量呢？为什么是常量？因为，在sql语句中，一经定义，无法再次进行修改，只能调用。阅读详情

遇到一个奇怪现象，百思不得奇解

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%，但看不出什么问题来

...全文

CTFHUB------XSS反射型(WP) 文章目录前言XSS测试平台前言一开始我直接在上面写js脚本发现出错，最后发现只能通过下面的输入框来执行XSS测试，于是我们只能用XSS测试平台解决问题 XSS测试平台注册一个XSS测试平台这里随便填写勾选默认模块就够了，点击下一步这时会出现一个找个将代码复制拼接到网站后缀name后面，点击send 这时平台会收到一个记录点开即可得到flag ... 阅读详情

BurningM

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

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

打赏
举报

最好把你的这串代码贴出来看看

SQLSERVER Union效率难题求解

相关推荐