-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Robert Citek wrote: > Does anyone have any pointers on how I can speed up a sqlite3 query > that sorts a list of random integers?
Funnily enough I use a sequence of random integers and sort them in my test suite when I need SQLite to spend some time working! > I've noticed that sqlite3 takes a long time to sort random integers, > much longer than sorting a series of integers That stands to reason since there will be more comparisons and more shuffling values around. > and much longer than > piping the list of random integers into the sort command. A considerable amount of time in your test script is actually spent in print calls to pipes. Here are some timings I got. Note that used tmpfs so that disk speeds are not a factor. I also sent output to a file on tmpfs not stdout and pipes. sqlite> .output xx sqlite> .timer on sqlite> select * from sample order by samp1; CPU Time: user 5.060000 sys 0.100000 sqlite> select * from sample order by samp2; CPU Time: user 7.860000 sys 3.120000 sqlite> pragma cache_size=100; CPU Time: user 0.000000 sys 0.000000 sqlite> select * from sample order by samp2; CPU Time: user 8.300000 sys 4.010000 sqlite> pragma cache_size=100000; CPU Time: user 0.000000 sys 0.000000 sqlite> select * from sample order by samp2; CPU Time: user 7.290000 sys 1.270000 sqlite> create index foo_i on sample(samp2); CPU Time: user 3.800000 sys 0.080000 sqlite> select * from sample order by samp2; CPU Time: user 3.710000 sys 0.000000 You can see that the cache_size made a difference as does an index. > Any pointers in the right direction are greatly appreciated, > especially given that this dataset only needs to be queried and sorted > once. If this is really what your data looks like the just use the sort command. If you have to use SQLite (which will involve import, sorting, output) then you need to play with page and cache_sizes and indices. However this overhead is unlikely to be as fast as sort. Roger -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkstEVsACgkQmOOfHg372QTMgwCgyYk8xjwa6fyvv/KCBh/EtHYu xz4AoLJHT9fmLCInoqASm/A9GjqyzEUE =yZbC -----END PGP SIGNATURE----- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
