I apologise for Debug embarrassment.<br>I repeated the test with Release build and the results are the same, a bit faster than with Debug build though.<br>I will look deeper into it and will let you know.<br><br>Regards,<br>
Igor<br><br><div class="gmail_quote">On Mon, Apr 26, 2010 at 4:11 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>
Performance benchmarks should never be done in Debug builds in general, because in Debug build the CPU ends up executing up to 10x more code, especially when STL is involved, and there is a bit of STL usage in the provider.<br>
<font color="#888888"><br>
Traian<br>
</font><div class="im"><br>
<br>
-----Original Message-----<br>
From: <a href="mailto:fdo-users-bounces@lists.osgeo.org">fdo-users-bounces@lists.osgeo.org</a> [mailto:<a href="mailto:fdo-users-bounces@lists.osgeo.org">fdo-users-bounces@lists.osgeo.org</a>] On Behalf Of Romica Dascalescu<br>
Sent: Monday, April 26, 2010 10:06 AM<br>
To: FDO Users Mail List<br>
</div><div><div></div><div class="h5">Subject: RE: [fdo-users] SQLite provider and transactions/batch inserts/<br>
<br>
There is a huge difference between debug (slow) and release (faster) :)<br>
<br>
Romy.<br>
________________________________<br>
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>
Sent: Monday, April 26, 2010 9:59 AM<br>
To: FDO Users Mail List<br>
Subject: Re: [fdo-users] SQLite provider and transactions/batch inserts/<br>
<br>
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>
On Mon, Apr 26, 2010 at 3:30 PM, Traian Stanev <<a href="mailto:traian.stanev@autodesk.com">traian.stanev@autodesk.com</a><mailto:<a href="mailto:traian.stanev@autodesk.com">traian.stanev@autodesk.com</a>>> wrote:<br>
<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>
From: <a href="mailto:fdo-users-bounces@lists.osgeo.org">fdo-users-bounces@lists.osgeo.org</a><mailto:<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><mailto:<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><mailto:<a href="mailto:igor@sl-king.com">igor@sl-king.com</a>>]<br>
Sent: Monday, April 26, 2010 7:55 AM<br>
To: <a href="mailto:fdo-users@lists.osgeo.org">fdo-users@lists.osgeo.org</a><mailto:<a href="mailto:fdo-users@lists.osgeo.org">fdo-users@lists.osgeo.org</a>><br>
Subject: [fdo-users] SQLite provider and transactions/batch inserts/<br>
<br>
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>
_______________________________________________<br>
fdo-users mailing list<br>
<a href="mailto:fdo-users@lists.osgeo.org">fdo-users@lists.osgeo.org</a><mailto:<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>
<br>
_______________________________________________<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>
_______________________________________________<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>