sql 关于多表关联 优化问题

媛爱 2013-03-14 02:15:03
最近 有20w 的数据执行查询，还要多表关联值(已写成试图)，查询起来很慢！
求优化，求解释，求解决办法！

...全文
991 24 打赏 收藏 转发到动态 举报
写回复
用AI写文章
24 条回复
切换为时间正序
请发表友善的回复…
发表回复
Soli23365250 2014-05-07
  • 打赏
  • 举报
 回复
求优化方案 select * from ( select i.fcode,i.acode,i.cdate,i.year,i.month,i.nflags,i.keptbcode,i.icash,i.ocash,i.cremark,ii.initdate,isnull(b.rpbank,c.rpbank) rpbank,isnull(b.rpbankaccno,c.rpbankaccno) rpbankaccno,isnull(isnull(isnull(b.type,c.type),e.type) ,t.type)type,isnull(isnull(isnull(b.necode,c.necode),e.necode) ,t.necode) necode,isnull(b.ccode,c.ccode) as ccode,isnull(isnull(isnull(b.sheetcode,c.sheetcode),e.sheetcode),t.sheetcode)sheetcode from ( select (isnull(icash,0)-isnull(ocash,0) +isnull(cbalance,0)) as cbalancex,cbalance,keptbcode,acode,(case when isnull(fcode,'') = '' then 'RMB' else fcode end) fcode ,nflags,cdate,cremark,icash,ocash,cpcode,gid,year(cdate) year,month,rerate,rpicode from cash where (acode like '1001%' or acode like'1002%' or acode like '1012%' ) and (month is null or month<>0) ) i left join ( select acode,initdate,keptbcode from cash where cbalance is not null and keptbcode<>'1' and (( cremark ='期初余额') and (month is null or month<>0)) ) ii on i.acode = ii.acode and i.keptbcode = ii.keptbcode left join ( select a.* from ( select invicode,ccode,rpbank,rpbankaccno ,rerate,rmb,status,sheetcode,odate,fcode,left(user_company,4) as keptbcode ,user_unfcy as fcy,dc,bcode,isnull(user_remark1,'合作费用') as type,invcode as necode from invoice where status='70' and (sheetcode='1218113' or sheetcode='1218104'or sheetcode='1218111') union select invicode,''as ccode ,'' as rpbank,'' as rpbankaccno ,rerate,rmb,status,sheetcode,odate,fcode,left(user_company,4) as keptbcode ,user_unfcy as fcy,dc,bcode,(select cname from dictinfo where dictid=2 and code=paymode)as type,invcode as necode from invoice where status>='40' and (sheetcode='1218002' or sheetcode='1218011' ) )a ) b on b.necode=left((right(i.cremark,len(i.cremark)-3)),len(i.cremark)-4) left join ( select a.* from ( select m.fineicode as invicode,isnull(m.ccode,null)as ccode,isnull(m.rpbank,null)as rpbank,isnull(m.rpbankccno,null)as rpbankaccno ,f.rerate,m.rmb,m.status,f.sheetcode,f.odate,m.fcode,left(f.bcode,4) as keptbcode ,f.fcy,m.dc,f.bcode,(select invname from invtypedef where invtype=f.fetype) as type,m.finecode as necode from finemain m full join finexp f on m.fineicode=f.innercode1 where m.status='70' )a )c on (c.necode=right(i.cremark,16) or c.necode=right((left(i.cremark,len(i.cremark)-1)),16)) left join ( select a.* from ( select adjicode as invicode,''as rpbank,''as rpbankaccno ,rerate,rmb,status,sheetcode,odate,fcode,left(bcode,4) as keptbcode, fcy,'1' as dc,cashiercode as bcode,type,adjcode as necode from user_adjcash where status='70' )a )t on t.keptbcode =i.keptbcode and (t.necode=right((left(i.cremark,len(i.cremark)-1)),13) or t.necode=right((left(i.cremark,len(i.cremark)-4)),len(i.cremark)-7) or t.necode=left((right(i.cremark,len(i.cremark)-3)),len(i.cremark)-4) ) left join (select a.* from ( select invicode,''as rpbank,''as rpbankaccno ,rerate,rmb,status,sheetcode,odate,fcode,left(bcode,4) as keptbcode, fcy,'1' as dc,cashier as bcode,(select cname from dictinfo where code=paymode and dictid=2) as type,invcode as necode from jst_invoicex where status='70' )a )e on e.keptbcode =i.keptbcode and (right(e.necode,13)=left(i.cremark,13) or e.necode=left(i.cremark,14)) where (i.cdate > isnull(ii.initdate,'1911-01-01') ) and len(i.cremark)>7 union all select i.fcode,i.acode,i.cdate,i.year,i.month,i.nflags,i.keptbcode,i.icash,i.ocash,i.cremark,ii.initdate,'' as rpbank,'' as rpbankaccno,f.type as type,f.necode as necode,'' as ccode,'' as sheetcode from ( select (isnull(icash,0)-isnull(ocash,0) +isnull(cbalance,0)) as cbalancex,cbalance,keptbcode,acode,(case when isnull(fcode,'') = '' then 'RMB' else fcode end) fcode ,nflags,cdate,cremark,icash,ocash,cpcode,gid,year(cdate) year,month,rerate,rpicode from cash where (month is null or month<>0) ) i left join ( select acode,initdate,keptbcode from cash where (( cremark ='期初余额') and (month is null or month<>0)) and cbalance is not null and keptbcode<>'1') ii on i.keptbcode = ii.keptbcode and i.acode = ii.acode left join ( select a.* from ( select invicode,''as rpbank,''as rpbankaccno ,rerate,rmb,status,sheetcode,odate,fcode,left(bcode,4) as keptbcode, fcy,'1' as dc,cashier as bcode,(select cname from dictinfo where code=paymode and dictid=2) as type,invcode as necode from jst_invoicex where status='70' )a )f on f.keptbcode =i.keptbcode and f.invicode =right(i.rpicode,16) where (i.cdate > isnull(ii.initdate,'1911-01-01') ) and len(i.cremark)<=7 ) i where (i.cdate > isnull(i.initdate,'1911-01-01') ) order by i.cdate
媛爱 2013-03-26
  • 打赏
  • 举报
 回复
