On 2017/02/15 12:33 PM, Cecil Westerhof wrote:
I have a table vmstat that I use to store vmstat info. ;-)
At the moment it has more as 661 thousand records.
In principle the values of usertime, systemtime, idletime, waittime and
stolentime should add up to 100. I just wanted to check it. Of-course there
could be a rounding error, so I wrote the following query:
SELECT date
, time
, usertime
, systemtime
, idletime
, waittime
, stolentime
, (usertime + systemtime + idletime + waittime + stolentime) AS
totaltime
FROM vmstat
WHERE totaltime < 99 OR totaltime > 101
I did not like that, so I rewrote the WHERE to:
WHERE ABS(100 - totaltime) > 1
The funny thing the second WHERE is more efficient as the first, where I
would have expected it to be the other way around.
The first takes around 1.050 milliseconds.
The second takes around 950 milliseconds.
So the second is around 10% more efficient. Why is this?
That's because the first one executes 2 checks mostly - it first checks
to see if totaltime < 99, if so then it returns true, if not, then a
second comparison has to be done... so 2 comparison functions on many items.
The second check involves a single calculation and comparison - so what
you have deduced is that the "minus" function is slightly more efficient
than occasional extra comparison function.
Note however that this may not be entirely true. The Query might read
data from the disk cache (or several memory caches may be in play)
during the second run. Run each statement many times, and compare
average return times.
Also try this:
... WHERE totaltime NOT BETWEEN 99 AND 101;
or
... WHERE NOT (totaltime BETWEEN 99 AND 101);
Cheers,
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
