I have the following query in sql server 2008 r2
[code]SELECT
dh.deviceid, dh.archived, lastpoll, esttemperature, esttemperature_alarm, staticpressure, staticpressure_alarm, differentialpressure, differentialpressure_alarm, backflowtime,
energy, tubingpressure, tubingpressure_alarm, casingpressure, casingpressure_alarm, gasspotflowrate, gasspotflowrate_alarm, battery, battery_alarm,
orificesize, CASE WHEN DATEADD(hour, -6, getDate()) > lastpoll THEN 1 ELSE 0 END as lastpolltime_alm, contracthr,
CASE WHEN flags > 0 THEN 1 ELSE 0 END, week_mcf_avg, month_mcf_avg, (gasspotflowrate - week_mcf_avg) as week_mcf_avg_diff
FROM devicehistory dh
LEFT JOIN (SELECT deviceid, archived, AVG(CASE WHEN recorddate > DATEADD(day, -7, getDate()) THEN d2.estgasvolmcf ELSE NULL END) AS week_mcf_avg, AVG(d2.estgasvolmcf) AS month_mcf_avg FROM devicehistory d2 WHERE recorddate > DATEADD(day, -30, getDate()) GROUP BY deviceid, archived) d30 ON d30.deviceid = dh.deviceid AND d30.archived = dh.archived
LEFT JOIN (SELECT deviceid, COUNT(id) AS flags FROM memos WHERE DATEADD(day, 15, time) > getDate() GROUP BY deviceid) n ON n.deviceid = dh.deviceid
WHERE dh.deviceid IN (1485, 1486, 1487, 1488) AND CONVERT(DATE, recorddate) = ‘2012-04-25’
[/code]
this part of the query is really slowing down the execution time
LEFT JOIN (SELECT deviceid, archived, AVG(CASE WHEN recorddate > DATEADD(day, -7, getDate()) THEN d2.estgasvolmcf ELSE NULL END) AS week_mcf_avg, AVG(d2.estgasvolmcf) AS month_mcf_avg FROM devicehistory d2 WHERE recorddate > DATEADD(day, -30, getDate()) GROUP BY deviceid, archived) d30 ON d30.deviceid = dh.deviceid AND d30.archived = dh.archivedthe where clause can have thousands of deviceid’s in it. If I comment out the join that calculates the week_mcf_avg, month_mcf_avg, ,week_mcf_avg_diff columns, the query executes in 10-15 seconds. with it in their it takes a minute or 2. Is there something I can do differently that would speed it up?