hybridview
commented
9 years ago I forgot to mention the CONS:
- DB size increase by ~13% to store combo index and stats analysis.
- Might be minor performance hit to stats inserts, but since we already index 3 other fields, I wouldn't expect this to be much. Will test.
This will benefit those who want to keep a large historical stats DB.
I am keeping a very large history in my stats DB for analysis. I am currently at 40MB DB size and have noticed delays when loading the energy monitor widget. I ran some SQLite query tests and saw that the queries being run for the widget are taking 5+ seconds to complete on my low-power server. I did some research regarding the SQLite DB engine and discovered that many query optimizations that are automatic in SQLServer are not so in SQLite.
There are indexes in the HG stats DB for the important fields (3 total), but SQLite only uses ONE index by default when running a query. Interesting... So, I created a NEW index that encompasses all of the fields that are mostly commonly used for filtering in HG queries (recommended in SQLite help doc). This 4th index is a combined index of fields Parameter, Domain, Address, and StartTime. Now, in order for SQLite to be able to use this new index instead of a single original one, we must run ANALYZE. This step is important as you will see no speed benefit with it!
Here is the QUERY to create the new index. It's just 2 commands:
` CREATE INDEX IDX_Domain_Address_Parameter_TimeStart ON ValuesHist (TimeStart ASC,Domain ASC,Address ASC,Parameter ASC);
ANALYZE `
I ran the test, and got up to a 21x speed boost on some queries! Here are my tests (based on real queries sent via HG on my server) and results. Times are in milliseconds (run against my 40+MB database), and lower is better:
`
-- Get Params List
-- [Original DB] 410ms -- [Re-Indexed DB] 400ms [MINIMAL CHANGE] select distinct Parameter from ValuesHist;
-- GetDateRange
-- [Original DB] 1900 -- [Re-Indexed DB] 1900 [MINIMAL CHANGE] select min(TimeStart),max(TimeEnd) from ValuesHist;
-- GetTotalCounter
-- [Original DB] 2150 -- [Re-Indexed DB] 2160 [MINIMAL CHANGE] select Sum(AverageValue( ((julianday(TimeEnd) - 2440587.5) * 86400.0)-((julianday(TimeStart) - 2440587.5) \ 86400.0) )/60) as CounterValue from ValuesHist where Parameter = 'Meter.Watts';
-- GetHourlyCounter
-- [Original DB] 2730 -- [Re-Indexed DB] 2130 [1.3x FASTER] select TimeStart,TimeEnd,Domain,Address,Sum(AverageValue( ((julianday(TimeEnd) - 2440587.5) * 86400.0) -((julianday(TimeStart) - 2440587.5) \ 86400.0) )/60) as CounterValue from ValuesHist where Domain='HomeAutomation.ZWave' AND Address=5 and Parameter = 'Meter.Watts' AND (TimeStart >= '2015-03-05 00:29:29.086095' AND TimeStart <= '2015-03-19 00:29:29.086095') group by Domain, Address, strftime('%H', TimeStart) order by TimeStart desc;
-- GetHourlyStatsToday
-- [Original DB] 1900
-- [Re-Indexed DB] 130 [14.6x FASTER] select TimeStart,TimeEnd,Domain,Address,AVG(AverageValue) as Value from ValuesHist where Domain='HomeAutomation.ZWave' AND Address=5 and Parameter = 'Meter.Watts' and TimeStart >= '2015-03-19 00:00:29.086095' group by Domain, Address, strftime('%H', TimeStart) order by TimeStart asc;
-- GetTodayDetail
-- [Original DB] 1900 -- [Re-Indexed DB] 90 [21.1x FASTER] select TimeStart,TimeEnd,Domain,Address,AVG(AverageValue) as Value from ValuesHist where Domain='HomeAutomation.ZWave' AND Address=5 and Parameter = 'Meter.Watts' AND TimeStart >= '2015-03-19 00:00:29.086095' group by TimeStart order by TimeStart asc;
-- GetHourlyStats
-- [Original DB] 2550 -- [Re-Indexed DB] 1950 [1.3x FASTER] select TimeStart,TimeEnd,Domain,Address,AVG(AverageValue) as Value from ValuesHist where Domain='HomeAutomation.ZWave' AND Address=5 AND Parameter = 'Meter.Watts' AND (TimeStart >= '2015-03-05 00:29:29.086095' AND TimeStart <= '2015-03-19 00:29:29.086095') group by Domain, Address, strftime('%H', TimeStart) order by TimeStart asc;
`