简化查询以在更短的时间内执行

本文介绍了简化查询以在更短的时间内执行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

你好朋友...... i有以下查询

hello friends... i have following query

declare @month int declare @year int set @month=8 set @year =2014 select * from ( select CONVERT(varchar,s1.edatetime,103) as [date], AVG(s1.alumina) as alumina, AVG(s1.caustic) as caustic, AVG(s1.ratio) as ratio, AVG(s2.alumina) as fsfalumina, AVG(s2.caustic) as fsfcaustic, AVG(s2.ratio) as fsfratio, AVG(l1.nt_6o) as nt_6o, AVG(l1.nt_6u) as nt_6u, AVG(l2.density) as nt_6_density, AVG(l2.solid) as nt_6_solid, AVG(d1.sodagpldms) as sodagpldms1, AVG(d1.soliddms) as soliddms1, AVG(d2.mudmgpl) as mudmgpldms2, AVG(d2.sodagplpf) as sodagplpfdms2, AVG(d2.solidpf) as solidpfdms2, AVG(e1.gpl_soda) as gplsodae1, avg(e1.ph) as phe1, AVG(s3.nt_2) as nt_2s3, AVG(p1.pglmud) as pglmudp1, AVG(p2.ofmgpl) as ofmgplp2, AVG(p3.density) as densityp3, AVG(p3.solid) as solidp3, AVG(h1.hrddensity) as hrddensityh1, AVG(h1.hrdsolid) as hrdsolidh1, AVG(h1.ufdensity) as ufdensityh1, AVG(h1.ufsolid) as ufsolidh1, AVG(p4.mud) as mudp4, AVG(tu1.density) as ntu1density, AVG(tu1.soda) as ntu1so da, AVG(tu1.solid) as ntu1solid, AVG(tu2.density) as ntu2density, AVG(tu2.soda) as ntu2soda, AVG(tu2.solid) as ntu2solid, AVG(tu3.density) as ntu3density, AVG(tu3.soda) as ntu3soda, AVG(tu3.solid) as ntu3solid, AVG(tu4.density) as ntu4density, AVG(tu4.soda) as ntu4soda, AVG(tu4.solid) as ntu4solid, AVG(tu5.density) as ntu5density, AVG(tu5.soda) as ntu5soda, AVG(tu5.solid) as ntu5solid, AVG(tu6.density) as ntu6density, AVG(tu6.soda) as ntu6soda, AVG(tu6.solid) as ntu6solid, AVG(to1.soda) as nto1soda, AVG(to2.soda) as nto2soda, AVG(to3.soda) as nto3soda, AVG(to4.soda) as nto4soda, AVG(to5.soda) as nto5soda, AVG(to6.soda) as nto6soda from caustic_batch_tank s1 full join caustic_batch_tank_fsf s2 on CONVERT(varchar,s1.edatetime,103) = CONVERT(varchar,s2.edatetime,103) full join last_wash_gpl_soda l1 on CONVERT(varchar,s1.edatetime,103) = CONVERT(varchar,l1.edatetime,103) full join last_wash_nt_6 l2 on CONVERT(varchar,s1.edatetime,103) = CONVERT(varchar,l2.edatetime,103) full join dms1 d1 on CONVERT(varchar,s1.edatetime,103) = CONVERT(varchar,d1.edatetime,103) full join dms2 d2 on CONVERT(varchar,s1.edatetime,103) = CONVERT(varchar,d2.edatetime,103) full join evaporator_cooling_tower e1 on CONVERT(varchar,s1.edatetime,103) = CONVERT(varchar,e1.edatetime,103) full join suspended_mud s3 on CONVERT(varchar,s1.edatetime,103) = CONVERT(varchar,s3.edatetime,103) full join pgl_tank_mgpl_mud p1 on CONVERT(varchar,s1.edatetime,103) = CONVERT(varchar,p1.edatetime,103) full join pd_tank p2 on CONVERT(varchar,s1.edatetime,103) = CONVERT(varchar,p2.edatetime,103) full join pd_tank2 p3 on CONVERT(varchar,s1.edatetime,103) = CONVERT(varchar,p3.edatetime,103) full join hrd_feed h1 on CONVERT(varchar,s1.edatetime,103) = CONVERT(varchar,h1.edatetime,103) full join pfmud p4 on CONVERT(varchar,s1.edatetime,103) = CONVERT(varchar,p4.edatetime,103) full join thickeners_u tu1 on CONVERT(varchar,s1.edatetime,103) = CONVERT(varchar,tu1.edatetime,103) full join thickeners_u tu2 on CONVERT(varchar,s1.edatetime,103) = CONVERT(varchar,tu2.edatetime,103) full join thickeners_u tu3 on CONVERT(varchar,s1.edatetime,103) = CONVERT(varchar,tu3.edatetime,103) full join thickeners_u tu4 on CONVERT(varchar,s1.edatetime,103) = CONVERT(varchar,tu4.edatetime,103) full join thickeners_u tu5 on CONVERT(varchar,s1.edatetime,103) = CONVERT(varchar,tu5.edatetime,103) full join thickeners_u tu6 on CONVERT(varchar,s1.edatetime,103) = CONVERT(varchar,tu6.edatetime,103) full join thickeners_o to1 on CONVERT(varchar,s1.edatetime,103) = CONVERT(varchar,to1.edatetime,103) full join thickeners_o to2 on CONVERT(varchar,s1.edatetime,103) = CONVERT(varchar,to2.edatetime,103) full join thickeners_o to3 on CONVERT(varchar,s1.edatetime,103) = CONVERT(varchar,to3.edatetime,103) full join thickeners_o to4 on CONVERT(varchar,s1.edatetime,103) = CONVERT(varchar,to4.edatetime,103) full join thickeners_o to5 on CONVERT(varchar,s1.edatetime,103) = CONVERT(varchar,to5.edatetime,103) full join thickeners_o to6 on CONVERT(varchar,s1.edatetime,103) = CONVERT(varchar,to6.edatetime,103) where s1.edatetime>='2014-08-01' or s2.edatetime >= '2014-08-01' or l1.edatetime>='2014-08-01' or l2.edatetime>='2014-08-01' or d1.edatetime>='2014-08-01' or d2.edatetime>='2014-08-01' or e1.edatetime>='2014-08-01' or s3.edatetime>='2014-08-01' or p1.edatetime>='2014-08-01' or (p2.edatetime>='2014-08-01' and p2.valstatus='Not Mud') or p3.edatetime>='2014-08-01' or h1.edatetime>='2014-08-01' or p4.edatetime>='2014-08-01' or (tu1.edatetime>='2014-08-01' and tu1.sample='NT - 1') or (tu2.edatetime>='2014-08-01' and tu2.sample='NT - 2') or (tu3.edatetime>='2014-08-01' and tu3.sample='NT - 3') or (tu4.edatetime>='2014-08-01' and tu4.sample='NT - 4') or (tu5.edatetime>='2014-08-01' and tu5.sample='NT - 5') or (tu6.edatetime>='2014-08-01' and tu6.sample='NT - 6') or (to1.edatetime>='2014-08-01' and to1.sample='NT - 1') or (to2.edatetime>='2014-08-01' and to2.sample='NT - 2') or (to3.edatetime>='2014-08-01' and to3.sample='NT - 3') or (to4.edatetime>='2014-08-01' and to4.sample='NT - 4') or (to5.edatetime>='2014-08-01' and to5.sample='NT - 5') or (to6.edatetime>='2014-08-01' and to6.sample='NT - 6') group by CONVERT(varchar,s1.edatetime,103)) as query order by query.date asc

