2017-02-15 12:02 GMT+01:00 R Smith <rsm...@rsweb.co.za>:
> 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);
>
I wrote the following Bash script:
#!/usr/bin/env bash
set -o errexit
set -o nounset
declare -r COUNT=10
declare -r SELECT='
SELECT date
, time
, usertime
, systemtime
, idletime
, waittime
, stolentime
, (usertime + systemtime + idletime + waittime + stolentime) AS
totaltime
FROM vmstat
'
declare -r ABS_VERSION="
${SELECT}
WHERE ABS(100 - totaltime) > 1
;"
declare -r OR_VERSION="
${SELECT}
WHERE totaltime < 99 OR totaltime > 101
;"
declare -r NOT_BETWEEN_VERSION="
${SELECT}
WHERE totaltime NOT BETWEEN 99 AND 101
;"
declare -r NOT_BETWEEN_VERSION2="
${SELECT}
WHERE NOT (totaltime BETWEEN 99 AND 101)
;"
function timeQuery {
for i in $(seq ${COUNT}) ; do
sqlite3 ~/Databases/general.sqlite <<EOT
.timer on
.output /dev/null
${1}
EOT
done
}
printf "Timing OR version\n"
timeQuery "${OR_VERSION}"
printf "\n\n\nTiming ABS version\n"
timeQuery "${ABS_VERSION}"
printf "\n\n\nTiming NOT BETWEEN version\n"
timeQuery "${NOT_BETWEEN_VERSION}"
printf "\n\n\nTiming NOT BETWEEN version 2\n"
timeQuery "${NOT_BETWEEN_VERSION2}"
This gives:
Timing OR version
Run Time: real 0.301 user 0.260000 sys 0.040000
Run Time: real 0.301 user 0.260000 sys 0.040000
Run Time: real 0.295 user 0.268000 sys 0.028000
Run Time: real 0.282 user 0.252000 sys 0.032000
Run Time: real 0.287 user 0.276000 sys 0.012000
Run Time: real 0.278 user 0.240000 sys 0.040000
Run Time: real 0.288 user 0.276000 sys 0.012000
Run Time: real 0.290 user 0.260000 sys 0.028000
Run Time: real 0.285 user 0.244000 sys 0.040000
Run Time: real 0.303 user 0.292000 sys 0.012000
Timing ABS version
Run Time: real 0.256 user 0.224000 sys 0.032000
Run Time: real 0.260 user 0.236000 sys 0.024000
Run Time: real 0.256 user 0.228000 sys 0.028000
Run Time: real 0.259 user 0.240000 sys 0.020000
Run Time: real 0.262 user 0.236000 sys 0.024000
Run Time: real 0.262 user 0.236000 sys 0.024000
Run Time: real 0.260 user 0.220000 sys 0.040000
Run Time: real 0.251 user 0.224000 sys 0.028000
Run Time: real 0.252 user 0.224000 sys 0.028000
Run Time: real 0.257 user 0.224000 sys 0.032000
Timing NOT BETWEEN version
Run Time: real 0.252 user 0.236000 sys 0.016000
Run Time: real 0.242 user 0.216000 sys 0.024000
Run Time: real 0.250 user 0.228000 sys 0.020000
Run Time: real 0.245 user 0.216000 sys 0.028000
Run Time: real 0.240 user 0.196000 sys 0.044000
Run Time: real 0.258 user 0.232000 sys 0.024000
Run Time: real 0.284 user 0.248000 sys 0.036000
Run Time: real 0.253 user 0.228000 sys 0.024000
Run Time: real 0.249 user 0.216000 sys 0.032000
Run Time: real 0.246 user 0.208000 sys 0.036000
Timing NOT BETWEEN version 2
Run Time: real 0.257 user 0.228000 sys 0.028000
Run Time: real 0.264 user 0.240000 sys 0.024000
Run Time: real 0.254 user 0.232000 sys 0.020000
Run Time: real 0.257 user 0.204000 sys 0.052000
Run Time: real 0.248 user 0.212000 sys 0.036000
Run Time: real 0.257 user 0.228000 sys 0.028000
Run Time: real 0.246 user 0.232000 sys 0.012000
Run Time: real 0.245 user 0.228000 sys 0.016000
Run Time: real 0.278 user 0.260000 sys 0.016000
Run Time: real 0.275 user 0.252000 sys 0.024000
So I would say that the OR version is less efficient as the other three.
And that there is not a big difference between those.
--
Cecil Westerhof
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
