Small (un-representative) benchmark on sqlite with blobs

Mon Jun 25 10:32:47 UTC 2007

On 25.06.07 02:40:33, Andreas Pakulat wrote:
> Hi,
> 
> David, Kris and myself had a (short) discussion about how to persist
> duchain data. Especially with multi-projects in mind we might get quite
> some data.
> 
> As David said he had issues with bdb in KDevelop3.4 we pondered wether
> using a sql db with blobs might be a good idea (with a really simple
> table layout). We can use the Qt db classes here to not have to fiddle
> with the low-level API of the dbs.
> 
> So I created a short testcase for reading a 140 Meg file from disk,
> storing it into the db and retrieving it again. All this done on sqlite,
> because mysql and postgresql need more setup to be done (with sqlite all
> one needs to provide is a database name).

Here are the results from a cold-cache-run. Fetching first:
andreas at morpheus:~/temp/testsqlite>./testsqlite
true
Fetching Start: QDateTime("Mo Jun 25 12:23:05 2007")
lines fetched: 0
Fetching Row number 0 : QDateTime("Mo Jun 25 12:23:08 2007")
Fetched size: 36700160
Fetching Row number 0 done: QDateTime("Mo Jun 25 12:23:08 2007")
Fetching Row number 1 : QDateTime("Mo Jun 25 12:23:10 2007")
Fetched size: 36700160
Fetching Row number 1 done: QDateTime("Mo Jun 25 12:23:10 2007")
Fetching Row number 2 : QDateTime("Mo Jun 25 12:23:18 2007")
Fetched size: 36700160
Fetching Row number 2 done: QDateTime("Mo Jun 25 12:23:18 2007")
Fetching Row number 3 : QDateTime("Mo Jun 25 12:23:20 2007")
Fetched size: 36700160
Fetching Row number 3 done: QDateTime("Mo Jun 25 12:23:20 2007")
Fetching Row number 4 : QDateTime("Mo Jun 25 12:23:23 2007")
Fetched size: 36700160
Fetching Row number 4 done: QDateTime("Mo Jun 25 12:23:23 2007")
Fetching Row number 5 : QDateTime("Mo Jun 25 12:23:25 2007")
Fetched size: 36700160
Fetching Row number 5 done: QDateTime("Mo Jun 25 12:23:25 2007")
Fetching Row number 6 : QDateTime("Mo Jun 25 12:23:30 2007")
Fetched size: 36700160
Fetching Row number 6 done: QDateTime("Mo Jun 25 12:23:30 2007")
Fetching Row number 7 : QDateTime("Mo Jun 25 12:23:32 2007")
Fetched size: 36700160
Fetching Row number 7 done: QDateTime("Mo Jun 25 12:23:32 2007")
Fetching Row number 8 : QDateTime("Mo Jun 25 12:23:34 2007")
Fetched size: 36700160
Fetching Row number 8 done: QDateTime("Mo Jun 25 12:23:34 2007")
Fetching Row number 9 : QDateTime("Mo Jun 25 12:23:36 2007")
Fetched size: 36700160
Fetching Row number 9 done: QDateTime("Mo Jun 25 12:23:36 2007")
Fetching Row number 10 : QDateTime("Mo Jun 25 12:23:41 2007")
Fetched size: 36700160
Fetching Row number 10 done: QDateTime("Mo Jun 25 12:23:41 2007")
Fetching Row number 11 : QDateTime("Mo Jun 25 12:23:47 2007")
Fetched size: 36700160
Fetching Row number 11 done: QDateTime("Mo Jun 25 12:23:47 2007")
Fetching Row number 12 : QDateTime("Mo Jun 25 12:23:53 2007")
Fetched size: 36700160
Fetching Row number 12 done: QDateTime("Mo Jun 25 12:23:53 2007")
Fetching Row number 13 : QDateTime("Mo Jun 25 12:23:58 2007")
Fetched size: 36700160
Fetching Row number 13 done: QDateTime("Mo Jun 25 12:23:58 2007")
Fetching Row number 14 : QDateTime("Mo Jun 25 12:24:04 2007")
Fetched size: 36700160
Fetching Row number 14 done: QDateTime("Mo Jun 25 12:24:04 2007")
Fetching Row number 15 : QDateTime("Mo Jun 25 12:24:12 2007")
Fetched size: 34254415
Fetching Row number 15 done: QDateTime("Mo Jun 25 12:24:12 2007")
Fetching Row number 16 : QDateTime("Mo Jun 25 12:24:17 2007")
Fetched size: 34254415
Fetching Row number 16 done: QDateTime("Mo Jun 25 12:24:17 2007")
Fetching Row number 17 : QDateTime("Mo Jun 25 12:24:23 2007")
Fetched size: 34254415
Fetching Row number 17 done: QDateTime("Mo Jun 25 12:24:23 2007")
Fetching Row number 18 : QDateTime("Mo Jun 25 12:24:28 2007")
Fetched size: 34254415
Fetching Row number 18 done: QDateTime("Mo Jun 25 12:24:28 2007")
Fetching Row number 19 : QDateTime("Mo Jun 25 12:24:32 2007")
Fetched size: 34254415
Fetching Row number 19 done: QDateTime("Mo Jun 25 12:24:32 2007")
Fetching End: QDateTime("Mo Jun 25 12:24:33 2007")

