Hello, I¹m relatively new to SQLite and have a couple questions centered around performance. I am running SQLite 3.1.3 on Mac OSX 10.4 (this is the version that is pre-installed).
I created a very simple database with one Table: sqlite3 test-db "create table Node (NodeId INTEGER PRIMARY KEY, url TEXT, filename TEXT);" I have then been writing some sample C programs making use of the C API, but have been very disappointed in the performance of insert¹ing into the database. For example, timing the performance of executing an insert statement into this table only gives me an insertion rate of 6 rows per second (³insert into Node (url, filename) values (foo¹,¹bar¹);²). I am wondering what options I have for speeding up these results. I know submitting my inserts as a batch transaction would help, but I¹m not sure that will be possible. I have also read about the PRAGMA SYNCHRONOUS setting, and while this gives me the speed boost I¹m looking for, I don¹t like giving up the security of the database not getting corrupted. How much of a performance boost will I see by precompiling my SQL statements? I¹m currently making use of sqlite3_get_table() - is it any slower than using sql3_exec() ? Thanks for any answers and pointers to performance tweaks for sqlite! Cheers, Andrew Cheyne GridIron Software
