[odb-users] Erase/Remove Performance comparison to native SQL

[Boris Kolpackov]

Hi Christian,

[CC'ed odb-users back in.]

Thanks for the test. It took me several hours to wade through it
trying to figure out what's going on. The overall summary (for
those who don't feel like reading through the gory details) is
that you are either comparing different things (e.g., your native
test does one thing while the ODB test does another), you configure
SQLite differently for the two tests (e.g., foreign key support),
or both. In every test case where I addressed these, ODB is faster,
often significantly.

Ok, now for the details. I started with the select test. By comparing
the number of statements executed for each test, you immediately see
the difference which tells us that your ODB and native tests perform
different amount of work for some reason. After creating the session
inside the transaction, the number of statements in the ODB test got
reduced quite a bit, which tells us that before some objects were
loaded multiple time while now they are loaded only once.

Next, I looked at the inverse list in your ObjectBase class. In the
native test you populate this in an ad-hoc manner. That is, you
know that you are going to load all the structures for each object,
so you populate the list by hand. ODB doesn't do that since it has
no such application-specific knowledge. Instead, ODB has to run a
SELECT query to load each inverse relationship (you can still use
your ad-hoc approach with ODB by making the list transient and
populating it manually, just like you did in your native test).

To make the comparison apples-to-apples, I marked the list transient,
which means ODB won't be loading it. With this change, ODB is quite
a bit faster than your native test and the number of statements
executed by ODB is actually fewer which tells me that your native
test is probably unnecessarily re-loading the same objects multiple
times (ODB avoids this with the help of a session).

So the SELECT test was sorted. Next was the UPDATE test. This one
took me some time to figure out. Long story short, you create the
SQLite connection for your native test with foreign keys disabled
while ODB, by default, enables them.

Once I disabled foreign keys in ODB (pass 'false' as the third
argument to odb::sqlite::database and also comment out the 
"PRAGMA foreign_keys ..." statements in database.hxx), ODB
test was again faster than your native test.

With these changes, the rest of the tests also fell in line. Below
is my output for both cases. The only test where native is still
faster than ODB is "Select Object IDs by ID-Range", but, as you
said, to be a fair comparison you should use views rather than
loading the whole objects (including their relationships).


Native:

./driver -mode 1 -count 10000 -countTrans 3
Database connection test.s3db established, successfully!
====================================================
Testcase: Persist Objects
generating content... for transaction 1 with (10000 elements)
elapsed time for saving data to database: 0.39
generating content... for transaction 2 with (10000 elements)
elapsed time for saving data to database: 0.4
generating content... for transaction 3 with (10000 elements)
elapsed time for saving data to database: 0.39
elapsed global time for saving data to database: 1.18, average per transaction 0.393333
====================================================
Testcase: Select Objects by ID-Range
elapsed time for selecting 10000 data classes from database: 0.48
====================================================
Testcase: Update Objects 10000
elapsed time for updateing 10000 data classes from database: 0.29
====================================================
Testcase: Delete Objects 10000
elapsed time for deleting 10000 data classes from database: 0.25
====================================================
Testcase: Select Object IDs by ID-Range
elapsed time for selecting 10000 data by id range from database: 0.01
====================================================
Testcase: Delete Objects by IDs
elapsed time for deleting 10000 data by ids from database: 0.24
====================================================
Testcase: Find Objects by IDs
elapsed time for finding and loading 10000 data by ids from database: 1.23
====================================================
Testcase: Delete Objects by Query (ID-Range)
elapsed time for deleting 10000 data by id range query from database: 0.01
====================================================
Testcase: Find non existing Objects by IDs
elapsed time for finding (of non existing) and loading of 10000 data from database: 0.33

ODB:

./driver -mode 2 -count 10000 -countTrans 3
====================================================
Testcase: Persist Objects
generating content... for transaction 1 with (10000 elements)
elapsed time for saving data to database: 0.13
generating content... for transaction 2 with (10000 elements)
elapsed time for saving data to database: 0.13
generating content... for transaction 3 with (10000 elements)
elapsed time for saving data to database: 0.12
elapsed global time for saving data to database: 0.38, average per transaction 0.126667
====================================================
Testcase: Select Objects by ID-Range
elapsed time for selecting 10000 data classes from database: 0.15
====================================================
Testcase: Update Objects 10000
elapsed time for updateing 10000 data classes from database: 0.1
====================================================
Testcase: Delete Objects 10000
elapsed time for deleting 10000 data classes from database: 0.07
====================================================
Testcase: Select Object IDs by ID-Range
elapsed time for selecting 10000 data by id range from database: 0.19
====================================================
Testcase: Delete Objects by IDs
elapsed time for deleting 10000 data by ids from database: 0.08
====================================================
Testcase: Find Objects by IDs
elapsed time for finding and loading 10000 data by ids from database: 0.2
====================================================
Testcase: Delete Objects by Query (ID-Range)
elapsed time for deleting 10000 data by id range query from database: 0
====================================================
Testcase: Find non existing Objects by IDs
elapsed time for finding (of non existing) and loading of 10000 data from database: 0.02

Boris