# Query Performance **URL:** \ **Category:** Ignition\ **Created:** [October 20, 2016, 2:56pm UTC](https://forum.inductiveautomation.com/t/query-performance/12262 "2016-10-20T14:56:13Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![Mr.K001](https://avatars.discourse-cdn.com/v4/letter/m/ea5d25/32.png) [@Mr.K001](https://forum.inductiveautomation.com/u/Mr.K001)\ **Post date:** [October 20, 2016, 2:56pm UTC](https://forum.inductiveautomation.com/t/query-performance/12262/1 "2016-10-20T14:56:13Z")
Hi guys, I’d like to ask for your opinion. Most of my windows that have a table in them lets users have different options to query data, for example I have a table set up that grabs system resources(RAM%,CPU% etc.) every one second so I can be able to go back and look at it if needed. I let users be able to look at the data every 30 second, every hour and every midnight. When the window opens the time interval is always set to the previous day at midnight to current day at midnight. My data property of the table has a query with 2 if and one else statements that basically check for ```auto if midnight.selected = 1 Run a query if hour.selected = 1 Run a query else "(30 sec)" Run a query ``` Is that approach good enough or should I maybe have 3 dataset custom properties that each query the data for one of those 3 instances and depending on my selection the corresponding dataset gets displayed in the table? Just want to see if you all have better ideas to maximize my query timing. I mean the timing is not horrible now, but there is definitely a few seconds lag for queries to appear. anyways thanks in advance for your thoughts. ![:thumb_right:](https://us1.discourse-cdn.com/inductiveautomation/original/1X/2026e28c0c425034352c1b75807edf956adc75f4.gif ":thumb\_right:") ---
**Author:** ![MMaynard](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mmaynard/32/3366_2.png) [@MMaynard](https://forum.inductiveautomation.com/u/MMaynard)\ **Post date:** [October 20, 2016, 4:39pm UTC](https://forum.inductiveautomation.com/t/query-performance/12262/2 "2016-10-20T16:39:53Z")
Can you post the queries? Also, 1 days worth of data at 1 second intervals is not a ton of data, so if it is slow it might be table design as well. Do you have indexes created on the tables? ---
**Author:** ![Mr.K001](https://avatars.discourse-cdn.com/v4/letter/m/ea5d25/32.png) [@Mr.K001](https://forum.inductiveautomation.com/u/Mr.K001)\ **Post date:** [October 20, 2016, 5:45pm UTC](https://forum.inductiveautomation.com/t/query-performance/12262/3 "2016-10-20T17:45:32Z")
Oh my bad, probably should have been more specific. yes day worth of data is actually almost instantly, the delay happens when I try to look at span of say a month. this is is the query I used to glad the data for every hour, the other two are the same and only difference is in the WHERE clause. ```auto if ('{Root Container.Hour.selected}' = '1') SELECT t_stamp, TimeDifference, Memory_Usage, CPU_Usage AS 'CPU%', Memory_Utilization AS 'MEM%', Disk_Utilization AS 'DISK%', Time_Gateway AS 'Gateway Time', Time_IgnitionPLC AS 'PLC Time', TIME_DB AS 'DB Time' FROM table WHERE t_stamp BETWEEN '{Root Container.Day.date}' AND '{Root Container.Day 1.date}' AND DATEPART(mi,t_stamp) = 0 AND DATEPART(ss,t_stamp) = 0 ORDER BY t_stamp ASC if ('{Root Container.Midnight.selected}' = '1') Query else Query ``` ---
**Author:** ![MMaynard](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mmaynard/32/3366_2.png) [@MMaynard](https://forum.inductiveautomation.com/u/MMaynard)\ **Post date:** [October 20, 2016, 6:45pm UTC](https://forum.inductiveautomation.com/t/query-performance/12262/4 "2016-10-20T18:45:58Z")
Make sure you have indexes on the t\_stamp column and change the WHERE from a between to a \>= WHERE t\_stamp \>= ‘{Root Container.Day.date}’ AND t\_stamp \< ‘{Root Container.Day 1.date}’ AND DATEPART(mi,t\_stamp) = 0 AND DATEPART(ss,t\_stamp) = 0 The DATEPART functions will slow it down too as it is processing every record twice there. ---
**Author:** ![Mr.K001](https://avatars.discourse-cdn.com/v4/letter/m/ea5d25/32.png) [@Mr.K001](https://forum.inductiveautomation.com/u/Mr.K001)\ **Post date:** [October 20, 2016, 8:02pm UTC](https://forum.inductiveautomation.com/t/query-performance/12262/5 "2016-10-20T20:02:28Z")
As far as indexing I will do that down the road on the tables I know will be used a lot. this table is mainly for my personal use and I was just curious to know how I could speed it up faster that way I can use that knowledge down later on. As far as the DATEPART functions, I see your point, do you recommend another way to specify 0 min and 0 sec in a WHERE clause? or is this the best option? ---
**Author:** ![KathyApplebaum](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kathyapplebaum/32/7261_2.png) [@KathyApplebaum](https://forum.inductiveautomation.com/u/KathyApplebaum)\ **Post date:** [October 20, 2016, 9:14pm UTC](https://forum.inductiveautomation.com/t/query-performance/12262/6 "2016-10-20T21:14:36Z")
Not sure what DB you are using – I’ll assume MySQL, but there are equivalent functions in other RDMS’s If you want to know how to speed up a query, a great way is the [EXPLAIN](http://dev.mysql.com/doc/refman/5.7/en/explain.html) function in your DB’s front end (like MySQL Workbench). You would type in EXPLAIN , and the results will estimate about how long each part of the query will take. You especially want to look for things like “full table scan”, which means it’s looking at every single record in a table. If the table will never have more than a few records, that’s fine, but in a table that can grow you’ll take a big performance hit. That’s where indexing (such as on the t\_stamp column) will speed things up. Instead of loading in all the records, the DB first loads the index to see which records it will really need. If you’re only retrieving a fraction of your entire table, this is usually a LOT faster than a full table scan. ---
**Author:** ![Mr.K001](https://avatars.discourse-cdn.com/v4/letter/m/ea5d25/32.png) [@Mr.K001](https://forum.inductiveautomation.com/u/Mr.K001)\ **Post date:** [October 20, 2016, 10:39pm UTC](https://forum.inductiveautomation.com/t/query-performance/12262/7 "2016-10-20T22:39:06Z")
I’m using SQL . I’ll definitely look into that, thank you