We're working on refactoring many of our offline (python) scripts. I'm very new to any kind of SQL, but have been trying to learn as much as I can, especially in the performance field.

We have a script that loads a CSV from a client, that has user-defined fields in it, that must be matched to unique names (and eventually keys) in a separate table.

We write each row of the CSV to our DB in 4 different queries, to 4 different tables. All four queries relate to the same row of data, and use the first's primary key in each.

The latter 3 use the primary key from the first insert, so I assume that's a constraint.

We're getting around 100-150 IOPs on our Amazon RDS instance, so it's painfully slow trying to do something like this. We proved (well, somewhat), that the script is not the bottleneck. I created another script that just ran a loop to 'insert' into a dummy table with no indexes, and it ran about as fast.

Is there anything we can do to optimize our current method? I think there has to be, I couldn't imagine we really have to perform 2 million queries for our theoretical max-size CSV file of 500,000 rows.

I've ruled out (or think I have, I could be very, very wrong) a few options:

  1. batch-inserts: Since each insert goes to a separate table, can we really batch insert?
  2. stored procedure/functions: The headers are dynamic, so I don't think there's anything we can really do in that department.
  3. Building a query through string concatenation: Mostly because I don't know even where to start with this, but I also think it would be impossible since we need the keys from the first insert.
  • 3
    So, you are running 4 Insert statements for every row of the CSV file? No wonder it's slow. Try to batch insert, as you are thinking, into one (temporary) table and then do the tansfer from there to the 4 tables. That may need just 4 inserts (one for each table). Commented Jul 19, 2012 at 15:05
  • So, I should build a temporary table with identical headers (well, with unique names replaced with the primary keys), and concatenate a query string to place all rows into that table?
    – Travis
    Commented Jul 19, 2012 at 16:23
  • 4
    Fastest way to load data is LOAD DATA INFILE, read this: Why is 'LOAD DATA INFILE' faster than normal INSERT statements? Commented Jul 19, 2012 at 20:12
  • If you can''t do that, you can use INSERT INTO table VALUES (row1), (row2), ..., (rowN); where N can be a large number, say from 100 to 5000, depending on the size of your rows. A value of 500 for example, will mean that your script will need only 1000 insert statements to load the data into the temp table. Commented Jul 19, 2012 at 20:15

1 Answer 1

  1. Read the entire CSV into a new MyISAM table with no indexes.
  2. Write SQL statements to migrate that data en-masse to the other 4 tables.
  3. Drop that table.

100-150 IOPs -- not bad. That's about all you can get with a consumer-quality disk.

So, the trick is to minimize the IOPs.

  • What engine are you using? (InnoDB has some tunables that can help/hurt.)
  • What indexes? (The more indexes, the more I/O. And the cache may not be set optimally.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.