昨天研究sql优化,从需要4分钟优化到不到1秒,倒腾到晚上12点多,不敢私存,贴出来大家共享一下

昨天研究sql优化,从需要4分钟优化到不到1秒,倒腾到晚上12点多,不敢私存,贴出来大家共享一下:

 1 ---------------------------写法一:下面的脚本执行了4分02秒---------------------------------
 2 select shoufei.sYuYue, 
 3         shoufei.sCheType, shoufei.sCheID, shoufei.sCheZhu, shoufei.sPhone as sChePhone, 
 4         max(shoufei.dDate) as dShoufei, 
 5         min((case when shouFei.lRed=1 then -1 else 1 end)* 1) as nCount, 
 6         sum((case when shouFei.lRed=1 then -1 else 1 end)* shoufeiList.nMoney) as nListMoney, 
 7         sum((case when shouFei.lRed=1 then -1 else 1 end)* 
 8             (shoufeiList.nEmpBonus + shoufeiList.nMoney*shoufeiList.nEmpRate) ) as nEmpBonus
 9         /*sum( (case when shouFei.lRed=1 then -1 else 1 end) * 
10              isNull( select sum(nEmpBonus + nMoney*nEmpRate) as nEmpBonus from vShouFeiList as list where sID=shoufei.sID), 0) 
11             ) as nEmpBonus */
12     from ShouFei /*vShouFei*/ as shoufei 
13         inner join ShouFeiList as shoufeiList on shoufei.sID=shoufeilist.sID 
14     where shoufei.sYuYue<>'' 
15          and (shoufei.dDate>='2018-04-01'  And shoufei.dDate<='2018-04-27 23:59:59')  
16     group by shoufei.sYuYue, shoufei.sCheType, shoufei.sCheID, shoufei.sCheZhu,shoufei.sPhone 
17 
18 
19 
20 ---------------------写法二:速度时快时慢,快时用了3秒,慢时用了3分30秒----------------------------
21 select shoufei.sYuYue, 
22         shoufei.sCheType, shoufei.sCheID, shoufei.sCheZhu, shoufei.sPhone as sChePhone, 
23         max(shoufei.dDate) as dShoufei, 
24         min((case when shouFei.lRed=1 then -1 else 1 end)* 1) as nCount,  
25         sum((case when shouFei.lRed=1 then -1 else 1 end)* shoufei.nListMoney) as nListMoney, 
26         sum((case when shouFei.lRed=1 then -1 else 1 end)* shoufeiList.nEmpBonus) as nEmpBonus 
27         --sum( (case when shouFei.lRed=1 then -1 else 1 end) * 
28         --     isNull( (select sum(nEmpBonus + nMoney*nEmpRate) as nEmpBonus from vShouFeiList as list where sID=shoufei.sID), 0) 
29         --    ) as nEmpBonus 
30         --    isNull(
31         --        (select sum((case when shouFei.lRed=1 then -1 else 1 end) * (nEmpBonus + nMoney*nEmpRate)) as nEmpBonus 
32         --        from vShouFeiList as list where sID=shoufei.sID), 0) as nEmpBonus 
33     from ShouFei /*vShouFei*/ as shoufei 
34         inner join (
35             select sID, sum(nEmpBonus + nMoney*nEmpRate) as nEmpBonus from ShouFeiList group by sID 
36         ) as shoufeiList on shoufei.sID=shoufeiList.sID 
37     where 1=1 
38         and shoufei.sYuYue<>'' 
39         and (shoufei.dDate>='2018-04-01'  And shoufei.dDate<='2018-04-27 23:59:59')  
40     group by shoufei.sYuYue,  shoufei.sCheType, shoufei.sCheID, shoufei.sCheZhu,shoufei.sPhone 
41 
42 
43 
44 
45 --------------------------------写法三:脚本执行了不到1秒---------------------------------------------
46 select shoufei.sYuYue, 
47         shoufei.sCheType, shoufei.sCheID, shoufei.sCheZhu, shoufei.sPhone as sChePhone, 
48         max(shoufei.dDate) as dShoufei, 
49         min((case when shouFei.lRed=1 then -1 else 1 end)* 1) as nCount,  
50         sum((case when shouFei.lRed=1 then -1 else 1 end)* shoufei.nListMoney) as nListMoney, 
51         sum((case when shouFei.lRed=1 then -1 else 1 end)* shoufeiList.nEmpBonus) as nEmpBonus 
52         --sum( (case when shouFei.lRed=1 then -1 else 1 end) * 
53         --     isNull( (select sum(nEmpBonus + nMoney*nEmpRate) as nEmpBonus from vShouFeiList as list where sID=shoufei.sID), 0) 
54         --    ) as nEmpBonus 
55         --    isNull(
56         --        (select sum((case when shouFei.lRed=1 then -1 else 1 end) * (nEmpBonus + nMoney*nEmpRate)) as nEmpBonus 
57         --        from vShouFeiList as list where sID=shoufei.sID), 0) as nEmpBonus 
58     from ShouFei /*vShouFei*/ as shoufei 
59         inner join (
60             select shoufeilist.sID, sum(nEmpBonus + shoufeilist.nMoney*nEmpRate) as nEmpBonus from ShouFeiList inner join ShouFei on shoufei.sID=shoufeilist.sID
61                 where shoufei.sYuYue<>'' and (shoufei.dDate>='2018-04-01'  And shoufei.dDate<='2018-04-27 23:59:59')  
62                 group by shoufeilist.sID
63         ) as shoufeiList on shoufei.sID=shoufeiList.sID 
64     where 1=1 
65         --    and shoufei.sYuYue<>'' 
66         --    and (shoufei.dDate>='2018-04-01'  And shoufei.dDate<='2018-04-27 23:59:59')  
67     group by shoufei.sYuYue,  shoufei.sCheType, shoufei.sCheID, shoufei.sCheZhu,shoufei.sPhone 

 

posted @ 2018-04-28 15:35  海宏软件  阅读(262)  评论(0编辑  收藏  举报