[obdgpslogger] Starting to code up a script that merges multiple OBD databases

[Robert Konigsberg]

On Sun, Jan 2, 2011 at 7:10 PM, Robert Konigsberg <konigsberg at gmail.com>wrote:

>
>
> On Sun, Jan 2, 2011 at 7:02 PM, Gary Briggs <chunky at icculus.org> wrote:
>
>> On Sat, Jan 01, 2011 at 02:45:08PM -0500, Robert Konigsberg wrote:
>> > On Sat, Jan 1, 2011 at 2:40 PM, Gary Briggs <chunky at icculus.org> wrote:
>> >
>> > > On Sat, Jan 01, 2011 at 01:26:15PM -0500, Robert Konigsberg wrote:
>> > > > Hi,
>> > > >
>> > > > I've started a github project for a script that merges multiple
>> > > > OBD databases.
>> > >
>> > > Cool. Something I've always wanted :-)
>> > >
>> >
>> > What's that? A database merger tool or obdgps in github? :)
>>
>> Ewwww, git :-)
>>
>> > > For what it's worth, if I were going to implement this, I'd use this
>> > > puppy:
>> > > http://www.sqlite.org/lang_attach.html
>> > >
>> > > I'd open a connection to the main database I'm merging stuff into
>> > > [creating a fresh copy of a previous database if I'm planning on
>> leaving
>> > > the originals intact], then using ATTACH to get the second database
>> into
>> > > the same connection.
>> > >
>> > > That way, you get INSERT INTO SELECT FROM for free. [sqlite's "SELECT
>> > > INTO" equivalent]. Dunno if that'd actually help if I sat down and
>> > > figured the logic all the way through, but it might be useful to you
>> > >
>> >
>> > That's certainly useful, but at this point just reading/writing is doing
>> the
>> > trick for me. What I'm doing is suboptimal in a few places, but doesn't
>> > really much matter for my needs -- code it first, optimize it later. :)
>>
>> Fair enough. Do make sure to batch the writes:
>> http://sqlite.org/faq.html#q19
>
>
> Looks like I could get away with something even simpler by using the
> synchronous PRAGMA.
>
> http://sqlite.org/pragma.html#pragma_synchronous
>

So I'm not seeing any significant savings with transactions, nor with PRAGMA
synchronous=off.

For 460,211 rows,

The code as is ran in 41.261 seconds.

When I deferred creating indexes until the very end of the process (duh)
this dropped to 40.633 seconds.
When I merely removed a bunch of superfluous commit statements, that dropped
the most, to 38.975.
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.

The worst part about writing indexes at the end is the unbearable
response-free delay.


>
>
>>
>> > Hey qq, is this correct -- every field (other than trip, ecu and time)
>> is a
>> > REAL?
>>
>> I think so, yes
>>
>> Gary (-;
>> _______________________________________________
>> obdgpslogger mailing list
>> obdgpslogger at icculus.org
>> http://icculus.org/mailman/listinfo/obdgpslogger
>>
>
>
>
> --
> Robert Konigsberg
> konigsberg at gmail.com
>



-- 
Robert Konigsberg
konigsberg at gmail.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://icculus.org/pipermail/obdgpslogger/attachments/20110102/382c1130/attachment.htm>