How embarrassing, Just having the program print row counts to stdout every 10,000 writes instead of every 1,000 took the time down to 37.367s. That's still almost 10% faster after all.<div><br><div class="gmail_quote">
On Sun, Jan 2, 2011 at 7:41 PM, Robert Konigsberg <span dir="ltr"><<a href="mailto:konigsberg@gmail.com" target="_blank">konigsberg@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><br><br><div class="gmail_quote"><div><div></div><div>On Sun, Jan 2, 2011 at 7:10 PM, Robert Konigsberg <span dir="ltr"><<a href="mailto:konigsberg@gmail.com" target="_blank">konigsberg@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<br><br><div class="gmail_quote"><div><div></div><div>On Sun, Jan 2, 2011 at 7:02 PM, Gary Briggs <span dir="ltr"><<a href="mailto:chunky@icculus.org" target="_blank">chunky@icculus.org</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div>On Sat, Jan 01, 2011 at 02:45:08PM -0500, Robert Konigsberg wrote:<br>
> On Sat, Jan 1, 2011 at 2:40 PM, Gary Briggs <<a href="mailto:chunky@icculus.org" target="_blank">chunky@icculus.org</a>> wrote:<br>
><br>
> > On Sat, Jan 01, 2011 at 01:26:15PM -0500, Robert Konigsberg wrote:<br>
> > > Hi,<br>
> > ><br>
> > > I've started a github project for a script that merges multiple<br>
> > > OBD databases.<br>
> ><br>
> > Cool. Something I've always wanted :-)<br>
> ><br>
><br>
> What's that? A database merger tool or obdgps in github? :)<br>
<br>
</div>Ewwww, git :-)<br>
<div><br>
> > For what it's worth, if I were going to implement this, I'd use this<br>
> > puppy:<br>
> > <a href="http://www.sqlite.org/lang_attach.html" target="_blank">http://www.sqlite.org/lang_attach.html</a><br>
> ><br>
> > I'd open a connection to the main database I'm merging stuff into<br>
> > [creating a fresh copy of a previous database if I'm planning on leaving<br>
> > the originals intact], then using ATTACH to get the second database into<br>
> > the same connection.<br>
> ><br>
> > That way, you get INSERT INTO SELECT FROM for free. [sqlite's "SELECT<br>
> > INTO" equivalent]. Dunno if that'd actually help if I sat down and<br>
> > figured the logic all the way through, but it might be useful to you<br>
> ><br>
><br>
> That's certainly useful, but at this point just reading/writing is doing the<br>
> trick for me. What I'm doing is suboptimal in a few places, but doesn't<br>
> really much matter for my needs -- code it first, optimize it later. :)<br>
<br>
</div>Fair enough. Do make sure to batch the writes:<br>
<a href="http://sqlite.org/faq.html#q19" target="_blank">http://sqlite.org/faq.html#q19</a></blockquote><div><br></div></div></div><div>Looks like I could get away with something even simpler by using the synchronous PRAGMA. </div>
<div>
<br></div><div><a href="http://sqlite.org/pragma.html#pragma_synchronous" target="_blank">http://sqlite.org/pragma.html#pragma_synchronous</a></div></div></blockquote><div><br></div></div></div><div>So I'm not seeing any significant savings with transactions, nor with PRAGMA synchronous=off.</div>
<div><br></div><div>For 460,211 rows,</div><div><br></div><div>The code as is ran in 41.261 seconds.</div><div><br>When I deferred creating indexes until the very end of the process (duh) this dropped to 40.633 seconds.</div>
<div>When I merely removed a bunch of superfluous commit statements, that dropped the most, to 38.975.</div><div>PRAGMA synchronous off dropped it down less than 100ms more, and using BEGIN/COMMIT also did nothing at that point. Of course each of those is cumulative on top of prior stuff, so they might have made a big difference on their own. Either way, I'm gonna stick to PRAGMA SYNCHRONOUS OFF, writing indexes at the end, and letting sqlite control the commits.</div>
<div><br></div><div>The worst part about writing indexes at the end is the unbearable response-free delay.</div><div><div> </div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div class="gmail_quote"><div><div><br></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<br>
<div><br>
> Hey qq, is this correct -- every field (other than trip, ecu and time) is a<br>
> REAL?<br>
<br>
</div>I think so, yes<br>
<div><div></div><div><br>
Gary (-;<br>
_______________________________________________<br>
obdgpslogger mailing list<br>
<a href="mailto:obdgpslogger@icculus.org" target="_blank">obdgpslogger@icculus.org</a><br>
<a href="http://icculus.org/mailman/listinfo/obdgpslogger" target="_blank">http://icculus.org/mailman/listinfo/obdgpslogger</a><br>
</div></div></blockquote></div></div><br><br clear="all"><div><div></div><div><br>-- <br>Robert Konigsberg<br><a href="mailto:konigsberg@gmail.com" target="_blank">konigsberg@gmail.com</a><br>
</div></div></blockquote></div></div><div><div></div><div><br><br clear="all"><br>-- <br>Robert Konigsberg<br><a href="mailto:konigsberg@gmail.com" target="_blank">konigsberg@gmail.com</a><br>
</div></div></blockquote></div><br><br clear="all"><br>-- <br>Robert Konigsberg<br><a href="mailto:konigsberg@gmail.com" target="_blank">konigsberg@gmail.com</a><br>
</div>