Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.
share|improve this question
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). –  ypercube Jul 19 '12 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 Jul 19 '12 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? –  ypercube Jul 19 '12 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. –  ypercube Jul 19 '12 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.)
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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