Hello all!<br><br>I'm using SQLite FDO provider and need fast insert capabilities.<br><br>I had 4 options to test:<br>- individual inserts<br>
(- transactions - not supported)<br>(- batch insert - not supported)<br>- compiled queries (*** see below)<br>
<br>I expected compiled queries would be comparable to SQLite transactions.<br>I'm, using code, that looks like this:<br><br>FdoPtr<FdoIInsert> ins = (FdoIInsert*)conn->CreateCommand(FdoCommandType_Insert);<br>
while(!done)<br>{<br> FdoPtr<FdoIFeatureReader> fr = ins->Execute();<br> if(enoughExecutesToCommitToDatabase) //100,1000,...<br> ins = (FdoIInsert*)conn->CreateCommand(FdoCommandType_Insert); //make a new one and release the old one<br>
}<br><br><br>These are the results of timing 1000 inserts compared to unoptimized SQLite implementation:<br><br>- SQLite: 10.83s, 0.0108s per insert<br>- SQLite, transactions, 1000 per transaction: 0.19s, 0.0002s per insert<br>
- FDO: 9.57s, 0.0096s per insert<br>- FDO: compiled queries, 1000 per commit, 9.49s, 0.0095s per insert<br>- FDO: compiled queries, 100 per commit, the same, etc. ...<br>
<br>Compiled queries are the same as executing individual inserts, .<br>Of course I could use SQLite directly but I have to keep everything in FDO.<br><br>Am I missing something?<br>Are there some plans to implement transactions or batch inserts for SQLite FDO provider?<br>
Or is there some other way to speed up the inserts?<br><br>Thank you.<br><br>Igor Jarm<br><br><br><br>***<br>//Insert is special. We attempt to speed up inserts if the caller is cooperating.<br>//The contract is as follows -- as long as the caller reuses the SltInsert object,<br>
//he is working with a compiled query and within a single transaction. Whenever<br>//the command object is freed, the transaction is committed.<br>class SltInsert : public SltCommand<FdoIInsert><br>...<br>