On 2016/08/11 10:59 AM, Cecil Westerhof wrote:
I have the folowing SQL code:
//...//
With this I can get the idle time of my computer easily for different
periods of time. There is only one small problem: it takes quit long to
execute, between 8 and 9 seconds. It is not a huge problem: how often do I
want to get this information? But I was wondering if I did something wrong
and if it could be optimised.
The table has just under 400.000 records.
The next will sort it in principle, I can't test it so you might
encounter a spelling mistake or syntax error, but if it should run well
when fixed:
(note also remarks in comments)
WITH
STAT_DATE(likeString) AS (
-- Today
-- SELECT CURRENT_DATE
-- This month
-- SELECT substr(CURRENT_DATE, 1, 8) || "%"
-- This year
-- SELECT substr(CURRENT_DATE, 1, 5) || "%"
-- All data
SELECT "%"
),
STAT_INFO(count, onePercent, likeString) AS ( -- Please don't use reserved words like
"count" as column names...
SELECT COUNT(*)
, COUNT(*) / 100.0
, likeString
FROM vmstat
, STAT_DATE
WHERE date LIKE likeString
),
SL(Slice) AS (
SELECT CAST((MAX(idletime-1,0) / 10) AS INT) * 10
FROM vmstat, STAT_DATE
WHERE date LIKE likeString
),
STAT_SLICES(Slice, Count, Percentage) AS (
SELECT (Slice+1)||' - '||(Slice+10), COUNT(*), ROUND(COUNT(*) /
onepercent,2)
FROM SL, STAT_INFO
GROUP BY Slice
)
SELECT Slice
, Percentage
FROM STAT_SLICES
WHERE Percentage IS NOT NULL -- This line is superfluous, you can remove it
(unless you meant zero - NULL is not Zero)
ORDER BY Percentage DESC
One other annoyance. I do this is the SQLite database browser. Every time I
execute this, my database is locked and I have to revert changes. What is
happening here.
By the way, this also happens when I execute:
pragma table_info(vmstat)
I think it executes it in a transaction - so won't unlock until the
transaction is either committed or rolled back.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