它显示一个月内的平均日期，但它需要8到9个小时才能执行。 i想要简化这个查询以便在更短的时间内执行。 plz帮助我

it shows averages date wise within a month but it takes 8-9 hours to execute. i want to simplify this query to execute in less time. plz help me

推荐答案

尝试使用查询分析器。这个工具内置于sql management studio中，可以帮助解决查询问题。 祝你好运！ Try using the query analyser. This tool is built into sql management studio and really can help in breaking down problems with queries. Good luck!

您好， 检查这个... Hi, Check this... declare @month int declare @year int set @month=8 set @year =2014 CREATE TABLE #TMP_Avg_Stats ( dt as datetime, alumina as numeric(10,2), caustic as numeric(10,2), ratio as numeric(10,2), fsfalumina as numeric(10,2), fsfcaustic as numeric(10,2), fsfratio as numeric(10,2), nt_6o as numeric(10,2), nt_6u as numeric(10,2), nt_6_density as numeric(10,2), nt_6_solid as numeric(10,2), sodagpldms1 as numeric(10,2), soliddms1 as numeric(10,2), mudmgpldms2 as numeric(10,2), sodagplpfdms2 as numeric(10,2), solidpfdms2 as numeric(10,2), gplsodae1 as numeric(10,2), phe1 as numeric(10,2), nt_2s3 as numeric(10,2), pglmudp1 as numeric(10,2), ofmgplp2 as numeric(10,2), densityp3 as numeric(10,2), solidp3 as numeric(10,2), hrddensityh1 as numeric(10,2), hrdsolidh1 as numeric(10,2), ufdensityh1 as numeric(10,2), ufsolidh1 as numeric(10,2), mudp4 as numeric(10,2), ntu1density as numeric(10,2), ntu1soda as numeric(10,2), ntu1solid as numeric(10,2), ntu2density as numeric(10,2), ntu2soda as numeric(10,2), ntu2solid as numeric(10,2), ntu3density as numeric(10,2), ntu3soda as numeric(10,2), ntu3solid as numeric(10,2), ntu4density as numeric(10,2), ntu4soda as numeric(10,2), ntu4solid as numeric(10,2), ntu5density as numeric(10,2), ntu5soda as numeric(10,2), ntu5solid as numeric(10,2), ntu6density as numeric(10,2), ntu6soda as numeric(10,2), ntu6solid as numeric(10,2), nto1soda as numeric(10,2), nto2soda as numeric(10,2), nto3soda as numeric(10,2), nto4soda as numeric(10,2), nto5soda as numeric(10,2), nto6soda as numeric(10,2) ) INSERT INTO #TMP_Avg_Stats SELECT CONVERT(varchar,s1.edatetime,103) as [date], AVG(s1.alumina) as alumina,AVG(s1.caustic),AVG(s1.ratio) as ratio, from caustic_batch_tank s1 where s1.edatetime>='2014-08-01' UPDATE #TMP_Avg_Stats SET fsfalumina=AVG(s2.alumina), fsfcaustic=AVG(s2.caustic),fsfratio=AVG(s2.ratio) FROM caustic_batch_tank_fsf s2 WHERE s2.edatetime >= '2014-08-01' UPDATE #TMP_Avg_Stats SET nt_6o=AVG(l1.nt_6o) , nt_6u= AVG(l1.nt_6u) FROM last_wash_gpl_soda l1 WHERE l1.edatetime>='2014-08-01' UPDATE #TMP_Avg_Stats SET nt_6_density=AVG(l2.density),nt_6_solid=AVG(l2.solid) FROM last_wash_nt_6 l2 WHERE l2.edatetime>='2014-08-01' UPDATE #TMP_Avg_Stats SET sodagpldms1 = AVG(d1.sodagpldms), soliddms1=AVG(d1.soliddms) dms1 d1 WHERE d1.edatetime>='2014-08-01' UPDATE #TMP_Avg_Stats SET mudmgpldms2 =AVG(d2.mudmgpl) , sodagplpfdms2= AVG(d2.sodagplpf) , solidpfdms2=AVG(d2.solidpf) FROM dms2 d2 WHERE d2.edatetime>='2014-08-01' UPDATE #TMP_Avg_Stats SET gplsodae1=AVG(e1.gpl_soda), phe1=avg(e1.ph) FROM evaporator_cooling_tower e1 WHERE e1.edatetime>='2014-08-01' UPDATE #TMP_Avg_Stats SET nt_2s3 =AVG(s3.nt_2) FROM suspended_mud s3 WHERE s3.edatetime>='2014-08-01' UPDATE #TMP_Avg_Stats SET pglmudp1=AVG(p1.pglmud) FROM pgl_tank_mgpl_mud p1 WHERE p1.edatetime>='2014-08-01' UPDATE #TMP_Avg_Stats SET ofmgplp2=AVG(p2.ofmgpl) FROM pd_tank p2 WHERE (p2.edatetime>='2014-08-01' and p2.valstatus='Not Mud') UPDATE #TMP_Avg_Stats SET densityp3=AVG(p3.density), solidp3=AVG(p3.solid) FROM pd_tank2 p3 WHERE p3.edatetime>='2014-08-01' UPDATE #TMP_Avg_Stats SET hrddensityh1=AVG(h1.hrddensity),hrdsolidh1=AVG(h1.hrdsolid), ufdensityh1=AVG(h1.ufdensity),ufsolidh1 = AVG(h1.ufsolid) FROM hrd_feed h1 WHERE h1.edatetime>='2014-08-01' UPDATE #TMP_Avg_Stats SET AVG(p4.mud) as mudp4, FROM pfmud p4 WHERE p4.edatetime>='2014-08-01' UPDATE #TMP_Avg_Stats SET AVG(tu1.density) as ntu1density, AVG(tu1.soda) as ntu1so da, AVG(tu1.solid) as ntu1solid, FROM thickeners_u tu1 WHERE (tu1.edatetime>='2014-08-01' and tu1.sample='NT - 1') UPDATE #TMP_Avg_Stats SET AVG(tu2.density) as ntu2density, AVG(tu2.soda) as ntu2soda, AVG(tu2.solid) as ntu2solid, FROM thickeners_u tu2 WHERE (tu2.edatetime>='2014-08-01' and tu2.sample='NT - 2') UPDATE #TMP_Avg_Stats SET AVG(tu3.density) as ntu3density, AVG(tu3.soda) as ntu3soda, AVG(tu3.solid) as ntu3solid, FROM thickeners_u tu3 WHERE (tu3.edatetime>='2014-08-01' and tu3.sample='NT - 3') UPDATE #TMP_Avg_Stats SET AVG(tu4.density) as ntu4density, AVG(tu4.soda) as ntu4soda, AVG(tu4.solid) as ntu4solid, FROM thickeners_u tu4 WHERE (tu4.edatetime>='2014-08-01' and tu4.sample='NT - 4') UPDATE #TMP_Avg_Stats SET AVG(tu5.density) as ntu5density, AVG(tu5.soda) as ntu5soda, AVG(tu5.solid) as ntu5solid, FROM thickeners_u tu5 WHERE (tu5.edatetime>='2014-08-01' and tu5.sample='NT - 5') UPDATE #TMP_Avg_Stats SET ntu6density=AVG(tu6.density), ntu6soda=AVG(tu6.soda), ntu6solid=AVG(tu6.solid) FROM thickeners_u tu6 WHERE (tu6.edatetime>='2014-08-01' and tu6.sample='NT - 6') UPDATE #TMP_Avg_Stats SET nto1soda=AVG(to1.soda) FROM thickeners_o to1 WHERE (to1.edatetime>='2014-08-01' and to1.sample='NT - 1') UPDATE #TMP_Avg_Stats SET nto2soda=AVG(to2.soda) FROM thickeners_o to2 WHERE (to2.edatetime>='2014-08-01' and to2.sample='NT - 2') UPDATE #TMP_Avg_Stats SET nto3soda=AVG(to3.soda) FROM thickeners_o to3 WHERE (to3.edatetime>='2014-08-01' and to3.sample='NT - 3') UPDATE #TMP_Avg_Stats SET nto4soda=AVG(to4.soda) FROM thickeners_o to4 WHERE (to4.edatetime>='2014-08-01' and to4.sample='NT - 4') UPDATE #TMP_Avg_Stats SET nto5soda=AVG(to5.soda) FROM thickeners_o to5 WHERE (to5.edatetime>='2014-08-01' and to5.sample='NT - 5') UPDATE #TMP_Avg_Stats SET nto6soda=AVG(to6.soda) FROM thickeners_o to6 WHERE (to6.edatetime>='2014-08-01' and to6.sample='NT - 6') SELECT * FROM #TMP_Avg_Stats

Hope this will give you faster output. Cheers

Hope this will give you faster output. Cheers