引用 21 楼 duoxu1983 的回复:
lz的query主要的连接是 SQL code?123456select *from F_Company_EU_Info a left join F_Temp_Project hon a.TP_ID=h.TP_ID left join Sys_Mapping c on a.Partner_Name=c.Mapping_ID left join vw_MapToArea ……
子查询 不是更慢吗？
媛爱 2013-03-26
  • 打赏
  • 举报
 回复
duoxu1983 2013-03-19
  • 打赏
  • 举报
 回复
lz的query主要的连接是 
select *
from F_Company_EU_Info a left join F_Temp_Project h
on a.TP_ID=h.TP_ID left join Sys_Mapping c 
on a.Partner_Name=c.Mapping_ID left join vw_MapToArea e 
on a.Area_ID=e.Area_ID left join F_Company_EU_Contact b
on a.Company_ID=b.Company_ID
所以先看下这段连接的执行计划是否合理，索引是否合理。 其次被多次引用的User_Info如果user_id在该表中是唯一的话可以通过自查询的方式直接在select列中写。 同样SYSTEM_PARAMETERS表中如果PP_VALUE( PP_NAME) ,PP_FIELD,PP_TABLE这三列是唯一值话也可以通过子查询的方式写在select列中。 如下。
isnull(e.Status,'<空>') as Status,
这句改成

isnull((select PP_VALUE from SYSTEM_PARAMETERS where PP_TABLE='F_Company_EU_Contact' and PP_FIELD='Status' and b.PP_VALUE=F_Company_EU_Contact.Status)'<空>') as Status,
---涛声依旧--- 2013-03-19
  • 打赏
  • 举报
 回复
要想速度快，须合理的建立索引，且在查询时where后面依次跟主键、聚簇索引、非聚簇索引； 等号左边尽量少用函数 用or代替in 如果没有必要区分出重复记录的话，则用union all代替union 用>=代替>号（此是经验之谈，未验证过） 楼主的视图太复杂了，建议用SP来返回查询结果会更快
milkman_nuaa 2013-03-19
  • 打赏
  • 举报
 回复
