I have the folowing SQL code:
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 (
SELECT COUNT(*)
, COUNT(*) / 100.0
, likeString
FROM vmstat
, STAT_DATE
WHERE date LIKE likeString
),
STAT_SLICES(Slice, Count, Percent) AS (
SELECT ' 0 - 10', COUNT(*), COUNT(*) / onePercent FROM vmstat,
STAT_INFO WHERE date LIKE likeString AND idletime >= 0 AND idletime <= 10
UNION
SELECT '11 - 20', COUNT(*), COUNT(*) / onePercent FROM vmstat,
STAT_INFO WHERE date LIKE likeString AND idletime >= 11 AND idletime <= 20
UNION
SELECT '21 - 30', COUNT(*), COUNT(*) / onePercent FROM vmstat,
STAT_INFO WHERE date LIKE likeString AND idletime >= 21 AND idletime <= 30
UNION
SELECT '31 - 40', COUNT(*), COUNT(*) / onePercent FROM vmstat,
STAT_INFO WHERE date LIKE likeString AND idletime >= 31 AND idletime <= 40
UNION
SELECT '41 - 50', COUNT(*), COUNT(*) / onePercent FROM vmstat,
STAT_INFO WHERE date LIKE likeString AND idletime >= 41 AND idletime <= 50
UNION
SELECT '51 - 60', COUNT(*), COUNT(*) / onePercent FROM vmstat,
STAT_INFO WHERE date LIKE likeString AND idletime >= 51 AND idletime <= 60
UNION
SELECT '61 - 70', COUNT(*), COUNT(*) / onePercent FROM vmstat,
STAT_INFO WHERE date LIKE likeString AND idletime >= 61 AND idletime <= 70
UNION
SELECT '71 - 80', COUNT(*), COUNT(*) / onePercent FROM vmstat,
STAT_INFO WHERE date LIKE likeString AND idletime >= 71 AND idletime <= 80
UNION
SELECT '81 - 90', COUNT(*), COUNT(*) / onePercent FROM vmstat,
STAT_INFO WHERE date LIKE likeString AND idletime >= 81 AND idletime <= 90
UNION
SELECT '91 - 100', COUNT(*), COUNT(*) / onePercent FROM vmstat,
STAT_INFO WHERE date LIKE likeString AND idletime >= 91 AND idletime <= 100
)
SELECT Slice
, ROUND(Percent, 2) AS Percentage
FROM STAT_SLICES
WHERE NOT Percentage IS NULL
ORDER BY Percentage DESC
The important part of the table is:
"0" "vmstatID" "INTEGER" "0" "NULL" "1"
"1" "date" "TEXT" "1" "CURRENT_DATE" "0"
"2" "time" "TEXT" "1" "CURRENT_TIME" "0"
…
"18" "idletime" "INTEGER" "1" "NULL" "0"
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.
I tried to change:
idletime >= 0 AND idletime <= 10
to:
idletime BETWEEN 0 AND 10
but that seemed to take more time. (Between 10 to 100% more.)
I did the following measurements if that is any help.
The following takes about 900 ms
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 (
SELECT COUNT(*)
, COUNT(*) / 100.0
, likeString
FROM vmstat
, STAT_DATE
WHERE date LIKE likeString
)
SELECT ' 0 - 10'
, COUNT(*)
, COUNT(*) / onePercent
FROM vmstat
, STAT_INFO
WHERE date LIKE likeString
AND idletime BETWEEN 0 AND 10
When made simpler it takes about 500 ms
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 "%"
)
SELECT ' 0 - 10'
, COUNT(*)
FROM vmstat
, STAT_DATE
WHERE date LIKE likeString
AND idletime BETWEEN 0 AND 10
And the following takes about 300 ms:
SELECT ' 0 - 10'
, COUNT(*)
FROM vmstat
WHERE idletime BETWEEN 0 AND 10
So probably when I write a program I could make it run in 3 seconds.
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)
--
Cecil Westerhof
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
