表假脱机

[zestsnake]

在执行计划中，有个步骤是表假脱机 lazy spool ,开销占了 92%

输出的列表 为 cust.cur.cur_name

是不是影响了查询的速度？

[zestsnake]

cur 表的索引 是cur_dm

语句很长，稍后贴上来

[吉普赛的歌]

引用 5 楼 zestsnake 的回复:

cur 表的索引 是cur_dm 语句很长，稍后贴上来

为什么不贴 #4 sql 执行的结果截图呢？ 全是一鳞半爪的信息……

[zestsnake]

[zestsnake]

弱弱的问下，执行计划好大一块，用什么工具可以截取下来？

[吉普赛的歌]

那你只贴 sql 就可以了。

[吉普赛的歌]

截图可以分几块贴的。 可以放大缩小……

[zestsnake]

[zestsnake]

[吉普赛的歌]

sql 在哪里？

[zestsnake]

select sort,cate,  userdb_num,  
userdb_name ,balance_cur, cur_name,userdb_branchname,  
hgwd,  userdb_branchnum,  balance_balance_jf, balance_rj, 
balance_yj,balance_bzr , balance_bsy,balance_bsyn, balance_bqyn  
From  ( select 1 sort , '网点汇总' cate , null userdb_num ,null userdb_name  , 
balance_cur as balance_cur,  MAX(cur_name) as cur_name ,MAX(userdb_branchname) 
as userdb_branchname ,  null userdb_branchnum , MAX(hgwd) as hgwd,  
SUM(balance_balance_jf) as balance_balance_jf ,  SUM(balance_rj) as balance_rj, 
SUM(balance_yj) as balance_yj,SUM(balance_bzr) as balance_bzr,  
SUM(balance_bsy) as balance_bsy ,SUM(balance_bsyn) as balance_bsyn,  
SUM(balance_bqyn) as balance_bqyn  
from  ( select userdb_num ,userdb_name as userdb_name  ,balance_cur as balance_cur, 
userdb_branchnum as userdb_branchnum, cur_name as cur_name ,userdb_branchname as userdb_branchname , 
balance_managernum as balance_managernum , balance_managerbranch as balance_managerbranch , 
e.branch_name as hgwd,  balance_balance_jf as balance_balance_jf ,  balance_rj as balance_rj, 
balance_yj as balance_yj,balance_bzr as balance_bzr,  balance_bsy as balance_bsy ,
balance_bsyn as balance_bsyn,  balance_bqyn as balance_bqyn , balance_branchsys as balance_branchsys , 
balance_subaccount as balance_subaccount  
from  ( select distinct userdb_num ,userdb_name , userdb_branchnum, 
branch_name as userdb_branchname  from (  select userdb_num ,userdb_name , 
userdb_branchnum,b.branch_name  from userdb a  left join branch b on a.userdb_branchnum=b.branch_num  
where userdb_branchnum in ( '0002', '0005', '0010', '0050', '0051', '0052', 
'0053', '0054', '0055', '0088', '0099', '0230', '0231', '0234', '0235', '0236', 
'0237', '0238', '1014', '2302', '2303', '2305', '2307', '2310', '2314', '2315', '2317',
'2318', '2319', '2320', '2321', '2322', '2323', '2324', '2326', '2327', '2328', '2329', '2331') 
union all  select distinct  ManagerAcc_managerNum,ManagerAcc_managerName,
a.ManagerAcc_branch,b.branch_name  from ManagerAccount a   
left join branch b on a.ManagerAcc_branch =b.branch_num   
where  ManagerAcc_Accbranch in ( '0002', '0005', '0010', '0050', '0051', '0052', 
'0053', '0054', '0055', '0088', '0099', '0230', '0231', '0234', '0235', '0236', '0237', 
'0238', '1014', '2302', '2303', '2305', '2307', '2310', '2314', '2315', '2317', '2318',
 '2319', '2320', '2321', '2322', '2323', '2324', '2326', '2327', '2328', '2329', '2331') ) t ) 
 a  left join balance b on a.userdb_num = b.balance_managernum  l
 eft join cur c on b.balance_cur= c.cur_dm  
 left join branch e on b.balance_branchSys= e.branch_num   
 where 1=1  and b.balance_cur ='001' and  b.balance_date ='2018-09-28'
  and b.balance_branchsys in  ( '0002', '0005', '0010', '0050', '0051', '0052', '0053', 
  '0054', '0055', '0088', '0099', '0230', '0231', '0234', '0235', '0236', '0237', '0238', 
  '1014', '2302', '2303', '2305', '2307', '2310', '2314', '2315', '2317', '2318', '2319', 
  '2320', '2321', '2322', '2323', '2324', '2326', '2327', '2328', '2329', '2331')) mmm  where 1=1 
   group by hgwd ,balance_cur  union all  select 2 sort , '户管汇总' cate ,null userdb_num ,
   null userdb_name  ,balance_cur as balance_cur,  MAX(cur_name) as cur_name ,
   MAX(userdb_branchname) as userdb_branchname ,  userdb_branchnum , MAX(hgwd) as hgwd, 
   SUM(balance_balance_jf) as balance_balance_jf ,  SUM(balance_rj) as balance_rj, SUM(balance_yj)
    as balance_yj,SUM(balance_bzr) as balance_bzr,  SUM(balance_bsy) as balance_bsy ,
    SUM(balance_bsyn) as balance_bsyn,  SUM(balance_bqyn) as balance_bqyn  
    from  ( select userdb_num ,userdb_name as userdb_name  ,
    balance_cur as balance_cur, userdb_branchnum as userdb_branchnum, 
    cur_name as cur_name ,userdb_branchname as userdb_branchname ,
     balance_managernum as balance_managernum , 
     balance_managerbranch as balance_managerbranch , e.branch_name as hgwd, 
      balance_balance_jf as balance_balance_jf ,  balance_rj as balance_rj, balance_yj
       as balance_yj,balance_bzr as balance_bzr,  balance_bsy as balance_bsy ,balance_bsyn as balance_bsyn, 
        balance_bqyn as balance_bqyn , balance_branchsys as balance_branchsys ,
     balance_subaccount as balance_subaccount  
     from  ( select distinct userdb_num ,userdb_name , userdb_branchnum, 
     branch_name as userdb_branchname  from (  select userdb_num ,userdb_name , 
     userdb_branchnum,b.branch_name  from userdb a  left join branch b on a.userdb_branchnum=b.branch_num  
     where userdb_branchnum in ( '0002', '0005', '0010', '0050', '0051', '0052', '0053', '0054',
     '0055', '0088', '0099', '0230', '0231', '0234', '0235', '0236', '0237', '0238', '1014', 
     '2302', '2303', '2305', '2307', '2310', '2314', '2315', '2317', '2318', '2319', '2320', 
     '2321', '2322', '2323', '2324', '2326', '2327', '2328', '2329', '2331') 
     union all  select distinct  ManagerAcc_managerNum,ManagerAcc_managerName,
     a.ManagerAcc_branch,b.branch_name  from ManagerAccount a   
     left join branch b on a.ManagerAcc_branch =b.branch_num   
     where  ManagerAcc_Accbranch in ( '0002', '0005', '0010', '0050', '0051', 
     '0052', '0053', '0054', '0055', '0088', '0099', '0230', '0231', '0234', '0235', 
     '0236', '0237', '0238', '1014', '2302', '2303', '2305', '2307', '2310', '2314', '2315',
      '2317', '2318', '2319', '2320', '2321', '2322', '2323', '2324', '2326', '2327', '2328', 
      '2329', '2331') ) t ) a  left join balance b on a.userdb_num = b.balance_managernum  
      left join cur c on b.balance_cur= c.cur_dm  left join branch e on b.balance_branchSys= e.branch_num   
      where 1=1  and b.balance_cur ='001' and  b.balance_date ='2018-09-28' and b.balance_branchsys
       in  ( '0002', '0005', '0010', '0050', '0051', '0052', '0053', '0054', '0055', '0088', '0099', 
       '0230', '0231', '0234', '0235', '0236', '0237', '0238', '1014', '2302', '2303', '2305', '2307', 
       '2310', '2314', '2315', '2317', '2318', '2319', '2320', '2321', '2322', '2323', '2324', '2326', 
       '2327', '2328', '2329', '2331')) mmm  where 1=1  group by hgwd ,userdb_branchnum,balance_cur 
     union all  
    select 3 sort , '经理汇总' cate  ,max(userdb_num) as userdb_num  ,
    mAX(userdb_name)as userdb_name  ,balance_cur as balance_cur, 
     MAX(cur_name) as cur_name ,MAX(userdb_branchname) as userdb_branchname ,  userdb_branchnum ,
      MAX(hgwd) as hgwd,  SUM(balance_balance_jf) as balance_balance_jf , 
   SUM(balance_rj) as balance_rj, SUM(balance_yj) as balance_yj,SUM(balance_bzr) as balance_bzr,  
   SUM(balance_bsy) as balance_bsy ,SUM(balance_bsyn) as balance_bsyn,  
   SUM(balance_bqyn) as balance_bqyn  from  ( select userdb_num ,userdb_name as userdb_name  ,
   balance_cur as balance_cur, userdb_branchnum as userdb_branchnum, cur_name as cur_name ,
   userdb_branchname as userdb_branchname , balance_managernum as balance_managernum ,
    balance_managerbranch as balance_managerbranch , e.branch_name as hgwd,  
    balance_balance_jf as balance_balance_jf ,  balance_rj as balance_rj, 
    balance_yj as balance_yj,balance_bzr as balance_bzr,  balance_bsy as balance_bsy ,
    balance_bsyn as balance_bsyn,  balance_bqyn as balance_bqyn , balance_branchsys as balance_branchsys , 
    balance_subaccount as balance_subaccount  from  ( select distinct userdb_num ,userdb_name , 
    userdb_branchnum, branch_name as userdb_branchname  from (  select userdb_num ,userdb_name , 
    userdb_branchnum,b.branch_name  from userdb a 
     left join branch b on a.userdb_branchnum=b.branch_num 
   where userdb_branchnum in ( '0002', '0005', '0010', '0050', '0051', '0052', '0053', 
   '0054', '0055', '0088', '0099', '0230', '0231', '0234', '0235', '0236', '0237',
    '0238', '1014', '2302', '2303', '2305', '2307', '2310', '2314', '2315', '2317', '2318',
    '2319', '2320', '2321', '2322', '2323', '2324', '2326', '2327', '2328', '2329', '2331') 
    union all  select distinct  ManagerAcc_managerNum,ManagerAcc_managerName,
    a.ManagerAcc_branch,b.branch_name  from ManagerAccount a   
    left join branch b on a.ManagerAcc_branch =b.branch_num   
    where  ManagerAcc_Accbranch in ( '0002', '0005', '0010', '0050', '0051', '0052',
     '0053', '0054', '0055', '0088', '0099', '0230', '0231', '0234', '0235', '0236', 
     '0237', '0238', '1014', '2302', '2303', '2305', '2307', '2310', '2314', '2315', 
     '2317', '2318', '2319', '2320', '2321', '2322', '2323', '2324', '2326', '2327', '2328', 
     '2329', '2331') ) t ) a  left join balance b on a.userdb_num = b.balance_managernum  
     left join cur c on b.balance_cur= c.cur_dm  left join branch e on b.balance_branchSys= e.branch_num   
     where 1=1  and b.balance_cur ='001' and  b.balance_date ='2018-09-28' and 
     b.balance_branchsys in  ( '0002', '0005', '0010', '0050', '0051', '0052', '0053', 
     '0054', '0055', '0088', '0099', '0230', '0231', '0234', '0235', '0236', '0237', '0238', 
     '1014', '2302', '2303', '2305', '2307', '2310', '2314', '2315', '2317', 
     '2318', '2319', '2320', '2321', '2322', '2323', '2324', '2326', '2327', '2328', '2329',
      '2331')) mmm  where 1=1  group by hgwd,userdb_branchnum,balance_managernum ,balance_cur) 
       amm   order by hgwd,userdb_branchnum,sort 