引用 14 楼 DBA_Huangzj 的回复:
看了一下你的执行计划，问题有几个： 1、[ICCL_OA_20100519].[dbo].[F_Company_EU_Contact].Company_ID 这个，用了排序，估计你的聚集索引不在这个列上或者没有按照Company_ID升序来建聚集索引，这一步是开销最大的。把聚集索引建在这列或者包含这列，并按这列升序排序，可以减少这部分的开销，你这一步对33万数据排序，开……
1. 遇到过视图影响性能的case，直接写在SP里面性能提升很多，估计是预编译有效果 2. 大量left join会降低性能 3. 尝试分步吧，中间结果放在临时表，不断减少结果集
發糞塗牆 2013-03-19
  • 打赏
  • 举报
 回复
下次麻烦引用一下，帖子太多的时候看不到你回复了
發糞塗牆 2013-03-19
  • 打赏
  • 举报
 回复
引用 16 楼 xy164889369 的回复:
版主 ： 有几个问题 不是很明白！ 回复问题一： Company_ID 是主键 ，设置主键后 就是一个主键索引吗？ 也就是 不用ROW_NUMBER() OVER (ORDER BY a.Company_ID)AS Row, 这个函数来 排序， 就会减少部分开销？？？？ 回复问题二： 怎么看出来 是带着4G的数据进行运算，结果的数据快8G 又是怎么看呢？ 一共查询出来时 3……
1、主键和row_number是两回事，你这里的row_number只是加个id排序，主键有其自己的顺序，另外准确来说没有主键索引，只有主键约束，而SQLServer默认对主键加上聚集索引，但是这不是强制的，可以按需修改。如果CompanyID创建的时候就已经按照你想要的顺序存放，那么没必要用row_number，这个函数会对你的数据集全表扫描，也就是20多万的数据，开销啊 问题2，你看你的执行计划那些粗的箭头，鼠标移过去就有信息了。
媛爱 2013-03-19
  • 打赏
  • 举报
 回复
版主 ： 有几个问题 不是很明白！ 回复问题一： Company_ID 是主键 ，设置主键后 就是一个主键索引吗？ 也就是 不用ROW_NUMBER() OVER (ORDER BY a.Company_ID)AS Row, 这个函数来 排序， 就会减少部分开销？？？？ 回复问题二： 怎么看出来 是带着4G的数据进行运算，结果的数据快8G 又是怎么看呢？ 一共查询出来时 36万的数据。 后面的几个问题 都明白了！
媛爱 2013-03-19
  • 打赏
  • 举报
 回复
谢谢版主了！ 你说的这些问题 我在研究下！ 真是万分感谢
媛爱 2013-03-18
  • 打赏
  • 举报
 回复
引用 12 楼 DBA_Huangzj 的回复:
私信给你了，发到我邮箱吧，视力不好，看不清楚
版主 已回复邮件…… 期待中……
發糞塗牆 2013-03-18
  • 打赏
  • 举报
 回复
看了一下你的执行计划，问题有几个：
1、[ICCL_OA_20100519].[dbo].[F_Company_EU_Contact].Company_ID 这个，用了排序，估计你的聚集索引不在这个列上或者没有按照Company_ID升序来建聚集索引，这一步是开销最大的。把聚集索引建在这列或者包含这列，并按这列升序排序，可以减少这部分的开销，你这一步对33万数据排序，开销会非常大。
2、你的执行计划每部分的开销都不大，都是1~5%，但是从里面看出你的代码筛选能力非常差，看图：

