What kind of performance can I expect to get from sqlite inserting many many rows of blob data into a database? I know this depends on many factors, but I'm a little puzzled because I have written some tests that allow me to test various ways of inserting data and as far as I can tell I've tested almost every combination of pragmas and sqlite3_config options that might have an effect on performance, and I cannot find a way to insert any faster than about 20MB / second. On this same disk, I wrote a similar program to test the speed of writing sequentially to a file and in this case I was able to get around 75 MB / second. Here was what I've done in the two scenarios:
Raw Disk Write Test (75 MB / second) ----------------------- 1) Open a very large input file (a few GB) from physical disk 1 2) Open an output file on physical disk 2 2) Read from the input file sequentially in 4KB chunks 3) For each 4KB chunk, write the chunk to the output file Sqlite Test (20 MB / second) ---------------------- 1) (Same as above) 2) Create a sqlite database on physical disk 2 with one table that has 1 column of type BLOB 3) Created a prepared statement "INSERT INTO MyTable (ChunkData) values (?1)" 4) Read from the input file sequentially in 4KB chunks 5) For each 4KB chunk,use sqlite3_bind_blob with the SQLITE_TRANSIENT flag, execute the prepared statement, then reset the prepared statement. In addition, I've tried changing pretty much every sqlite option I can find, including replacing the sqlite_mem_methods with an extremely fast allocator using sqlite3_config(SQLITE_CONFIG_MALLOC, ...), disabling memory stat collection with sqlite3_config(SQLITE_CONFIG_MEMSTATUS, ...), pragma default_cache_size, pragma page_size, and pragma synchronous. Even pragma synchronous, which I thought would surely increase performance if I set it to 0, has no effect at all. I also tried creating the database with SQLITE_OPEN_NOMUTEX and this also had no effect on performance. 20MB / second seems awfully slow given that I write almost 4x that fast using regular disk i/o. Am I doing something wrong? I was hoping to be able to achieve at least 40MB / second of throughput. Regards, Zach _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
