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

[Lichtenberger, Christian]

Hi Boris

Thanks for the analyses.
Now it looks very good!
Could you send (off-list) the test-code inclusive changes back to me.
To see directly what you have changed.

Than I have some further questions:

Is a session required to enable caching? Or why the number of statements got reduced?
I thought a cache exists per connection! But now I read in the manual that a session is primary a cache.

If a attribute is inverse and defined as QLazySharedPointer also a SELECT statement is performed?
I thought that the lazy means that it is loaded later. Or means lazy that a SELECT for the ids is performed and the objects behind are loaded later!?

The foreign key problem I must figure out later.

Thanks!!

Christian


-----Ursprüngliche Nachricht-----
Von: Boris Kolpackov [mailto:boris at codesynthesis.com] 
Gesendet: Donnerstag, 17. April 2014 14:05
An: Lichtenberger, Christian
Cc: odb-users at codesynthesis.com
Betreff: Re: [odb-users] Erase/Remove Performance comparison to native SQL

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