Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?

Re: Perl DBI adding some, not all, records to MySQL database

by Moron (Curate)
on Jul 06, 2007 at 10:51 UTC ( #625238=note: print w/replies, xml ) Need Help??

in reply to Perl DBI adding some, not all, records to MySQL database

Nevertheless, if the ASCII data MAY contain duplicates, you have to do something about it. Ny general approach in designing a script for this type of requirement is rather different:

1) keeps sorted backup of the flat input files used per run

2) use the unix comm utility (twice per file) to create a forward and backward difference extracts based on the latest ASCII file and the previous one

3) compare with the previous run and execute batched deletion SQL from the backward differences

4) and then for the insert case, take advantage of MySql's bulk loader, using Perl to translate the flat format only for the "insert" case.

This means that updates are translated into a delete plus an insert per case, but overall in this context that performs better anyway. Unchanged records in the flat file are filtered out by comm. This package of measures overall performs many times faster than the more "obvious" per line design and was proved to be the most reliable.

Update: this is assuming there are no manual changes made in the meantime, in which case you have to back-generate an updated version of the "previous" (now simulated) ASCII file from your own data rather than use the one from the previous run.


^M Free your mind!

  • Comment on Re: Perl DBI adding some, not all, records to MySQL database

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://625238]
[LanX]: # Pestis Africana Suum
[erix]: that's an old plan, I think. Isn't there already a danewal (or some such name)?
[marto]: Build with -Wall
[erix]: Danewerk
[LanX]: I seem to remember that the danewall was build by the Frankish empire against the Danes
[erix]: 500 AD thru viking age
[erix]: that would make sense but wikipedia thinks otherwise
[LanX]: oh me was wrong ...
[erix]: but it always works both ways of course
[LanX]: Funny some weeks ago, I met people from the Cimbrian peninsula who didn't know it's called the Cimbrian peninsula ...

How do I use this? | Other CB clients
Other Users?
Others wandering the Monastery: (9)
As of 2018-03-22 18:42 GMT
Find Nodes?
    Voting Booth?
    When I think of a mole I think of:

    Results (283 votes). Check out past polls.