[zestsnake]

比较长，麻烦大神了

[吉普赛的歌]

化繁为简， 化整为零。 这么长的sql， 你困难， 数据库同样也困难。 嵌套了 5 层， 其中有没有可以重用的部分数据呢？ 建议： 1. 拆出所有可以拆的部分， 先保存到临时表， 再组合查询 ; 2. 如果拆出的临时表， 有可以重用的部分， 那就更好。 差别较少的， 尽量想办法同时满足。 3. 临时表可以再建立连接字段、条件字段的索引 4. 所有的查询加上 with(nolock), 如: select * from a with(nolock) inner join b with(nolock) on a.id=b.id 做好这些， 你原来2秒的， 1秒不到就可以了。

[zestsnake]

还有个问题，为什么比如9.25的数据很快，到9、28就慢了很多， 也就增加了不到10w的记录

[吉普赛的歌]

你先拆为临时表了再说吧。 那么复杂的sql ， 怎么看？ 没人是神仙。 拆完了， 即使慢， 那拆后的sql 也只是小段了， 你自己可以每一段加一个时间提示， 看到底哪一段慢？ 知道具体是哪里慢， 再来优化就容易了。

[zestsnake]

感谢大神的指点。
仔细看了下语句执行的结果 ，发现worktable 的逻辑读到了7w多次，原因可能是某个嵌套的中间表没有索引。
把这个表改成了临时表，并对连接字段加了索引，秒出 . 再次感谢

