2017-02-15 12:02 GMT+01:00 R Smith <rsm...@rsweb.co.za>: > > 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. >
Minus function and abs 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. > I did. Not very much. But maybe I should try it a bit more often. Also try this: > ... WHERE totaltime NOT BETWEEN 99 AND 101; > or > ... WHERE NOT (totaltime BETWEEN 99 AND 101); > I will try those also. -- Cecil Westerhof _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
