Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer

Re: Bulk data insertion into MySql

by sundialsvc4 (Abbot)
on Sep 02, 2011 at 14:54 UTC ( #923859=note: print w/replies, xml ) Need Help??

in reply to Bulk data insertion into MySql

When the use of a command-line or other tool is (for whatever reason) infeasible, bulk-insertion operations are categorically improved through the judicious use of three techniques:

  1. Table locking, or the use of transactions with a very strict “isolation level,” to preclude any conflicting operations by other users... or even access by other users.
  2. Transactions, in general, to permit the database system to do “lazy writes” while carrying out the operation.   The transaction is periodically committed and reopened so that the rollback-file does not become huge.
  3. Disabling index updates and referential integrity checks until the entire operation is finished.

There is a very-considerable amount of material on this subject on the Internet, and you need not be concerned if the article you read is not specifically talking about Perl.   The core concepts are universal.

Replies are listed 'Best First'.
Re^2: Bulk data insertion into MySql
by JavaFan (Canon) on Sep 03, 2011 at 22:18 UTC
    Transactions make insert operations in most database, including MySQL slower. There's no such thing as a "lazy write" - in order to remain consistent, data needs to be written to disk as soon as possible.

    Now, I'd still recommend putting everything in a transaction, but not for performance reasons.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://923859]
[stevieb]: been thinking... if it wasn't for Perl, I believe that I'd be done in tech. I don't do social media or the like, if I'm not coding I'm not on a device at all, and don't miss comms at all when I'm not around. I code for my job (which I like), but I think..
[stevieb]: ...I haven't coughed up my laptop because of Perl.

How do I use this? | Other CB clients
Other Users?
Others contemplating the Monastery: (2)
As of 2017-03-29 02:06 GMT
Find Nodes?
    Voting Booth?
    Should Pluto Get Its Planethood Back?

    Results (343 votes). Check out past polls.