简化查询以在更短的时间内执行 [英] simplify the query to execute in less time

问题描述

你好朋友......



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

这篇关于简化查询以在更短的时间内执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！