首页 > 代码库 > 如何对于几百行SQL语句进行优化?

如何对于几百行SQL语句进行优化?

1.最近在开发中遇到的一些关于几百行SQL语句做查询的问题,需要如何的解决优化SQL这确实是个问题,对于当下的ORM 框架 EF 以及其他的一些的开源的框架例如Drapper ,以及Sqlite-Sugar 等等,对于查询的速度以及性能确实还不错,但是对于几百条的SQL语句那么可能就不行了这些轻量级的框架扛不住。当在写SQL语句需要注意的规则都无法提高速率的时候,个人认为还是需要传统的ADO.NET 参数化的SQL来进行解决问题。

下面是我最近开发当中遇到的一些复杂的SQL的语句如何处理以及优化查询我还在找确切的办法来进行解决。还在进行中,当然对于数据库确实我进行一定的处理,还是有一定的效果的。下面就进入正题吧!

2.花了2天时间写的SQL查询月结算历史的数据

  1  select                                                                                                                                                                                                            
  2              sum(case when indentdate >= ‘2015-11-28 00:00:00‘ and                                                                                                         
  3                   indentdate <= ‘2015-11-28 23:59:59‘ and                                                                                                             
  4                   indenttype = 0 and indent_step = ‘00‘ then 1 else 0 end) totalcount1, --本月总数量                                                                                                              
  5        convert(int,sum(case when indentdate >= ‘2015-11-28 00:00:00‘ and                                                                                            
  6                   indentdate <= ‘2015-11-28 23:59:59‘ and                                                                                                             
  7                   indenttype = 0 and indent_step = ‘00‘ then                                                                                                                                                      
  8               t1.totalpay else 0 end)) totalpay1 ,---本月总金额                                                                                                                                                      
  9        sum(case when indentdate >=‘2015-11-28 00:00:00‘ and                                                                                                       
 10                   t1.indentdate <= ‘2015-11-28 23:59:59‘ and                                                                                                          
 11                   t2.modifieddate >= ‘2015-11-28 00:00:00‘ and                                                                                                      
 12                   t2.modifieddate <= ‘2015-11-28 23:59:59‘ and                                                                                                        
 13                   t1.indentstatus=‘020‘ and indenttype = 0 and indent_step = ‘00‘ then                                                                                             
 14               1 else 0 end)                                                                                                                                                                                       
 15            + sum(case when   indentdate >=‘2015-11-28 00:00:00‘ and                                                                                                  
 16                   indentdate <= ‘2015-11-28 23:59:59‘ and t1.indentstatus=‘050‘                                                                              
 17                    and financedate >= ‘2015-11-28 00:00:00‘ and                                                                                
 18                   financedate <= ‘2015-11-28 23:59:59‘ and indenttype = 0 and                                                                                         
 19                   indent_step = ‘00‘ then                                                                                                                                                                         
 20               1 else 0 end)+sum(case when  indentdate >=‘2015-11-28 00:00:00‘ and indentdate <= ‘2015-11-28 23:59:59‘ and    financedate>=‘2015-11-28 00:00:00‘ and  financedate<=‘2015-11-28 23:59:59‘ and indentstatus IN (‘111‘,‘112‘)  and  indenttype=1 then 1 else 0 end)  totalcount2,--本月失效数量                                                                                                                                                           
 21        convert(int,sum(case when  indentdate >=‘2015-11-28 00:00:00‘  and                                                                                         
 22                   t1.indentdate <= ‘2015-11-28 23:59:59‘ and t2.modifieddate >= ‘2015-11-28 00:00:00‘ and               
 23                   t2.modifieddate <= ‘2015-11-28 23:59:59‘   and t1.indentstatus=‘020‘                                                                        
 24                     and indenttype = 0 and                                                                                                                                                    
 25                   indent_step = ‘00‘ then                                                                                                                                                                         
 26               t1.totalpay  else 0 end)                                                                                                                                                                               
 27            + sum(case when  indentdate >=‘2015-11-28 00:00:00‘ and                                                                                                 
 28                   indentdate <= ‘2015-11-28 23:59:59‘   and t1.indentstatus=‘050‘                                                                              
 29                     and financedate >= ‘2015-11-28 00:00:00‘ and                                                                                
 30                   financedate <= ‘2015-11-28 23:59:59‘ and indenttype = 0 and                                                                                         
 31                   indent_step = ‘00‘ then                                                                                                                                                                         
 32               t1.totalpay  else 0 end))-sum(case when indentdate >=‘2015-11-28 00:00:00‘ and indentdate <= ‘2015-11-28 23:59:59‘ and financedate>=‘2015-11-28 00:00:00‘ and t1.financedate<=‘2015-11-28 23:59:59‘ and t1.indentstatus IN (‘111‘,‘112‘)  and  indenttype=1 then t1.totalpay  else 0 end ) totalpay2, ---本月失效金额                                                                                                                                                   
 33                                                                                                                                                                                                                   
 34        sum(case when t1.indentdate<= ‘2015-11-28 00:00:00‘ and t2.modifieddate >= ‘2015-11-28 00:00:00‘ and           
 35                   t2.modifieddate <= ‘2015-11-28 23:59:59‘ and t1.indentstatus=‘020‘                                                                         
 36                     and indenttype = 0 and                                                                                                                                                    
 37                   indent_step = ‘00‘ then                                                                                                                                                                         
 38               1 else 0 end)                                                                                                                                                                                       
 39            + sum(case when                                                                                                                                                                                        
 40                   indentdate <= ‘2015-11-28 00:00:00‘ and t1.indentstatus=‘050‘                                                                            
 41                    and financedate >= ‘2015-11-28 00:00:00‘ and                                                                                
 42                   financedate <= ‘2015-11-28 23:59:59‘ and indenttype = 0 and                                                                                         
 43                   indent_step = ‘00‘ then                                                                                                                                                                         
 44               1                                                                                                                                                                                                   
 45              else                                                                                                                                                                                                 
 46               0      
 47               --------以上                                                                                                                                                                                             
 48            end)+sum(case when   indentdate <= ‘2015-11-28 00:00:00‘ and  financedate>=‘2015-11-28 00:00:00‘ and  financedate<=‘2015-11-28 23:59:59‘ and indentstatus IN (‘111‘,‘112‘)  and  indenttype=1 then 1 else 0 end) totalcount3 ,---历史失效数量                                                                                                                                                                      
 49        convert(int,sum(case                                                                                                                                                                                       
 50              when t1.indentdate<= ‘2015-11-28 00:00:00‘ and t2.modifieddate >= ‘2015-11-28 00:00:00‘ and              
 51                   t2.modifieddate <= ‘2015-11-28 23:59:59‘   and t1.indentstatus=‘020‘                                                                         
 52                     and indenttype = 0 and                                                                                                                                                    
 53                   indent_step = ‘00‘ then                                                                                                                                                                         
 54               t1.totalpay                                                                                                                                                                                            
 55              else                                                                                                                                                                                                 
 56               0                                                                                                                                                                                                   
 57            end)+                                                                                                                                                                                                  
 58        sum(case                                                                                                                                                                                                   
 59              when                                                                                                                                                                                                 
 60                   indentdate <= ‘2015-11-28 00:00:00‘  and t1.indentstatus=‘050‘                                                                            
 61                    and financedate >= ‘2015-11-28 00:00:00‘ and                                                                                
 62                   financedate <= ‘2015-11-28 23:59:59‘ and indenttype = 0 and                                                                                         
 63                   indent_step = ‘00‘ then                                                                                                                                                                         
 64               t1.totalpay                                                                                                                                                                                            
 65              else                                                                                                                                                                                                 
 66               0                                                                                                                                                                                                   
 67            end) -sum(case when   indentdate <= ‘2015-11-28 00:00:00‘ and financedate>=‘2015-11-28 00:00:00‘ and  financedate<=‘2015-11-28 23:59:59‘ and t1.indentstatus IN (‘111‘,‘112‘)  and  indenttype=1 then t1.totalpay  else 0 end )) totalpay3, --历史失效金额                                                                                                                                                                        
 68              sum(case                                                                                                                                                                                       
 69              when indentdate >= ‘2015-11-28 00:00:00‘ and                                                                                                           
 70                   indentdate <= ‘2015-11-28 23:59:59‘ and indenttype = 0 and                                                                                          
 71                   indent_step = ‘00‘ then                                                                                                                                                                         
 72               1                                                                                                                                                                                     
 73              else                                                                                                                                                                                                 
 74               0                                                                                                                                                                                                   
 75            end) -                                                                                                                                                                                                 
 76         (sum(case                                                                                                                                                                                                  
 77              when indentdate >= ‘2015-11-28 00:00:00‘ and indentdate <= ‘2015-11-28 23:59:59‘and t2.modifieddate>= ‘2015-11-28 00:00:00‘ and                                                                                                       
 78                   t2.modifieddate <= ‘2015-11-28 23:59:59‘                                                                           
 79                    and t1.indentstatus=‘020‘ and indenttype = 0 and                                                                                                                                                    
 80                   indent_step = ‘00‘ then                                                                                                                                                                         
 81               1                                                                                                                                                                                     
 82              else                                                                                                                                                                                                 
 83               0                                                                                                                                                                                                   
 84            end)                                                                                                                                                                                                   
 85            +sum(case                                                                                                                                                                                              
 86              when                                                                                                                                                                                                 
 87                   indentdate >= ‘2015-11-28 00:00:00‘ and indentdate <= ‘2015-11-28 23:59:59‘   and                                                                              
 88                      t1.indentstatus=‘050‘ and financedate >= ‘2015-11-28 00:00:00‘ and                                                                                
 89                   financedate <= ‘2015-11-28 23:59:59‘ and indenttype = 0 and                                                                                         
 90                   indent_step = ‘00‘ then                                                                                                                                                                         
 91               1                                                                                                                                                                                      
 92              else                                                                                                                                                                                                 
 93               0                                                                                                                                                                                                   
 94            end)+sum(case                                                                                                                                                                                          
 95              when                                                                                                                                                                                                 
 96                 indentdate >= ‘2015-11-28 00:00:00‘ and indentdate <= ‘2015-11-28 23:59:59‘   and t1.indentstatus IN (‘111‘,‘112‘)  and financedate >= ‘2015-11-28 00:00:00‘ and                                                                            
 97                   financedate <= ‘2015-11-28 23:59:59‘ and indenttype = 1  then                                                                                       
 98               1                                                                                                                                                                                     
 99              else                                                                                                                                                                                                 
100               0                                                                                                                                                                                                   
101            end)) totalcount4,---本月应结算订单数=本月总订单数-本月失效订单数                                                                                                                                      
102        convert(int,sum(case                                                                                                                                                                                       
103              when indentdate >= ‘2015-11-28 00:00:00‘ and                                                                                                           
104                   indentdate <= ‘2015-11-28 23:59:59‘ and indenttype = 0 and                                                                                          
105                   indent_step = ‘00‘ then                                                                                                                                                                         
106               t1.totalpay                                                                                                                                                                                            
107              else                                                                                                                                                                                                 
108               0                                                                                                                                                                                                   
109            end) -                                                                                                                                                                                                 
110         sum(case                                                                                                                                                                                                  
111              when indentdate >= ‘2015-11-28 00:00:00‘ and indentdate <= ‘2015-11-28 23:59:59‘and t2.modifieddate>= ‘2015-11-28 00:00:00‘ and                                                                                                       
112                   t2.modifieddate <= ‘2015-11-28 23:59:59‘                                                                           
113                    and t1.indentstatus=‘020‘ and indenttype = 0 and                                                                                                                                                    
114                   indent_step = ‘00‘ then                                                                                                                                                                         
115               t1.totalpay                                                                                                                                                                                            
116              else                                                                                                                                                                                                 
117               0                                                                                                                                                                                                   
118            end)                                                                                                                                                                                                   
119            -sum(case                                                                                                                                                                                              
120              when                                                                                                                                                                                                 
121                   indentdate >= ‘2015-11-28 00:00:00‘ and indentdate <= ‘2015-11-28 23:59:59‘   and                                                                              
122                      t1.indentstatus=‘050‘ and financedate >= ‘2015-11-28 00:00:00‘ and                                                                                
123                   financedate <= ‘2015-11-28 23:59:59‘ and indenttype = 0 and                                                                                         
124                   indent_step = ‘00‘ then                                                                                                                                                                         
125               t1.totalpay                                                                                                                                                                                            
126              else                                                                                                                                                                                                 
127               0                                                                                                                                                                                                   
128            end)+sum(case                                                                                                                                                                                          
129              when                                                                                                                                                                                                 
130                 indentdate >= ‘2015-11-28 00:00:00‘ and indentdate <= ‘2015-11-28 23:59:59‘   and t1.indentstatus IN (‘111‘,‘112‘)  and financedate >= ‘2015-11-28 00:00:00‘ and                                                                            
131                   financedate <= ‘2015-11-28 23:59:59‘ and indenttype = 1  then                                                                                       
132               t1.totalpay                                                                                                                                                                                            
133              else                                                                                                                                                                                                 
134               0                                                                                                                                                                                                   
135            end)) totalpay4, --应结算金额                                                                                                                                                                          
136                                                                                                                                                                                                                   
137                 sum(case                                                                                                                                                                                                   
138              when indentdate >= ‘2015-11-28 00:00:00‘ and                                                                                                           
139                   indentdate <= ‘2015-11-28 23:59:59‘ and indenttype = 0 and                                                                                          
140                   indent_step = ‘00‘ and status=2 then                                                                                                                                                            
141               1                                                                                                                                                                                                   
142              else                                                                                                                                                                                                 
143               0                                                                                                                                                                                                   
144            end) -                                                                                                                                                                                                 
145         sum(case                                                                                                                                                                                                  
146              when t2.modifieddate>= ‘2015-11-28 00:00:00‘ and                                                                                                       
147                   t2.modifieddate <= ‘2015-11-28 23:59:59‘ and workorderstatus = ‘FAILED‘ and                                                                         
148                   curstep_id = ‘2A9B4B‘ and status=2 and indenttype = 0 and                                                                                                                                       
149                   indent_step = ‘00‘ then                                                                                                                                                                         
150                1                                                                                                                                                                                                  
151              else                                                                                                                                                                                                 
152               0                                                                                                                                                                                                   
153            end)                                                                                                                                                                                                   
154            -sum(case                                                                                                                                                                                              
155              when                                                                                                                                                                                                 
156                   indentdate <= ‘2015-11-28 23:59:59‘ and workorderstatus = ‘FAILED‘ and                                                                              
157                   curstep_id = ‘5CB9E0‘ and financedate >= ‘2015-11-28 00:00:00‘ and                                                                                
158                   financedate <= ‘2015-11-28 23:59:59‘ and status=2 and indenttype = 0 and                                                                            
159                   indent_step = ‘00‘ then                                                                                                                                                                         
160               1                                                                                                                                                                                                   
161              else                                                                                                                                                                                                 
162               0                                                                                                                                                                                                   
163            end)-sum(case                                                                                                                                                                                          
164              when                                                                                                                                                                                                 
165                   workorderstatus = ‘CLOSE‘ and financedate >= ‘2015-11-28 00:00:00‘ and                                                                            
166                   financedate <= ‘2015-11-28 23:59:59‘ and status=2 and indenttype = 1  then                                                                          
167               1                                                                                                                                                                                                   
168              else                                                                                                                                                                                                 
169               0                                                                                                                                                                                                   
170            end) totalcount5,                                                                                                                                                                                      
171                                                                                                                                                                                                                   
172         convert(int,sum(case                                                                                                                                                                                      
173              when indentdate >= ‘2015-11-28 00:00:00‘ and                                                                                                           
174                   indentdate <= ‘2015-11-28 23:59:59‘ and indenttype = 0 and                                                                                          
175                   indent_step = ‘00‘ and status=2 then                                                                                                                                                            
176               cust_partner_value                                                                                                                                                                                  
177              else                                                                                                                                                                                                 
178               0                                                                                                                                                                                                   
179            end) -                                                                                                                                                                                                 
180         sum(case                                                                                                                                                                                                  
181              when t2.modifieddate>= ‘2015-11-28 00:00:00‘ and                                                                                                       
182                   t2.modifieddate <= ‘2015-11-28 23:59:59‘ and workorderstatus = ‘FAILED‘ and                                                                         
183                   curstep_id = ‘2A9B4B‘ and status=2 and indenttype = 0 and                                                                                                                                       
184                   indent_step = ‘00‘ then                                                                                                                                                                         
185                cust_partner_value                                                                                                                                                                                 
186              else                                                                                                                                                                                                 
187               0                                                                                                                                                                                                   
188            end)                                                                                                                                                                                                   
189            -sum(case                                                                                                                                                                                              
190              when                                                                                                                                                                                                 
191                   indentdate <= ‘2015-11-28 23:59:59‘ and workorderstatus = ‘FAILED‘ and                                                                              
192                   curstep_id = ‘5CB9E0‘ and financedate >= ‘2015-11-28 00:00:00‘ and                                                                                
193                   financedate <= ‘2015-11-28 23:59:59‘ and status=2 and indenttype = 0 and                                                                            
194                   indent_step = ‘00‘ then                                                                                                                                                                         
195               cust_partner_value                                                                                                                                                                                  
196              else                                                                                                                                                                                                 
197               0                                                                                                                                                                                                   
198            end)-sum(case                                                                                                                                                                                          
199              when                                                                                                                                                                                                 
200                   workorderstatus = ‘CLOSE‘ and financedate >= ‘2015-11-28 00:00:00‘ and                                                                            
201                   financedate <= ‘2015-11-28 23:59:59‘ and status=2 and indenttype = 1  then                                                                          
202               cust_partner_value                                                                                                                                                                                  
203              else                                                                                                                                                                                                 
204               0                                                                                                                                                                                                   
205            end))  totalpay5,                                                                                                                                                                                      
206        sum(case                                                                                                                                                                                                   
207              when indentdate >= ‘2015-11-28 00:00:00‘ and                                                                                                           
208                   indentdate <= ‘2015-11-28 23:59:59‘ and indenttype = 0 and                                                                                          
209                   indent_step = ‘00‘ then                                                                                                                                                                         
210               1                                                                                                                                                                                  
211              else                                                                                                                                                                                                 
212               0                                                                                                                                                                                                   
213            end) -                                                                                                                                                                                                 
214         sum(case                                                                                                                                                                                                  
215              when t2.modifieddate>= ‘2015-11-28 00:00:00‘ and                                                                                                       
216                   t2.modifieddate <= ‘2015-11-28 23:59:59‘ and workorderstatus = ‘FAILED‘ and                                                                         
217                   curstep_id = ‘2A9B4B‘ and indenttype = 0 and                                                                                                                                                    
218                   indent_step = ‘00‘ then                                                                                                                                                                         
219                1                                                                                                                                                                                 
220              else                                                                                                                                                                                                 
221               0                                                                                                                                                                                                   
222            end)                                                                                                                                                                                                   
223            -sum(case                                                                                                                                                                                              
224              when                                                                                                                                                                                                 
225                   indentdate <= ‘2015-11-28 23:59:59‘ and workorderstatus = ‘FAILED‘ and                                                                              
226                   curstep_id = ‘5CB9E0‘ and financedate >= ‘2015-11-28 00:00:00‘ and                                                                                
227                   financedate <= ‘2015-11-28 23:59:59‘ and indenttype = 0 and                                                                                         
228                   indent_step = ‘00‘ then                                                                                                                                                                         
229               1                                                                                                                                                                                  
230              else                                                                                                                                                                                                 
231               0                                                                                                                                                                                                   
232            end)-sum(case                                                                                                                                                                                          
233              when                                                                                                                                                                                                 
234                   workorderstatus = ‘CLOSE‘ and financedate >= ‘2015-11-28 00:00:00‘ and                                                                            
235                   financedate <= ‘2015-11-28 23:59:59‘ and indenttype = 1  then                                                                                       
236               1                                                                                                                                                                                  
237              else                                                                                                                                                                                                 
238               0                                                                                                                                                                                                   
239            end) - (sum(case                                                                                                                                                                                       
240              when indentdate >= ‘2015-11-28 00:00:00‘ and                                                                                                           
241                   indentdate <= ‘2015-11-28 23:59:59‘ and indenttype = 0 and                                                                                          
242                   indent_step = ‘00‘ and status=2 then                                                                                                                                                            
243               1                                                                                                                                                                                                   
244              else                                                                                                                                                                                                 
245               0                                                                                                                                                                                                   
246            end) -                                                                                                                                                                                                 
247         sum(case                                                                                                                                                                                                  
248              when t2.modifieddate>= ‘2015-11-28 00:00:00‘ and                                                                                                       
249                   t2.modifieddate <= ‘2015-11-28 23:59:59‘ and workorderstatus = ‘FAILED‘ and                                                                         
250                   curstep_id = ‘2A9B4B‘ and status=2 and indenttype = 0 and                                                                                                                                       
251                   indent_step = ‘00‘ then                                                                                                                                                                         
252                1                                                                                                                                                                                                  
253              else                                                                                                                                                                                                 
254               0                                                                                                                                                                                                   
255            end)                                                                                                                                                                                                   
256            -sum(case                                                                                                                                                                                              
257              when                                                                                                                                                                                                 
258                   indentdate <= ‘2015-11-28 23:59:59‘ and workorderstatus = ‘FAILED‘ and                                                                              
259                   curstep_id = ‘5CB9E0‘ and financedate >= ‘2015-11-28 00:00:00‘ and                                                                                
260                   financedate <= ‘2015-11-28 23:59:59‘ and status=2 and indenttype = 0 and                                                                            
261                   indent_step = ‘00‘ then                                                                                                                                                                         
262               1                                                                                                                                                                                                   
263              else                                                                                                                                                                                                 
264               0                                                                                                                                                                                                   
265            end)-sum(case                                                                                                                                                                                          
266              when                                                                                                                                                                                                 
267                   workorderstatus = ‘CLOSE‘ and financedate >= ‘2015-11-28 00:00:00‘ and                                                                            
268                   financedate <= ‘2015-11-28 23:59:59‘ and status=2 and indenttype = 1  then                                                                          
269               1                                                                                                                                                                                                   
270              else                                                                                                                                                                                                 
271               0                                                                                                                                                                                                   
272            end)) totalcount6,                                                                                                                                                                                     
273        convert(int,sum(case                                                                                                                                                                                       
274              when indentdate >= ‘2015-11-28 00:00:00‘ and                                                                                                           
275                   indentdate <= ‘2015-11-28 23:59:59‘ and indenttype = 0 and                                                                                          
276                   indent_step = ‘00‘ then                                                                                                                                                                         
277               cust_partner_value                                                                                                                                                                                  
278              else                                                                                                                                                                                                 
279               0                                                                                                                                                                                                   
280            end) -                                                                                                                                                                                                 
281         sum(case                                                                                                                                                                                                  
282              when t2.modifieddate>= ‘2015-11-28 00:00:00‘ and                                                                                                       
283                   t2.modifieddate <= ‘2015-11-28 23:59:59‘ and workorderstatus = ‘FAILED‘ and                                                                         
284                   curstep_id = ‘2A9B4B‘ and indenttype = 0 and                                                                                                                                                    
285                   indent_step = ‘00‘ then                                                                                                                                                                         
286                cust_partner_value                                                                                                                                                                                 
287              else                                                                                                                                                                                                 
288               0                                                                                                                                                                                                   
289            end)                                                                                                                                                                                                   
290            -sum(case                                                                                                                                                                                              
291              when                                                                                                                                                                                                 
292                   indentdate <= ‘2015-11-28 23:59:59‘ and workorderstatus = ‘FAILED‘ and                                                                              
293                   curstep_id = ‘5CB9E0‘ and financedate >= ‘2015-11-28 00:00:00‘ and                                                                                
294                   financedate <= ‘2015-11-28 23:59:59‘ and indenttype = 0 and                                                                                         
295                   indent_step = ‘00‘ then                                                                                                                                                                         
296               cust_partner_value                                                                                                                                                                                  
297              else                                                                                                                                                                                                 
298               0                                                                                                                                                                                                   
299            end)-sum(case                                                                                                                                                                                          
300              when                                                                                                                                                                                                 
301                   workorderstatus = ‘CLOSE‘ and financedate >= ‘2015-11-28 00:00:00‘ and                                                                            
302                   financedate <= ‘2015-11-28 23:59:59‘ and indenttype = 1  then                                                                                       
303               cust_partner_value                                                                                                                                                                                  
304              else                                                                                                                                                                                                 
305               0                                                                                                                                                                                                   
306            end)-(sum(case                                                                                                                                                                                         
307              when indentdate >= ‘2015-11-28 00:00:00‘ and                                                                                                           
308                   indentdate <= ‘2015-11-28 23:59:59‘ and indenttype = 0 and                                                                                          
309                   indent_step = ‘00‘ and status=2 then                                                                                                                                                            
310               cust_partner_value                                                                                                                                                                                  
311              else                                                                                                                                                                                                 
312               0                                                                                                                                                                                                   
313            end) -                                                                                                                                                                                                 
314         sum(case                                                                                                                                                                                                  
315              when t2.modifieddate>= ‘2015-11-28 00:00:00‘ and                                                                                                       
316                   t2.modifieddate <= ‘2015-11-28 23:59:59‘ and workorderstatus = ‘FAILED‘ and                                                                         
317                   curstep_id = ‘2A9B4B‘ and status=2 and indenttype = 0 and                                                                                                                                       
318                   indent_step = ‘00‘ then                                                                                                                                                                         
319                cust_partner_value                                                                                                                                                                                 
320              else                                                                                                                                                                                                 
321               0                                                                                                                                                                                                   
322            end)                                                                                                                                                                                                   
323            -sum(case                                                                                                                                                                                              
324              when                                                                                                                                                                                                 
325                   indentdate <= ‘2015-11-28 23:59:59‘ and workorderstatus = ‘FAILED‘ and                                                                              
326                   curstep_id = ‘5CB9E0‘ and financedate >= ‘2015-11-28 00:00:00‘ and                                                                                
327                   financedate <= ‘2015-11-28 23:59:59‘ and status=2 and indenttype = 0 and                                                                            
328                   indent_step = ‘00‘ then                                                                                                                                                                         
329               cust_partner_value                                                                                                                                                                                  
330              else                                                                                                                                                                                                 
331               0                                                                                                                                                                                                   
332            end)-sum(case                                                                                                                                                                                          
333              when                                                                                                                                                                                                 
334                   workorderstatus = ‘CLOSE‘ and financedate >= ‘2015-11-28 00:00:00‘ and                                                                            
335                   financedate <= ‘2015-11-28 23:59:59‘ and status=2 and indenttype = 1  then                                                                          
336               cust_partner_value                                                                                                                                                                                  
337              else                                                                                                                                                                                                 
338               0                                                                                                                                                                                                   
339            end))) totalpay6                                                                                                                                                                                       
340  from tabcindent t1 left join workorder2 t2 on t1.relation_id=t2.workorder_guid                
341  --left join customer t4 on  t1.customer_guid = t4.customer_guid                                 
342  left join tvmedia t3 on  t1.cust_media_id=t3.idkey --and t1.cust_partner_guid=t3.partner_guid   
343  where t1.web_flag=1 and  cust_media_id in (‘67B3CB84-81F4-87AA-01EB-857EA1474223‘,‘CFC5A634-2375-1552-59B4-9A1263DCFCA4‘,‘673473E7-8079-68ED-3CB6-9A2256E34A67‘,‘E6192562-FCF8-415C-0AC4-9A22A6200706‘,‘542CF17F-374E-627D-389B-9A22F09BC4D3‘,‘A270E30B-368B-F962-F44F-AA0D76E8865E‘)                                                                    

