>I'm not sure that theses tests are meaningful. First off, the conversion to >WAL mode takes time, and that time is being included in the result. >Secondly, WAL mode is persistent. Once it is set it stays set until it is >changed. So you cannot turn around and run another test after setting WAL >mode and >expect the subsequent test to be in rollback mode. What I would >like to see is something like this:
> cp trip-original.db trip.db > sqlite3 trip.db 'PRAGMA journal_mode=WAL' > time sqlite3 trip.db 'DELETE FROM trip WHERE key<=1400' > cp trip-original.db trip.db > sqlite3 trip.db 'PRAGMA journal_mode=DELETE' > time sqlite3 trip.db 'DELETE FROM trip WHERE key<=1400' Ah, I did not understand this. I ran three tests after enabling this: root@raspberrypi:/opt/obdpi/sql# sqlite3 trip.db 'PRAGMA journal_mode=WAL' wal # time sqlite3 trip.db "delete from trip where key<=200" real 0m0.642s # time sqlite3 trip.db "delete from trip where key<=400" real 0m1.262s # time sqlite3 trip.db "delete from trip where key<=600" real 0m1.322s I went ahead and ran the same tests without it, for reference (basically the same as the original email -- all 4 indexes) # cp trip-original.db trip.db # time sqlite3 trip.db "delete from trip where key<=200" real 0m0.686s # time sqlite3 trip.db "delete from trip where key<=400" real 0m1.545s # time sqlite3 trip.db "delete from trip where key<=600" real 0m1.539s It looks marginal. >You probably have fossil going, then. Which is good. But you could have just >clicked on the "Download: Tarball" link or the "Download: ZIP Archive" >link at http://www.sqlite.org/src4/info/7cc153f523 in order to get a copy of >the latest code. >Now that you have a fossil clone, you can always get the latest code by >running: > fossil update trunk >Yeah - I guess we don't have a configure script yet. You'll need to edit the >makefile to make it work on your system. My guess is that you probably just >need to >go to >http://www.sqlite.org/src4/artifact/72944b28eb3a?ln=161-167 and select some >compiler other than clang. Looks like I had to actually change this to gcc as well: http://www.sqlite.org/src4/artifact/72944b28eb3a?ln=22-22 Unfortunately, I ran into a few issues compiling with 'make'. rtree.c looks to have had sqlite3_malloc /free changed to sqlite4, but not the sqlite4_env parameter passed. I went ahead and set it to NULL, as mentioned in http://www.sqlite.org/src4/doc/trunk/www/porting.wiki I had to change a few instances of sqlite_int64 to sqlite4_int64 in the same file. I had some trouble linking functions in that same file as well. Anyway, I got through it.. I fixed some things and commented out others.. so this might not be functional =) I reimported the database, but the primary key being a real primary key in 4 did change the query. So, the numbers are much higher now. Results: # time /root/sqlite4/sqlite4 trip.db "delete from trip where key<=1358697579" real 0m0.623s # time /root/sqlite4/sqlite4 trip.db "delete from trip where key<=1358697779" real 0m0.430s # time /root/sqlite4/sqlite4 trip.db "delete from trip where key<=1358697979" real 0m0.611s There is some definite improvement there. 2-3x! In case you're interested: # sqlite4 trip.db 'PRAGMA journal_mode=WAL' # sqlite4 trip.db "delete from trip where key<=1358698379" real 0m0.748s # time sqlite4 trip.db "delete from trip where key<=1358698579" real 0m0.818s # time sqlite4 trip.db "delete from trip where key<=1358698779" real 0m0.816s Thanks for reading, this was a long one! -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
