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

[Igor Jarm]

Thank you, that's good news.
I forgot to mention I need this also for FDO 3.4.

Igor


On Mon, Apr 26, 2010 at 2:37 PM, Badreddine Karoui <
badreddine.karoui at autodesk.com> wrote:

>  Transaction is supported in 3.5:
>
>
> http://svnmirror.osgeo.org/fdo/trunk/Providers/SQLite/Src/Provider/SltCapabilities.h
>
>
>
> Badreddine
>
>
>
> *From:* fdo-users-bounces at lists.osgeo.org [mailto:
> fdo-users-bounces at lists.osgeo.org] *On Behalf Of *Igor Jarm
> *Sent:* Monday, April 26, 2010 7:56 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/6af103f2/attachment.html