[fdo-users] SQLite provider and transactions/batch inserts/
igor at sl-king.com
Mon Apr 26 09:59:52 EDT 2010
Thank you, Traian.
I guess I'll have to look deeper into it, like you suggest.
As this problem is concerned I don't insert any geometry, but plan to use
other databases for the same purpose as well, so I use FDO which is used in
this project primarily for GIS purposes.
I tested all this in Debug mode, but I think this should not affect speed so
On Mon, Apr 26, 2010 at 3:30 PM, Traian Stanev
<traian.stanev at autodesk.com>wrote:
> Hi Igor.
> Your loop should be fast even in 3.4. The provider has since (almost) the
> beginning used a SQLite transaction internally to do bulk inserts in the way
> your code shows.
> It is strange that you are getting bad performance then -- but hard to tell
> why without getting more information. You can try not bulking the insert
> manually (so create the Insert command only once). Also take a look at
> SQLteConverter.cpp which has a similar workflow for converting large files
> -- and compare to see what it does differently. Also, what kind of geometry
> are you inserting?
> From: fdo-users-bounces at lists.osgeo.org [fdo-users-bounces at lists.osgeo.org]
> On Behalf Of Igor Jarm [igor at sl-king.com]
> Sent: Monday, April 26, 2010 7:55 AM
> To: fdo-users at lists.osgeo.org
> Subject: [fdo-users] SQLite provider and transactions/batch inserts/
> Hello all!
> I'm using SQLite FDO provider and need fast insert capabilities.
> I had 4 options to test:
> - individual inserts
> (- transactions - not supported)
> (- batch insert - not supported)
> - compiled queries (*** see below)
> I expected compiled queries would be comparable to SQLite transactions.
> I'm, using code, that looks like this:
> FdoPtr<FdoIInsert> ins =
> FdoPtr<FdoIFeatureReader> fr = ins->Execute();
> if(enoughExecutesToCommitToDatabase) //100,1000,...
> ins = (FdoIInsert*)conn->CreateCommand(FdoCommandType_Insert);
> //make a new one and release the old one
> These are the results of timing 1000 inserts compared to unoptimized SQLite
> - SQLite: 10.83s, 0.0108s per insert
> - SQLite, transactions, 1000 per transaction: 0.19s, 0.0002s per insert
> - FDO: 9.57s, 0.0096s per insert
> - FDO: compiled queries, 1000 per commit, 9.49s, 0.0095s per insert
> - FDO: compiled queries, 100 per commit, the same, etc. ...
> Compiled queries are the same as executing individual inserts, .
> Of course I could use SQLite directly but I have to keep everything in FDO.
> Am I missing something?
> Are there some plans to implement transactions or batch inserts for SQLite
> FDO provider?
> Or is there some other way to speed up the inserts?
> Thank you.
> Igor Jarm
> //Insert is special. We attempt to speed up inserts if the caller is
> //The contract is as follows -- as long as the caller reuses the SltInsert
> //he is working with a compiled query and within a single transaction.
> //the command object is freed, the transaction is committed.
> class SltInsert : public SltCommand<FdoIInsert>
> fdo-users mailing list
> fdo-users at lists.osgeo.org
-------------- next part --------------
An HTML attachment was scrubbed...
More information about the fdo-users