AW: [odb-users] Erase/Remove Performance comparison to native SQL
Lichtenberger, Christian
christian.lichtenberger at etm.at
Tue Apr 15 12:32:06 EDT 2014
Hi Boris
I do some further tests and implement some other testcases. Below you can find the output of my test application for native SQL and ODB with much data (10000) and a further output with trace information with only 2 objects (to keep it readable).
Persist and Update is faster or equal with ODB.
But Select, Find and Delete is much faster with SQL Native.
As you can see in the SQL statements, they are very similar in both.
Only the testcase "Select Object IDs by ID-Range" is different because it is with ODB not possible to query directly ids. The objects must be loaded always. But that is not the problem because we could define a view in this case.
The used test object model is very simple. See below.
We use QSharedPointer for relations. QList we use only for inverse members.
What did we do wrong?
Our class model:
#pragma db object
class ObjectBase
{
public:
<ctor>
public:
<get/set-er)
private:
friend class odb::access;
#pragma db id auto
unsigned long id_;
int extid_;
QString name_;
#pragma db value_not_null inverse(object_)
QList<QLazyWeakPointer<Structure> > structures_;
#pragma db not_null
QSharedPointer<EnumType> objectType_;
#pragma db null
QSharedPointer<EnumType> type_;
};
#pragma db object
class Structure
{
public:
<ctor>
public:
<get/set-er)
private:
friend class odb::access;
#pragma db id auto
unsigned long id_;
#pragma db null
QSharedPointer<Structure> source_;
#pragma db not_null
QSharedPointer<ObjectBase> object_;
#pragma db not_null
QSharedPointer<EnumType> relationType_;
};
#pragma db object
class EnumType
{
public:
<ctor>
public:
<get/set-er)
private:
friend class odb::access;
#pragma db id auto
unsigned long id_;
int discriminator_;
int value_;
QString name_;
QByteArray flags_;
};
e.g. following function is called in case of Testcase: Select Objects by ID-Range
QList<QSharedPointer<Structure>> TestSelect(int fromId, int toId)
{
typedef odb::query<Structure> query;
typedef odb::result<Structure> result;
QList<QSharedPointer<Structure>> structures;
transaction t (db_->begin ());
if (this->doSqlTrace) t.tracer(odb::stderr_tracer);
result r (db_->query<Structure>(query::id >= fromId && query::id <= toId));
for (result::iterator i (r.begin ()); i != r.end (); ++i)
{
structures.append(i.load());
}
t.commit();
return structures;
}
And e.g. following in case of Testcase: Delete Objects by Query (ID-Range)
void RemoveAllByQuery(int fromId, int toId)
{
typedef odb::query<Structure> query;
typedef odb::result<Structure> result;
transaction t (db_->begin ());
if (this->doSqlTrace) t.tracer(odb::stderr_tracer);
db_->erase_query<Structure>( query::id >= fromId && query::id <= toId );
t.commit();
}
This is the output of my test application with 10000 ( x 2 Tables) for SQL native:
===================================================
Testcase: Persist Objects
generating content... for transaction 1 with (10000 elements)
elapsed time for saving data to database: 1.88
generating content... for transaction 2 with (10000 elements)
elapsed time for saving data to database: 1.265
generating content... for transaction 3 with (10000 elements)
elapsed time for saving data to database: 1.423
elapsed global time for saving data to database: 4.568, average per transaction 1.52267
====================================================
Testcase: Select Objects by ID-Range
elapsed time for selecting 10000 data classes from database: 0.593
====================================================
Testcase: Update Objects
elapsed time for updateing 10000 data classes from database: 1.518
====================================================
Testcase: Delete Objects
elapsed time for deleting 10000 data classes from database: 0.275
====================================================
Testcase: Select Object IDs by ID-Range
elapsed time for selecting 10000 data by id range from database: 0.019
====================================================
Testcase: Delete Objects by IDs
elapsed time for deleting 10000 data by ids from database: 0.242
====================================================
Testcase: Find Objects by IDs
elapsed time for finding and loading 10000 data by ids from database: 1.61
====================================================
Testcase: Delete Objects by Query (ID-Range)
elapsed time for deleting 10000 data by id range query from database: 0.023
====================================================
Testcase: Find non existing Objects by IDs
elapsed time for finding (of non existing) and loading of 10000 data from database: 0.482
The same for ODB:
====================================================
Testcase: Persist Objects
generating content... for transaction 1 with (10000 elements)
elapsed time for saving data to database: 0.946
generating content... for transaction 2 with (10000 elements)
elapsed time for saving data to database: 1.357
generating content... for transaction 3 with (10000 elements)
elapsed time for saving data to database: 1.31
elapsed global time for saving data to database: 3.613, average per transaction 1.20433
====================================================
Testcase: Select Objects by ID-Range
elapsed time for selecting 10000 data classes from database: 26.539
====================================================
Testcase: Update Objects
elapsed time for updateing 10000 data classes from database: 1.878
====================================================
Testcase: Delete Objects
elapsed time for deleting 10000 data classes from database: 43.545
====================================================
Testcase: Select Object IDs by ID-Range
elapsed time for selecting 10000 data by id range from database: 20.864
====================================================
Testcase: Delete Objects by IDs
elapsed time for deleting 10000 data by ids from database: 21.701
====================================================
Testcase: Find Objects by IDs
elapsed time for finding and loading 10000 data by ids from database: 9.822
====================================================
Testcase: Delete Objects by Query (ID-Range)
elapsed time for deleting 10000 data by id range query from database: 3.309
====================================================
Testcase: Find non existing Objects by IDs
elapsed time for finding (of non existing) and loading of 10000 data from database: 0.008
And now the output inclusive trace information for SQL-Native but with only 2 instead of 10000 objects:
====================================================
Testcase: Persist Objects
generating content... for transaction 1 with (2 elements)
INSERT INTO 'wbeObjectBase' (extId, name, objectType, type) VALUES ('1', 'Test1', '3', '1');
INSERT INTO 'wbeStructure' (source, object, relationType) VALUES ( '1', '1', '5');
INSERT INTO 'wbeObjectBase' (extId, name, objectType, type) VALUES ('2', 'Test2', '3', '1');
INSERT INTO 'wbeStructure' (source, object, relationType) VALUES ( '1', '2', '5');
elapsed time for saving data to database: 0.1
generating content... for transaction 2 with (2 elements)
INSERT INTO 'wbeObjectBase' (extId, name, objectType, type) VALUES ('2', 'Test2', '3', '1');
INSERT INTO 'wbeStructure' (source, object, relationType) VALUES ( '1', '3', '5');
INSERT INTO 'wbeObjectBase' (extId, name, objectType, type) VALUES ('4', 'Test4', '3', '1');
INSERT INTO 'wbeStructure' (source, object, relationType) VALUES ( '1', '4', '5');
elapsed time for saving data to database: 0.032
generating content... for transaction 3 with (2 elements)
INSERT INTO 'wbeObjectBase' (extId, name, objectType, type) VALUES ('3', 'Test3', '3', '1');
INSERT INTO 'wbeStructure' (source, object, relationType) VALUES ( '1', '5', '5');
INSERT INTO 'wbeObjectBase' (extId, name, objectType, type) VALUES ('6', 'Test6', '3', '1');
INSERT INTO 'wbeStructure' (source, object, relationType) VALUES ( '1', '6', '5');
elapsed time for saving data to database: 0.024
elapsed global time for saving data to database: 0.156, average per transaction 0.052
====================================================
Testcase: Select Objects by ID-Range
SELECT id, source, object, relationType FROM 'wbeStructure' WHERE id >= 1 and id <= 2;
SELECT id, discriminator, value, name, flags FROM 'wbeEnumType' WHERE id = 5;
SELECT id, extId, name, objectType, type FROM 'wbeObjectBase' WHERE id = 1;
SELECT id, discriminator, value, name, flags FROM 'wbeEnumType' WHERE id = 1;
SELECT id, discriminator, value, name, flags FROM 'wbeEnumType' WHERE id = 3;
SELECT id, source, object, relationType FROM 'wbeStructure' WHERE id = 1;
SELECT id, extId, name, objectType, type FROM 'wbeObjectBase' WHERE id = 2;
elapsed time for selecting 2 data classes from database: 0.004
====================================================
Testcase: Update Objects
UPDATE 'wbeObjectBase' SET extId = '1', name = 'AAAA_Test1', objectType = '3', type = '1' WHERE id = '1';
UPDATE 'wbeStructure' SET source = '1', object = '1', relationType = '5' WHERE id = '1';
UPDATE 'wbeObjectBase' SET extId = '2', name = 'AAAA_Test2', objectType = '3', type = '1' WHERE id = '2';
UPDATE 'wbeStructure' SET source = '1', object = '2', relationType = '5' WHERE id = '2';
elapsed time for updateing 2 data classes from database: 0.023
====================================================
Testcase: Delete Objects
DELETE FROM 'wbeObjectBase' WHERE ID = '1';
DELETE FROM 'wbeStructure' WHERE ID = '1';
DELETE FROM 'wbeObjectBase' WHERE ID = '2';
DELETE FROM 'wbeStructure' WHERE ID = '2';
elapsed time for deleting 2 data classes from database: 0.018
====================================================
Testcase: Select Objects by IDs
SELECT id, object FROM 'wbeStructure' WHERE id >= 3 and id <= 4;
elapsed time for selecting 2 data by id range from database: 0.001
====================================================
Testcase: Delete Objects by IDs
DELETE FROM 'wbeStructure' WHERE ID = '3';
DELETE FROM 'wbeStructure' WHERE ID = '4';
DELETE FROM 'wbeObjectBase' WHERE ID = '3';
DELETE FROM 'wbeObjectBase' WHERE ID = '4';
elapsed time for deleting 2 data by ids from database: 0.024
====================================================
Testcase: Find Objects by IDs
SELECT id, source, object, relationType FROM 'wbeStructure' WHERE id = 5;
SELECT id, source, object, relationType FROM 'wbeStructure' WHERE id = 1;
SELECT id, discriminator, value, name, flags FROM 'wbeEnumType' WHERE id = 5;
SELECT id, extId, name, objectType, type FROM 'wbeObjectBase' WHERE id = 5;
SELECT id, discriminator, value, name, flags FROM 'wbeEnumType' WHERE id = 1;
SELECT id, discriminator, value, name, flags FROM 'wbeEnumType' WHERE id = 3;
SELECT id, source, object, relationType FROM 'wbeStructure' WHERE id = 6;
SELECT id, source, object, relationType FROM 'wbeStructure' WHERE id = 1;
SELECT id, extId, name, objectType, type FROM 'wbeObjectBase' WHERE id = 6;
elapsed time for finding and loading 2 data by ids from database: 0.006
====================================================
Testcase: Delete Objects by Query (ID-Range)
DELETE FROM 'wbeStructure' WHERE id >= 5 and id <= 6;
elapsed time for deleting 2 data by id range query from database: 0.017
====================================================
Testcase: Find non existing Objects by IDs
SELECT id, source, object, relationType FROM 'wbeStructure' WHERE id = 7;
SELECT id, source, object, relationType FROM 'wbeStructure' WHERE id = 8;
elapsed time for finding (of non existing) and loading of 2 data from database: 0.002
And the same for ODB:
====================================================
Testcase: Persist Objects
generating content... for transaction 1 with (2 elements)
INSERT INTO "wbeObjectBase" ("id", "extid", "name", "objectType", "type") VALUES (?, ?, ?, ?, ?)
INSERT INTO "wbeStructure" ("id", "source", "object", "relationType") VALUES (?, ?, ?, ?)
INSERT INTO "wbeObjectBase" ("id", "extid", "name", "objectType", "type") VALUES (?, ?, ?, ?, ?)
INSERT INTO "wbeStructure" ("id", "source", "object", "relationType") VALUES (?, ?, ?, ?)
elapsed time for saving data to database: 0.033
generating content... for transaction 2 with (2 elements)
INSERT INTO "wbeObjectBase" ("id", "extid", "name", "objectType", "type") VALUES (?, ?, ?, ?, ?)
INSERT INTO "wbeStructure" ("id", "source", "object", "relationType") VALUES (?, ?, ?, ?)
INSERT INTO "wbeObjectBase" ("id", "extid", "name", "objectType", "type") VALUES (?, ?, ?, ?, ?)
INSERT INTO "wbeStructure" ("id", "source", "object", "relationType") VALUES (?, ?, ?, ?)
elapsed time for saving data to database: 0.026
generating content... for transaction 3 with (2 elements)
INSERT INTO "wbeObjectBase" ("id", "extid", "name", "objectType", "type") VALUES (?, ?, ?, ?, ?)
INSERT INTO "wbeStructure" ("id", "source", "object", "relationType") VALUES (?, ?, ?, ?)
INSERT INTO "wbeObjectBase" ("id", "extid", "name", "objectType", "type") VALUES (?, ?, ?, ?, ?)
INSERT INTO "wbeStructure" ("id", "source", "object", "relationType") VALUES (?, ?, ?, ?)
elapsed time for saving data to database: 0.032
elapsed global time for saving data to database: 0.091, average per transaction 0.0303333
====================================================
Testcase: Select Objects by ID-Range
SELECT "wbeStructure"."id", "wbeStructure"."source", "wbeStructure"."object", "wbeStructure"."relationType" FROM "wbeStructure" WHERE ("wbeStructure"."id" >= ?) AND ("wbeStructure"."id" <= ?)
SELECT "wbeObjectBase"."id", "wbeObjectBase"."extid", "wbeObjectBase"."name", "wbeObjectBase"."objectType", "wbeObjectBase"."type" FROM "wbeObjectBase" WHERE "wbeObjectBase"."id"=?
SELECT "wbeEnumType"."id", "wbeEnumType"."discriminator", "wbeEnumType"."value", "wbeEnumType"."name", "wbeEnumType"."flags" FROM "wbeEnumType" WHERE "wbeEnumType"."id"=?
SELECT "wbeStructure"."id" FROM "wbeStructure" WHERE "wbeStructure"."object"=?
SELECT "wbeEnumType"."id", "wbeEnumType"."discriminator", "wbeEnumType"."value", "wbeEnumType"."name", "wbeEnumType"."flags" FROM "wbeEnumType" WHERE "wbeEnumType"."id"=?
SELECT "wbeObjectBase"."id", "wbeObjectBase"."extid", "wbeObjectBase"."name", "wbeObjectBase"."objectType", "wbeObjectBase"."type" FROM "wbeObjectBase" WHERE "wbeObjectBase"."id"=?
SELECT "wbeEnumType"."id", "wbeEnumType"."discriminator", "wbeEnumType"."value", "wbeEnumType"."name", "wbeEnumType"."flags" FROM "wbeEnumType" WHERE "wbeEnumType"."id"=?
SELECT "wbeStructure"."id" FROM "wbeStructure" WHERE "wbeStructure"."object"=?
SELECT "wbeEnumType"."id", "wbeEnumType"."discriminator", "wbeEnumType"."value", "wbeEnumType"."name", "wbeEnumType"."flags" FROM "wbeEnumType" WHERE "wbeEnumType"."id"=?
elapsed time for selecting 2 data classes from database: 0.064
====================================================
Testcase: Update Objects
UPDATE "wbeObjectBase" SET "extid"=?, "name"=?, "objectType"=?, "type"=? WHERE "id"=?
UPDATE "wbeStructure" SET "source"=?, "object"=?, "relationType"=? WHERE "id"=?
UPDATE "wbeObjectBase" SET "extid"=?, "name"=?, "objectType"=?, "type"=? WHERE "id"=?
UPDATE "wbeStructure" SET "source"=?, "object"=?, "relationType"=? WHERE "id"=?
elapsed time for updateing 2 data classes from database: 0.03
====================================================
Testcase: Delete Objects
DELETE FROM "wbeObjectBase" WHERE "id"=?
DELETE FROM "wbeStructure" WHERE "id"=?
DELETE FROM "wbeObjectBase" WHERE "id"=?
DELETE FROM "wbeStructure" WHERE "id"=?
elapsed time for deleting 2 data classes from database: 0.021
====================================================
Testcase: Select Objects by IDs
SELECT "wbeStructure"."id", "wbeStructure"."source", "wbeStructure"."object", "wbeStructure"."relationType" FROM "wbeStructure" WHERE ("wbeStructure"."id" >= ?) AND ("wbeStructure"."id" <= ?)
SELECT "wbeObjectBase"."id", "wbeObjectBase"."extid", "wbeObjectBase"."name", "wbeObjectBase"."objectType", "wbeObjectBase"."type" FROM "wbeObjectBase" WHERE "wbeObjectBase"."id"=?
SELECT "wbeEnumType"."id", "wbeEnumType"."discriminator", "wbeEnumType"."value", "wbeEnumType"."name", "wbeEnumType"."flags" FROM "wbeEnumType" WHERE "wbeEnumType"."id"=?
SELECT "wbeStructure"."id" FROM "wbeStructure" WHERE "wbeStructure"."object"=?
SELECT "wbeEnumType"."id", "wbeEnumType"."discriminator", "wbeEnumType"."value", "wbeEnumType"."name", "wbeEnumType"."flags" FROM "wbeEnumType" WHERE "wbeEnumType"."id"=?
SELECT "wbeObjectBase"."id", "wbeObjectBase"."extid", "wbeObjectBase"."name", "wbeObjectBase"."objectType", "wbeObjectBase"."type" FROM "wbeObjectBase" WHERE "wbeObjectBase"."id"=?
SELECT "wbeEnumType"."id", "wbeEnumType"."discriminator", "wbeEnumType"."value", "wbeEnumType"."name", "wbeEnumType"."flags" FROM "wbeEnumType" WHERE "wbeEnumType"."id"=?
SELECT "wbeStructure"."id" FROM "wbeStructure" WHERE "wbeStructure"."object"=?
SELECT "wbeEnumType"."id", "wbeEnumType"."discriminator", "wbeEnumType"."value", "wbeEnumType"."name", "wbeEnumType"."flags" FROM "wbeEnumType" WHERE "wbeEnumType"."id"=?
elapsed time for selecting 2 data by id range from database: 0.068
====================================================
Testcase: Delete Objects by IDs
DELETE FROM "wbeStructure" WHERE "id"=?
DELETE FROM "wbeStructure" WHERE "id"=?
DELETE FROM "wbeObjectBase" WHERE "id"=?
DELETE FROM "wbeObjectBase" WHERE "id"=?
elapsed time for deleting 2 data by ids from database: 0.027
====================================================
Testcase: Find Objects by IDs
SELECT "wbeStructure"."id", "wbeStructure"."source", "wbeStructure"."object", "wbeStructure"."relationType" FROM "wbeStructure" WHERE "wbeStructure"."id"=?
SELECT "wbeObjectBase"."id", "wbeObjectBase"."extid", "wbeObjectBase"."name", "wbeObjectBase"."objectType", "wbeObjectBase"."type" FROM "wbeObjectBase" WHERE "wbeObjectBase"."id"=?
SELECT "wbeEnumType"."id", "wbeEnumType"."discriminator", "wbeEnumType"."value", "wbeEnumType"."name", "wbeEnumType"."flags" FROM "wbeEnumType" WHERE "wbeEnumType"."id"=?
SELECT "wbeStructure"."id" FROM "wbeStructure" WHERE "wbeStructure"."object"=?
SELECT "wbeEnumType"."id", "wbeEnumType"."discriminator", "wbeEnumType"."value", "wbeEnumType"."name", "wbeEnumType"."flags" FROM "wbeEnumType" WHERE "wbeEnumType"."id"=?
SELECT "wbeStructure"."id", "wbeStructure"."source", "wbeStructure"."object", "wbeStructure"."relationType" FROM "wbeStructure" WHERE "wbeStructure"."id"=?
SELECT "wbeObjectBase"."id", "wbeObjectBase"."extid", "wbeObjectBase"."name", "wbeObjectBase"."objectType", "wbeObjectBase"."type" FROM "wbeObjectBase" WHERE "wbeObjectBase"."id"=?
SELECT "wbeEnumType"."id", "wbeEnumType"."discriminator", "wbeEnumType"."value", "wbeEnumType"."name", "wbeEnumType"."flags" FROM "wbeEnumType" WHERE "wbeEnumType"."id"=?
SELECT "wbeStructure"."id" FROM "wbeStructure" WHERE "wbeStructure"."object"=?
SELECT "wbeEnumType"."id", "wbeEnumType"."discriminator", "wbeEnumType"."value", "wbeEnumType"."name", "wbeEnumType"."flags" FROM "wbeEnumType" WHERE "wbeEnumType"."id"=?
elapsed time for finding and loading 2 data by ids from database: 0.072
====================================================
Testcase: Delete Objects by Query (ID-Range)
DELETE FROM "wbeStructure" WHERE ("wbeStructure"."id" >= ?) AND ("wbeStructure"."id" <= ?)
elapsed time for deleting 2 data by id range query from database: 0.013
====================================================
Testcase: Find non existing Objects by IDs
SELECT "wbeStructure"."id", "wbeStructure"."source", "wbeStructure"."object", "wbeStructure"."relationType" FROM "wbeStructure" WHERE "wbeStructure"."id"=?
SELECT "wbeStructure"."id", "wbeStructure"."source", "wbeStructure"."object", "wbeStructure"."relationType" FROM "wbeStructure" WHERE "wbeStructure"."id"=?
elapsed time for finding (of non existing) and loading of 2 data from database: 0.015
Christian
-----Ursprüngliche Nachricht-----
Von: Boris Kolpackov [mailto:boris at codesynthesis.com]
Gesendet: Dienstag, 15. April 2014 13:28
An: Lichtenberger, Christian
Cc: odb-users at codesynthesis.com
Betreff: Re: [odb-users] Erase/Remove Performance comparison to native SQL
Hi Christian,
Lichtenberger, Christian writes:
> 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.
Generally, if you do the same things in ODB and native SQL, ODB should be at least as fast and often faster because of various reuse/caching mechanisms.
It is hard to say why there is the difference without seeing the code, including the object model/schema (e.g., do you use containers)? Can you show the relevant transactions for each test (ODB case)? Also, can you enable statement tracing for each transaction:
t.tracer (odb:stderr_tracer)
And see which statements actually get executed by ODB under the hood?
Do they match your native SQL?
I would be interested to hear what you will find.
Boris
More information about the odb-users
mailing list