这里那么多步之后，数据量越来越大，临时结果整整4000M，也就是你带着4G的数据进行运算，之间每一步都是scan，最后返回的结果整整7999M大小，接近8G啊！你确定真的需要26万行的结果？
3、核心问题出在：F_Company_EU_Info和F_Company_EU_Contact这两个表，不能很好地筛选掉没用的数据，主要是没有where条件，所以随着这两个表的增大，你的查询会越来越慢。
4、尝试把这部分改写一下，试下使用UNION ALL或者用case when来代替多次left join
F_Company_EU_Contact a

                            LEFT JOIN ( SELECT  PP_VALUE ,

                                                PP_NAME

                                        FROM    SYSTEM_PARAMETERS

                                        WHERE   PP_TABLE = 'F_Company_EU_Contact'

                                                AND PP_FIELD = 'Status'

                                      ) b ON a.Status = b.PP_VALUE

                            LEFT JOIN ( SELECT  PP_VALUE ,

                                                PP_NAME

                                        FROM    SYSTEM_PARAMETERS

                                        WHERE   PP_TABLE = 'F_Company_EU_Contact'

                                                AND PP_FIELD = 'IS_MAINCONTACT'

                                      ) c ON a.IS_MAINCONTACT = c.PP_VALUE

                            LEFT JOIN ( SELECT  PP_VALUE ,

                                                PP_NAME

                                        FROM    SYSTEM_PARAMETERS

                                        WHERE   PP_TABLE = 'F_Company_EU_Contact'

                                                AND PP_FIELD = 'Job_Role'

                                      ) d ON a.Job_Role = d.PP_VALUE

5、还有一个核心的性能问题——视图，执行计划大量并行度运算，这个本来没多大问题，但是使用视图的话，要在执行视图的时候才编译各个表，然后生成执行计划，编译并生成执行计划是非常耗时耗资源的操作，如果没必要，就别用视图了，直接操作实体表快的多，也可以换成存储过程，这个是预编译的，能提高速度。
6、考虑一下是否有需要返回那么多列

上面的东西你改一下看看有没有效果。没有实际环境很难帮你调
發糞塗牆 2013-03-15
  • 打赏
  • 举报
 回复
私信给你了，发到我邮箱吧，视力不好，看不清楚
媛爱 2013-03-15
  • 打赏
  • 举报
 回复
期待版主回复……
媛爱 2013-03-15
  • 打赏
  • 举报
 回复
这样行吗 ？
媛爱 2013-03-15
  • 打赏
  • 举报
 回复
szm341 2013-03-14
  • 打赏
  • 举报
 回复
引用 7 楼 DBA_Huangzj 的回复:
现在有空了
向版主学习学习分析~
發糞塗牆 2013-03-14
  • 打赏
  • 举报
 回复
现在有空了
引用 5 楼 szm341 的回复:
执行一下估计查询计划，看看有没有索引缺失，可能的话把查询计划贴上来，等黄版主有空了给你分析分析
發糞塗牆 2013-03-14
  • 打赏
  • 举报
 回复
弄成视图本来就不会有什么效率提升。贴执行计划吧
szm341 2013-03-14
  • 打赏
  • 举报
 回复
执行一下估计查询计划，看看有没有索引缺失，可能的话把查询计划贴上来，等黄版主有空了给你分析分析
加载更多回复（4）
Sql Server2016从入门到实战应用
关联查询，优化查询，分组排序、视图、索引和游标，主键的好处和用法，外键的建立依据、存储过程与触发器、数据库的备份与还原、安全与权限综合知识学习，定时作业计划的设计和定制等相关内容详解。 本课程设计的...
多表关联查询SQL优化
在进行多表关联查询的时候，需要在合适的地方添加索引避免笛卡尔积操作。 select x1 from a left b on a.x1 = b.x1; 针对于以上sql，需要明确哪个是驱动表，哪个是被驱动表，left join 的话，a是驱动表，b是被驱动...
连表查询SQL优化
最后优化的sql是先查询出alarm，fan表这个时间段的数据 再进行连表查 相比两种全表关联 大大减少了查询条数和等待时间。优化一次后 fan表中全表数据已经限制到了需要查询的时间 按照这个逻辑 我们把alarm表也限制在...
mysql多表查询sql优化_SQL多表查询优化
SQL优化1.执行路径:ORACLE的这个功能大大地提高了SQL的执行性能并节省了内存的使用:我们发现,单表数据的统计比多表统计的速度完全是两个概念.单表统计可能只要0.02秒,但是2张表联合统计就可能要几十秒了.这是因为...
sqlserver 多表查询优化
俩个表都需要加 如果不加索引 比如 主表ID是 3 2 1 子表没加索引 1 2 1 1 2 3 3 1 每一次查询主表 1 的时候会遍历一次全子表 也就是 ...
MS-SQL Server

34,587

社区成员

254,588

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

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

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

+ 用AI写文章