顺便再请教下。比如查9-16的数据，worktable 的逻辑读是6w次，查询结果，是3秒出，而9-26的逻辑读是7w多次，查询结果是12秒出。
索引，统计信息都做过了。1W次的逻辑读，会有这么大的影响吗？

[吉普赛的歌]

引用 20 楼 zestsnake 的回复:

感谢大神的指点。
仔细看了下语句执行的结果 ，发现worktable 的逻辑读到了7w多次，原因可能是某个嵌套的中间表没有索引。
把这个表改成了临时表，并对连接字段加了索引，秒出 . 再次感谢

顺便再请教下。比如查9-16的数据，worktable 的逻辑读是6w次，查询结果，是3秒出，而9-26的逻辑读是7w多次，查询结果是12秒出。
索引，统计信息都做过了。1W次的逻辑读，会有这么大的影响吗？

9-16 与 9-26 的执行计划一模一样？
SQL Server 中有一些阈值， 可能正好超过了阈值， 导致产生变化。

举个类似的东西。
比如： select top 100 和 select top 101 ， 你觉得没什么区别， 但 top 101 在大表排序就是要慢很多， 因为 top 100 是 sql server 的一个阈值。

不要拘泥于理论。能解决就好吧。
本身太过复杂的 sql , 就该拆临时表。 拆完还慢的， 再来提问题才合适。

[zestsnake]

执行计划一样的 。速度慢了好多

[吉普赛的歌]

拆临时表、加索引之后还是一个3秒，一个12秒？

两者结果集行数一样？

[zestsnake]

拆临时表、加索引之后，不同的日期都是秒出。

我的意思是 ， 在没拆拆临时表、加索引， 9.11日期的 3秒 ，9.26日期的 12秒 ，返回的行数也就多10几条，当然是通过一系列的汇总的

实际明细数据9.26比9.11多个 2-3千条

[zestsnake]

结贴给分结贴给分