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

[Lichtenberger, Christian]

Hi

We currently compare ODB with native SQL by use of a SQLite database.
The comparison tests several scenarios, by use of the same object model (and db schema).

In the scenario "persist" ODB is equal (or faster) as native SQL. Both need in our case for 10000 (entries) x 2 (tables) x 3 (transactions) = 60000 (entries) about 5 seconds. ODB is by tendency a little bit faster. --> Yay!

In the scenario "delete by ids" ODB is much slower. In this scenario we remove 10000 (entries) x 2 (tables in 2 transactions) by entering the id.
In ODB we use "db->erase<Object>(id)" and in SQL we use "sqlite3_mprintf("delete from 'Object' where ID = '%d';", id)".
With native SQL we need 0,3 seconds and with ODB 18,3 seconds.

In the scenario "delete by query" ODB is also much slower. In this scenario we remove the same amount of data as before but use a query instead of a list of ids. In ODB we use "db->erase_query<Object>( query::id >= fromId && query::id <= toId )" and in SQL we use "sqlite3_mprintf("delete from 'Object' where id >= %d and id <= %d;", fromId, toId)".
With native SQL we need 0,06 seconds and with ODB 3,4 seconds.

In the scenario "delete object" ODB also seems to be slow. At the moment we did not have the corresponding native SQL code. But with ODB we use "db->erase(object). In this scenario we remove 10000 (entries) x 2 (tables) in one transaction.
ODB need 37,5 seconds

Further test scenarios come in future.

Why is there such a difference? ODB is about 60 times slower than native SQL for deleting data.
Did we do anything wrong?

ODB compiler is started with following options:
odb.exe -I..\..\\Qt4\include --std c++11 --database sqlite --generate-schema --generate-query --generate-session --profile qt --hxx-prologue "#include \"Global.hxx\"" --export-symbol MY_ODB_EXPORT <my File List>

Thanks,
Christian