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