Surprisingly this was way faster, but I guess the reason is that my
memory was nearly empty as this was done after a cold boot.

Creation of the db looks like this (dropping the table was taking quite
some time, btw):

andreas at morpheus:~/temp/testsqlite>./testsqlite -create
true
Drop table result: QSqlError(-1, "", "")
create table result: QSqlError(-1, "", "")
"Reading Start(1xaa):" QDateTime("Mo Jun 25 12:28:03 2007")
Opening file: true
read bytes: 36700160
Reading Done: QDateTime("Mo Jun 25 12:28:08 2007")
Executing: "insert into f1(num,data) values(:num,:blob);"
Inserting Start: QDateTime("Mo Jun 25 12:28:08 2007")
true
Inserting End: QDateTime("Mo Jun 25 12:28:12 2007")
Inserting result: QSqlError(-1, "", "")
lines inserted: 1
"Reading Start(2xaa):" QDateTime("Mo Jun 25 12:28:12 2007")
Opening file: true
read bytes: 36700160
Reading Done: QDateTime("Mo Jun 25 12:28:14 2007")
Executing: "insert into f1(num,data) values(:num,:blob);"
Inserting Start: QDateTime("Mo Jun 25 12:28:14 2007")
true
Inserting End: QDateTime("Mo Jun 25 12:28:18 2007")
Inserting result: QSqlError(-1, "", "")
lines inserted: 1
"Reading Start(3xaa):" QDateTime("Mo Jun 25 12:28:18 2007")
Opening file: true
read bytes: 36700160
Reading Done: QDateTime("Mo Jun 25 12:28:20 2007")
Executing: "insert into f1(num,data) values(:num,:blob);"
Inserting Start: QDateTime("Mo Jun 25 12:28:20 2007")
true
Inserting End: QDateTime("Mo Jun 25 12:28:24 2007")
Inserting result: QSqlError(-1, "", "")
lines inserted: 1
"Reading Start(4xaa):" QDateTime("Mo Jun 25 12:28:24 2007")
Opening file: true
read bytes: 36700160
Reading Done: QDateTime("Mo Jun 25 12:28:26 2007")
Executing: "insert into f1(num,data) values(:num,:blob);"
Inserting Start: QDateTime("Mo Jun 25 12:28:26 2007")
true
Inserting End: QDateTime("Mo Jun 25 12:28:29 2007")
Inserting result: QSqlError(-1, "", "")
lines inserted: 1
"Reading Start(5xaa):" QDateTime("Mo Jun 25 12:28:29 2007")
Opening file: true
read bytes: 36700160
Reading Done: QDateTime("Mo Jun 25 12:28:33 2007")
Executing: "insert into f1(num,data) values(:num,:blob);"
Inserting Start: QDateTime("Mo Jun 25 12:28:33 2007")
true
Inserting End: QDateTime("Mo Jun 25 12:28:37 2007")
Inserting result: QSqlError(-1, "", "")
lines inserted: 1
"Reading Start(1xab):" QDateTime("Mo Jun 25 12:28:37 2007")
Opening file: true
read bytes: 36700160
Reading Done: QDateTime("Mo Jun 25 12:28:40 2007")
Executing: "insert into f1(num,data) values(:num,:blob);"
Inserting Start: QDateTime("Mo Jun 25 12:28:40 2007")
true
Inserting End: QDateTime("Mo Jun 25 12:28:43 2007")
Inserting result: QSqlError(-1, "", "")
lines inserted: 1
"Reading Start(2xab):" QDateTime("Mo Jun 25 12:28:43 2007")
Opening file: true
read bytes: 36700160
Reading Done: QDateTime("Mo Jun 25 12:28:45 2007")
Executing: "insert into f1(num,data) values(:num,:blob);"
Inserting Start: QDateTime("Mo Jun 25 12:28:45 2007")
true
Inserting End: QDateTime("Mo Jun 25 12:28:49 2007")
Inserting result: QSqlError(-1, "", "")
lines inserted: 1
"Reading Start(3xab):" QDateTime("Mo Jun 25 12:28:49 2007")
Opening file: true
read bytes: 36700160
Reading Done: QDateTime("Mo Jun 25 12:28:51 2007")
Executing: "insert into f1(num,data) values(:num,:blob);"
Inserting Start: QDateTime("Mo Jun 25 12:28:51 2007")
true
Inserting End: QDateTime("Mo Jun 25 12:28:55 2007")
Inserting result: QSqlError(-1, "", "")
lines inserted: 1
"Reading Start(4xab):" QDateTime("Mo Jun 25 12:28:55 2007")
Opening file: true
read bytes: 36700160
Reading Done: QDateTime("Mo Jun 25 12:28:58 2007")
Executing: "insert into f1(num,data) values(:num,:blob);"
Inserting Start: QDateTime("Mo Jun 25 12:28:58 2007")
true
Inserting End: QDateTime("Mo Jun 25 12:29:01 2007")
Inserting result: QSqlError(-1, "", "")
lines inserted: 1
"Reading Start(5xab):" QDateTime("Mo Jun 25 12:29:01 2007")
Opening file: true
read bytes: 36700160
Reading Done: QDateTime("Mo Jun 25 12:29:04 2007")
Executing: "insert into f1(num,data) values(:num,:blob);"
Inserting Start: QDateTime("Mo Jun 25 12:29:04 2007")
true
Inserting End: QDateTime("Mo Jun 25 12:29:10 2007")
Inserting result: QSqlError(-1, "", "")
lines inserted: 1
"Reading Start(1xac):" QDateTime("Mo Jun 25 12:29:10 2007")
Opening file: true
read bytes: 36700160
Reading Done: QDateTime("Mo Jun 25 12:29:16 2007")
Executing: "insert into f1(num,data) values(:num,:blob);"
Inserting Start: QDateTime("Mo Jun 25 12:29:16 2007")
true
Inserting End: QDateTime("Mo Jun 25 12:29:23 2007")
Inserting result: QSqlError(-1, "", "")
lines inserted: 1
"Reading Start(2xac):" QDateTime("Mo Jun 25 12:29:23 2007")
Opening file: true
read bytes: 36700160
Reading Done: QDateTime("Mo Jun 25 12:29:25 2007")
Executing: "insert into f1(num,data) values(:num,:blob);"
Inserting Start: QDateTime("Mo Jun 25 12:29:25 2007")
true
Inserting End: QDateTime("Mo Jun 25 12:29:32 2007")
Inserting result: QSqlError(-1, "", "")
lines inserted: 1
"Reading Start(3xac):" QDateTime("Mo Jun 25 12:29:32 2007")
Opening file: true
read bytes: 36700160
Reading Done: QDateTime("Mo Jun 25 12:29:34 2007")
Executing: "insert into f1(num,data) values(:num,:blob);"
Inserting Start: QDateTime("Mo Jun 25 12:29:34 2007")
true
Inserting End: QDateTime("Mo Jun 25 12:29:42 2007")
Inserting result: QSqlError(-1, "", "")
lines inserted: 1
"Reading Start(4xac):" QDateTime("Mo Jun 25 12:29:42 2007")
Opening file: true
read bytes: 36700160
Reading Done: QDateTime("Mo Jun 25 12:29:45 2007")
Executing: "insert into f1(num,data) values(:num,:blob);"
Inserting Start: QDateTime("Mo Jun 25 12:29:45 2007")
true
Inserting End: QDateTime("Mo Jun 25 12:29:55 2007")
Inserting result: QSqlError(-1, "", "")
lines inserted: 1
"Reading Start(5xac):" QDateTime("Mo Jun 25 12:29:55 2007")
Opening file: true
read bytes: 36700160
Reading Done: QDateTime("Mo Jun 25 12:29:56 2007")
Executing: "insert into f1(num,data) values(:num,:blob);"
Inserting Start: QDateTime("Mo Jun 25 12:29:56 2007")
true
Inserting End: QDateTime("Mo Jun 25 12:30:12 2007")
Inserting result: QSqlError(-1, "", "")
lines inserted: 1
"Reading Start(1xad):" QDateTime("Mo Jun 25 12:30:12 2007")
Opening file: true
read bytes: 34254415
Reading Done: QDateTime("Mo Jun 25 12:30:16 2007")
Executing: "insert into f1(num,data) values(:num,:blob);"
Inserting Start: QDateTime("Mo Jun 25 12:30:16 2007")
true
Inserting End: QDateTime("Mo Jun 25 12:30:42 2007")
Inserting result: QSqlError(-1, "", "")
lines inserted: 1
"Reading Start(2xad):" QDateTime("Mo Jun 25 12:30:42 2007")
Opening file: true
read bytes: 34254415
Reading Done: QDateTime("Mo Jun 25 12:30:45 2007")
Executing: "insert into f1(num,data) values(:num,:blob);"
Inserting Start: QDateTime("Mo Jun 25 12:30:45 2007")
true
Inserting End: QDateTime("Mo Jun 25 12:31:02 2007")
Inserting result: QSqlError(-1, "", "")
lines inserted: 1
"Reading Start(3xad):" QDateTime("Mo Jun 25 12:31:02 2007")
Opening file: true
read bytes: 34254415
Reading Done: QDateTime("Mo Jun 25 12:31:03 2007")
Executing: "insert into f1(num,data) values(:num,:blob);"
Inserting Start: QDateTime("Mo Jun 25 12:31:03 2007")
true
Inserting End: QDateTime("Mo Jun 25 12:31:14 2007")
Inserting result: QSqlError(-1, "", "")
lines inserted: 1
"Reading Start(4xad):" QDateTime("Mo Jun 25 12:31:14 2007")
Opening file: true
read bytes: 34254415
Reading Done: QDateTime("Mo Jun 25 12:31:16 2007")
Executing: "insert into f1(num,data) values(:num,:blob);"
Inserting Start: QDateTime("Mo Jun 25 12:31:16 2007")
true
Inserting End: QDateTime("Mo Jun 25 12:31:25 2007")
Inserting result: QSqlError(-1, "", "")
lines inserted: 1
"Reading Start(5xad):" QDateTime("Mo Jun 25 12:31:25 2007")
Opening file: true
read bytes: 34254415
Reading Done: QDateTime("Mo Jun 25 12:31:26 2007")
Executing: "insert into f1(num,data) values(:num,:blob);"
Inserting Start: QDateTime("Mo Jun 25 12:31:26 2007")
true
Inserting End: QDateTime("Mo Jun 25 12:31:36 2007")
Inserting result: QSqlError(-1, "", "")
lines inserted: 1

Andreas

-- 
Your love life will be happy and harmonious.