Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine

Re^2: When to use forks, when to use threads ...?

by Krambambuli (Curate)
on Sep 05, 2008 at 07:42 UTC ( #709191=note: print w/replies, xml ) Need Help??

in reply to Re: When to use forks, when to use threads ...?
in thread When to use forks, when to use threads ...?

For a mass insertion into a database I would recommend neither approach. Instead I would suggest learning what native tools your database has for mass inserts. I would then use them.
...If the table you're inserting into has indexes I would strongly suggest dropping all indexes,...

That doesn't work as needed, unfortunately. It's not a simple insertion, but rather an insert-or-update-if-already-there process. So I cannot disable indexes either. Parallelizing the process in some way seemed the most appealing alternative to speed up things.

  • Comment on Re^2: When to use forks, when to use threads ...?

Replies are listed 'Best First'.
Re^3: When to use forks, when to use threads ...?
by perrin (Chancellor) on Sep 05, 2008 at 11:17 UTC
    If you're using MySQL, the fastest approach is to load the data in a temporary table and then use an INSERT... ON DUPLICATE UPDATE statement to copy it all into the existing table in one shot.
Re^3: When to use forks, when to use threads ...?
by tilly (Archbishop) on Sep 05, 2008 at 11:40 UTC
    Those are called upserts and the SQL 03 standard for that (may not be implemented in your database) is called merge. If that is not implemented and no other variants exist you can do an update followed by an insert of everything that is not found.

    In any case variants of Perrin's solution is the standard way to do it. Load a temporary table, then do the update within the database. If there is The primary key index should not be dropped, but all other indexes can be, and then can be re-created. With a good database that should be the most efficient way to go.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://709191]
erix the sun never sets on the CB

How do I use this? | Other CB clients
Other Users?
Others romping around the Monastery: (10)
As of 2017-04-24 12:32 GMT
Find Nodes?
    Voting Booth?
    I'm a fool:

    Results (439 votes). Check out past polls.