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

[Robert Konigsberg]

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.

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

>
>
> 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
>



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