Good evening list,
I have been profiling the performance of SQLite version 3.6.1 against my current custom (hacktastic) runtime database solution (which I am hoping to replace with SQLite) and I just got a nasty and unexpected result: SQLite is a lot slower! I am running SQLite completely in memory during profiling using an in memory database (:memory:) and I am setting temp_store=MEMORY and journal_mode=MEMORY. I have ten tests statements that select data from an database that I load completely into memory from file before each test begins. I iterate over the results summing the values of a column (usually max) and record the duration over ten iterations to get a rough mean, high and low time count: 1. "SELECT * FROM test_item INNER JOIN test_container ON test_item.container_code = test_container.container_code" 2. "SELECT * FROM test_item INNER JOIN test_container ON test_item.container_code = test_container.container_code WHERE (test_item.item_code > '100')" 3. "SELECT * FROM test_item WHERE (test_item.item_code > '100')" 4. "SELECT * FROM test_item INNER JOIN test_container ON test_item.container_code = test_container.container_code WHERE (test_item.item_code > '100') AND (test_item.max > '50') AND (test_item.initial > '30')" 5. "SELECT max FROM test_item INNER JOIN test_container ON test_item.container_code = test_container.container_code"; 6. "SELECT * FROM test_item INNER JOIN test_container ON test_item.container_code = test_container.container_code ORDER BY max" 7. "SELECT * FROM test_item INNER JOIN test_container ON test_item.container_code = test_container.container_code WHERE (test_item.max > '50') ORDER BY max" 8. "SELECT max FROM test_item" 9. "SELECT * FROM test_item" SQLite: in memory. Test 1 Mean 288.470825, Low 286.080383, High 308.041931. (milliseconds) Test 2 Mean 270.140808, Low 267.758209, High 289.795166. (milliseconds) Test 3 Mean 68.888512, Low 66.573952, High 88.525116. (milliseconds) Test 4 Mean 255.051758, Low 251.905319, High 273.752533. (milliseconds) Test 5 Mean 103.347633, Low 101.023598, High 123.561203. (milliseconds) Test 6 Mean 2050.301025, Low 2047.153442, High 2069.840088. (milliseconds) Test 7 Mean 1927.293213, Low 1923.600952, High 1944.863159. (milliseconds) Test 8 Mean 16.426598, Low 13.990897, High 37.434727. (milliseconds) Test 9 Mean 68.630898, Low 66.280098, High 88.732208. (milliseconds) Custom DB: in memory. Test 1 (milliseconds) mean,low,high, 10.120694,9.463699,10.540760 Test 2 (milliseconds) mean,low,high, 10.226122,9.593320,10.600039 Test 3 (milliseconds) mean,low,high, 9.077908,8.492440,9.602920 Test 4 (milliseconds) mean,low,high, 10.259830,9.591120,10.668540 Test 5 (milliseconds) mean,low,high, 3.890896,3.626060,4.679040 Test 6 (milliseconds) mean,low,high, 10.156658,9.529779,10.696919 Test 7 (milliseconds) mean,low,high, 10.371894,9.598040,11.083039 Test 8 (milliseconds) mean,low,high, 1.155094,1.010600,2.416280 Test 9 (milliseconds) mean,low,high, 1.120254,1.002940,2.032100 As you can see the performance difference is significant, profiling indicates that 75%-80% of the test execution for SQLite is being spent inside sqlite3VdbeExec. What I'd like to know is if that is normal and if there is anything we can do with our queries, SQLite set-up or library configuration to improve the speed? So far I have found force inlining sqlite3VdbeSerialTypeLen helped the performance a bit. I am not using the amalgamation version of the source as I have our my VFS implementations for two of the platforms I work with based on the original win_os.c VFS and the amalgamation does not provide the necessary header files (os_common.h and sqliteInt.h) to make VFS integration possible. Other than by inserting the source for my VFS implementations directly into the amalgamation source, which I'd rather not do as it would make upgrading to new SQLite versions much more complex. I love SQLite's feature set, flexibility, tools and syntax compared to my current solution but I need SQLite to be at least as fast as my current solution to make it worth the switch. Cheers, Daniel Brown | Software Engineer "The best laid schemes o' mice an' men, gang aft agley" _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
