Thank you, Traian.<br><br>I guess I'll have to look deeper into it, like you suggest.<br>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.<br>
I tested all this in Debug mode, but I think this should not affect speed so much.<br><br>Regards,<br>Igor<br><br><br><div class="gmail_quote">On Mon, Apr 26, 2010 at 3:30 PM, Traian Stanev <span dir="ltr"><<a href="mailto:traian.stanev@autodesk.com">traian.stanev@autodesk.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><br>
Hi Igor.<br>
<br>
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.<br>
<br>
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?<br>
<br>
<br>
Traian<br>
<br>
________________________________________<br>
<div class="im">From: <a href="mailto:fdo-users-bounces@lists.osgeo.org">fdo-users-bounces@lists.osgeo.org</a> [<a href="mailto:fdo-users-bounces@lists.osgeo.org">fdo-users-bounces@lists.osgeo.org</a>] On Behalf Of Igor Jarm [<a href="mailto:igor@sl-king.com">igor@sl-king.com</a>]<br>
</div>Sent: Monday, April 26, 2010 7:55 AM<br>
<div class="im">To: <a href="mailto:fdo-users@lists.osgeo.org">fdo-users@lists.osgeo.org</a><br>
</div><div class="im">Subject: [fdo-users] SQLite provider and transactions/batch inserts/<br>
<br>
</div><div><div></div><div class="h5">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>
</div></div><div><div></div><div class="h5">_______________________________________________<br>
fdo-users mailing list<br>
<a href="mailto:fdo-users@lists.osgeo.org">fdo-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/mailman/listinfo/fdo-users" target="_blank">http://lists.osgeo.org/mailman/listinfo/fdo-users</a><br>
</div></div></blockquote></div><br>