2.通过SQL语句进行查询当天结算历史的记录

  1 select                                                                                                                                                                         
  2        convert(int,sum(case when indentdate >= ‘2015-11-28 00:00:00‘ and                                                                                            
  3                   indentdate <= ‘2015-11-28 23:59:59‘ and                                                                                                             
  4                   indenttype = 0 and indent_step = ‘00‘ then                                                                                                                                                      
  5               t1.totalpay else 0 end)) pay1 ,                                                                                                                                         
  6        convert(int,sum(case when t1.indentdate>= ‘2015-11-28 00:00:00‘ and                                                                                          
  7                   t1.indentdate <= ‘2015-11-28 23:59:59‘ and t2.modifieddate >= ‘2015-11-28 00:00:00‘ and               
  8                   t2.modifieddate <= ‘2015-11-28 23:59:59‘ and workorderstatus = ‘FAILED‘ and                                                                         
  9                   curstep_id = ‘2A9B4B‘ and indenttype = 0 and                                                                                                                                                    
 10                   indent_step = ‘00‘ then                                                                                                                                                                         
 11               t1.totalpay  else 0 end)                                                                                                                                                                               
 12            + sum(case when t1.indentdate>= ‘2015-11-28 00:00:00‘ and                                                                                                
 13                   indentdate <= ‘2015-11-28 23:59:59‘ and workorderstatus = ‘FAILED‘ and                                                                              
 14                   curstep_id = ‘5CB9E0‘ and financedate >= ‘2015-11-28 00:00:00‘ and                                                                                
 15                   financedate <= ‘2015-11-28 23:59:59‘ and indenttype = 0 and                                                                                         
 16                   indent_step = ‘00‘ then                                                                                                                                                                         
 17               t1.totalpay  else 0 end)) +                                                                                                                                               
 18        convert(int,sum(case                                                                                                                                                                                       
 19              when t1.indentdate<= ‘2015-11-28 00:00:00‘ and t2.modifieddate >= ‘2015-11-28 00:00:00‘ and              
 20                   t2.modifieddate <= ‘2015-11-28 23:59:59‘ and workorderstatus = ‘FAILED‘ and                                                                         
 21                   curstep_id = ‘2A9B4B‘ and indenttype = 0 and                                                                                                                                                    
 22                   indent_step = ‘00‘ then                                                                                                                                                                         
 23               t1.totalpay                                                                                                                                                                                            
 24              else                                                                                                                                                                                                 
 25               0                                                                                                                                                                                                   
 26            end)+                                                                                                                                                                                                  
 27        sum(case                                                                                                                                                                                                   
 28              when                                                                                                                                                                                                 
 29                   indentdate <= ‘2015-11-28 00:00:00‘ and workorderstatus = ‘FAILED‘ and                                                                            
 30                   curstep_id = ‘5CB9E0‘ and financedate >= ‘2015-11-28 00:00:00‘ and                                                                                
 31                   financedate <= ‘2015-11-28 23:59:59‘ and indenttype = 0 and                                                                                         
 32                   indent_step = ‘00‘ then                                                                                                                                                                         
 33               t1.totalpay                                                                                                                                                                                            
 34              else                                                                                                                                                                                                 
 35               0                                                                                                                                                                                                   
 36            end)-sum(case                                                                                                                                                                                          
 37              when                                                                                                                                                                                                 
 38                   workorderstatus = ‘CLOSE‘ and financedate >= ‘2015-11-28 00:00:00‘ and                                                                            
 39                   financedate <= ‘2015-11-28 23:59:59‘ and indenttype = 1  then                                                                                       
 40               t1.totalpay                                                                                                                                                                                            
 41              else                                                                                                                                                                                                 
 42               0                                                                                                                                                                                                   
 43            end)) pay2, --历史失效金额                                                                                                                                                                        
 44        convert(int,sum(case                                                                                                                                                                                       
 45              when indentdate >= ‘2015-11-28 00:00:00‘ and                                                                                                           
 46                   indentdate <= ‘2015-11-28 23:59:59‘ and indenttype = 0 and                                                                                          
 47                   indent_step = ‘00‘ then                                                                                                                                                                         
 48               cust_partner_value                                                                                                                                                                                  
 49              else                                                                                                                                                                                                 
 50               0                                                                                                                                                                                                   
 51            end) -                                                                                                                                                                                                 
 52         sum(case                                                                                                                                                                                                  
 53              when t2.modifieddate>= ‘2015-11-28 00:00:00‘ and                                                                                                       
 54                   t2.modifieddate <= ‘2015-11-28 23:59:59‘ and workorderstatus = ‘FAILED‘ and                                                                         
 55                   curstep_id = ‘2A9B4B‘ and indenttype = 0 and                                                                                                                                                    
 56                   indent_step = ‘00‘ then                                                                                                                                                                         
 57                cust_partner_value                                                                                                                                                                                 
 58              else                                                                                                                                                                                                 
 59               0                                                                                                                                                                                                   
 60            end)                                                                                                                                                                                                   
 61            -sum(case                                                                                                                                                                                              
 62              when                                                                                                                                                                                                 
 63                   indentdate <= ‘2015-11-28 23:59:59‘ and workorderstatus = ‘FAILED‘ and                                                                              
 64                   curstep_id = ‘5CB9E0‘ and financedate >= ‘2015-11-28 00:00:00‘ and                                                                                
 65                   financedate <= ‘2015-11-28 23:59:59‘ and indenttype = 0 and                                                                                         
 66                   indent_step = ‘00‘ then                                                                                                                                                                         
 67               cust_partner_value                                                                                                                                                                                  
 68              else                                                                                                                                                                                                 
 69               0                                                                                                                                                                                                   
 70            end)-sum(case                                                                                                                                                                                          
 71              when                                                                                                                                                                                                 
 72                   workorderstatus = ‘CLOSE‘ and financedate >= ‘2015-11-28 00:00:00‘ and                                                                            
 73                   financedate <= ‘2015-11-28 23:59:59‘ and indenttype = 1  then                                                                                       
 74               cust_partner_value                                                                                                                                                                                  
 75              else                                                                                                                                                                                                 
 76               0                                                                                                                                                                                                   
 77            end)-(sum(case                                                                                                                                                                                         
 78              when indentdate >= ‘2015-11-28 00:00:00‘ and                                                                                                           
 79                   indentdate <= ‘2015-11-28 23:59:59‘ and indenttype = 0 and                                                                                          
 80                   indent_step = ‘00‘ and status=2 then                                                                                                                                                            
 81               cust_partner_value                                                                                                                                                                                  
 82              else                                                                                                                                                                                                 
 83               0                                                                                                                                                                                                   
 84            end) -                                                                                                                                                                                                 
 85         sum(case                                                                                                                                                                                                  
 86              when t2.modifieddate>= ‘2015-11-28 00:00:00‘ and                                                                                                       
 87                   t2.modifieddate <= ‘2015-11-28 23:59:59‘ and workorderstatus = ‘FAILED‘ and                                                                         
 88                   curstep_id = ‘2A9B4B‘ and status=2 and indenttype = 0 and                                                                                                                                       
 89                   indent_step = ‘00‘ then                                                                                                                                                                         
 90                cust_partner_value                                                                                                                                                                                 
 91              else                                                                                                                                                                                                 
 92               0                                                                                                                                                                                                   
 93            end)                                                                                                                                                                                                   
 94            -sum(case                                                                                                                                                                                              
 95              when                                                                                                                                                                                                 
 96                   indentdate <= ‘2015-11-28 23:59:59‘ and workorderstatus = ‘FAILED‘ and                                                                              
 97                   curstep_id = ‘5CB9E0‘ and financedate >= ‘2015-11-28 00:00:00‘ and                                                                                
 98                   financedate <= ‘2015-11-28 23:59:59‘ and status=2 and indenttype = 0 and                                                                            
 99                   indent_step = ‘00‘ then                                                                                                                                                                         
100               cust_partner_value                                                                                                                                                                                  
101              else                                                                                                                                                                                                 
102               0                                                                                                                                                                                                   
103            end)-sum(case                                                                                                                                                                                          
104              when                                                                                                                                                                                                 
105                   workorderstatus = ‘CLOSE‘ and financedate >= ‘2015-11-28 00:00:00‘ and                                                                            
106                   financedate <= ‘2015-11-28 23:59:59‘ and status=2 and indenttype = 1  then                                                                          
107               cust_partner_value                                                                                                                                                                                  
108              else                                                                                                                                                                                                 
109               0                                                                                                                                                                                                   
110            end))) pay4                                                                                                                                                                                       
111  from tabcindent t1 left join workorder2 t2 on t1.relation_id=t2.workorder_guid                
112  --left join customer t4 on  t1.customer_guid = t4.customer_guid                                 
113  left join tvmedia t3 on  t1.cust_media_id=t3.idkey --and t1.cust_partner_guid=t3.partner_guid   
114  where t1.web_flag=1 and  cust_media_id in (‘CFC5A634-2375-1552-59B4-9A1263DCFCA4‘)                                                                    

 

3.对于这样的复杂的SQL进行大数据量的查询如何优化SQL确实是一个问题,但是我通常是这样做的,虽然没有办法解决根本问题但是,效果还是有的。

4.通常我会建立需要的索引,来增加查询的速度。尽量的避免内嵌的查询因为这真的是影响效率。

5.那么当这些工作都做完后优化的作用不大了,那么我通常会在数据库上面进行动手脚,建立数据库集群进行数据库的读写的分离,然后进行建立数据库快照进行数据库的数据的映射。

6.如果此时的方法不行那么创建分区,以及建立临时表倒是一个不错的选择。

7.尽量的避免表与表之间过多的交差,此时宁愿数据库中的表格的字段冗余一些,也不要太多的交差,JOIN ,LEFT JOIN 真的影响查询的效率。

8.通过上面描述的方法,优化后数据库的表的结构以及数据库几百行的SQL语句查询的效率确实变快了。只不过折磨多的SQL语句只能通过

创建存储过程了。然后在应用ADO.NET 参数化SQL 进行访问了。

9.如果您有好的方法可以随时的交流,毕竟我对于数据库方面的经验还不足。

以上内容,全部都是原创,如需转载,请标明!谢谢!

 

如何对于几百行SQL语句进行优化?