[odb-users] SQLIte "INSERT" slows dramatically...

[Andrew Cunningham]

Hi,

In general I am very happy with the performance ODB 2.4.0 /SQLite.


However I am running into some serious performance issues when creating
“large” databases - I  am seeing  SQLIte performance completely tanking ,
but only at a certain database size.


Assume I have am inserting a thousand “subsystem” objects to the database,
each of those “subsystem” objects has a std::vector of “elements” that can
be size 100-50,000

This is being done in a single transaction..



When creating this database, performance is perfectly fine until at some
point the time to persist a “subsystem and its elements” ( and all
subsequent ones)  increases dramatically by a factor of 1000.



For example, early on in the database creation a subsystem with ~1000
elements might take 0.08s to persist (along with its elements)

However, at a certain point, persisting a similar object  starts to take
118s ( 1000x longer).



Breaking into SQLite seems to show that the code is often in this phase.
Looks like some kind of database paging/moving is going on….



>             odb-sqlite-2.4-vc10.dll!winRead(sqlite3_file * id, void *
pBuf, int amt, __int64 offset)  Line 35654 + 0x2 bytes                C

                odb-sqlite-2.4-vc10.dll!readDbPage(PgHdr * pPg, unsigned
int iFrame)  Line 44461 + 0x19 bytes C

                odb-sqlite-2.4-vc10.dll!sqlite3PagerAcquire(Pager * pPager,
unsigned int pgno, PgHdr * * ppPage, int flags)  Line 46979 + 0xb bytes   C

                odb-sqlite-2.4-vc10.dll!getAndInitPage(BtShared * pBt,
unsigned int pgno, MemPage * * ppPage, int bReadonly)  Line 54790 + 0xd
bytes         C

                odb-sqlite-2.4-vc10.dll!moveToRoot(BtCursor * pCur)  Line
57596 + 0x22 bytes  C

                odb-sqlite-2.4-vc10.dll!sqlite3VdbeExec(Vdbe * p)  Line
9732 + 0x8 bytes             C

                odb-sqlite-2.4-vc10.dll!sqlite3Step(Vdbe * p)  Line 3812 C

                odb-sqlite-2.4-vc10.dll!sqlite3_step(sqlite3_stmt *
pStmt)  Line
3878 + 0x8 bytes              C


odb-sqlite-2.4-vc10.dll!odb::sqlite::insert_statement::execute()  Line 649
+ 0xd bytes    C++



Monitoring the process with “Process Monitor”, shows that SQLite is making
a very large number of calls to the Windows API ReadFile at this point.



Any ideas welcome!


Andrew