# SQL Server help for speeding up query

**URL:** <https://forum.inductiveautomation.com/t/sql-server-help-for-speeding-up-query/4349>\
**Category:** Archived Topics\
**Created:** [April 25, 2012, 5:45pm UTC](https://forum.inductiveautomation.com/t/sql-server-help-for-speeding-up-query/4349 "2012-04-25T17:45:22Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![diat150](https://avatars.discourse-cdn.com/v4/letter/d/a587f6/32.png) [@diat150](https://forum.inductiveautomation.com/u/diat150)\
**Post date:** [April 25, 2012, 5:45pm UTC](https://forum.inductiveautomation.com/t/sql-server-help-for-speeding-up-query/4349/1 "2012-04-25T17:45:22Z")

</div>

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

```auto
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
```

the 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?

---

<div class="post-metadata">

**Author:** ![Travis.Cox](https://avatars.discourse-cdn.com/v4/letter/t/b9bd4f/32.png) [@Travis.Cox](https://forum.inductiveautomation.com/u/Travis.Cox)\
**Post date:** [May 15, 2012, 5:17pm UTC](https://forum.inductiveautomation.com/t/sql-server-help-for-speeding-up-query/4349/2 "2012-05-15T17:17:22Z")

</div>

That is a pretty gnarly query. I would suggest using a MySQL query analyzer like this one:

[tools.percona.com/](https://tools.percona.com/)