[fdo-users] SQLite provider and transactions/batch inserts/

[Igor Jarm]

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
much.

Regards,
Igor


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?
>
>
> Traian
>
> ________________________________________
> 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 =
> (FdoIInsert*)conn->CreateCommand(FdoCommandType_Insert);
> while(!done)
> {
>    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
> implementation:
>
> - 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
> cooperating.
> //The contract is as follows -- as long as the caller reuses the SltInsert
> object,
> //he is working with a compiled query and within a single transaction.
> Whenever
> //the command object is freed, the transaction is committed.
> class SltInsert : public SltCommand<FdoIInsert>
> ...
> _______________________________________________
> fdo-users mailing list
> fdo-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/fdo-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/fdo-users/attachments/20100426/11fce717/attachment-0001.html