Does anyone have any pointers on how I can speed up a sqlite3 query
that sorts a list of random integers?
I've noticed that sqlite3 takes a long time to sort random integers,
much longer than sorting a series of integers and much longer than
piping the list of random integers into the sort command. The script
below tests those three methods by generating 1 MM random integers,
loading them into a sqlite3 database, and then querying them. This
process models a real process in which a several million record
dataset is to be queried and sorted exactly once.
For this model process I've included data from a sample run. Notice
that querying the random integers takes about 7X longer than querying
the series and 16X longer than piping to the sort command.
Any pointers in the right direction are greatly appreciated,
especially given that this dataset only needs to be queried and sorted
once.
Regards,
- Robert
--- data ---
=== load data
1000000
real 38.80
user 37.31
sys 0.58
=== no sort
1000000
real 2.38
user 2.30
sys 0.16
=== sort series
1000000
real 14.17
user 12.63
sys 0.45
=== sort random
1000000
real 99.02
user 18.86
sys 9.87
=== shell sort random
1000000
real 5.91
user 5.76
sys 0.40
----- script ---
#!/bin/bash
echo "=== load data"
time -p echo {1..1000000}$'\t'${RANDOM} |
tr ' ' '\n' |
sqlite3 -init <(echo '
CREATE TABLE sample ( samp1 INTEGER, samp2 INTEGER)
;
.mod tabs
.imp "/dev/stdin" "sample"
select count(*) from sample
;
') sample.db .quit
echo "=== no sort"
time -p { grep -v ^# <<eof
.mode tabs
select *
from sample
;
eof
} | sqlite3 sample.db |
wc -l
echo "=== sort series"
time -p { grep -v ^# <<eof
.mode tabs
select *
from sample
order by samp1
;
eof
} | sqlite3 sample.db |
wc -l
echo "=== sort random"
time -p { grep -v ^# <<eof
.mode tabs
select *
from sample
order by samp2
;
eof
} | sqlite3 sample.db |
wc -l
echo "=== shell sort random"
time -p { grep -v ^# <<eof
.mode tabs
select *
from sample
;
eof
} | sqlite3 sample.db |
LC_ALL=C sort -k2,2n |
wc -l
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
