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

[Romica Dascalescu]

Hi Igor,

Many changes were been done in 3.5 SQLite provider. In case you need the last code for SQLite provider you have to port locally and build 3.5 version of the provider FDO with 3.4 version, or you should move to FDO 3.5. There are a few changes in the API but I think it should work. There is no plan to port 3.5 SQLite provider into FDO 3.4.
3.4 SQLite provider do not support transactions.

Regards,
Romy.
________________________________
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 8:53 AM
To: FDO Users Mail List
Subject: Re: [fdo-users] SQLite provider and transactions/batch inserts/

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<mailto: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> [mailto: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<mailto: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<mailto:fdo-users at lists.osgeo.org>
http://lists.osgeo.org/mailman/listinfo/fdo-users