looping through a bunch of DB changes in a Rails project

Question

I have a Import function which takes a bunch of data in xml format and pastes it into my db. The problem is, that depending on the amount of data that process can take quite a long time. I see in the server log, that there are incredible lots of sql statements beeing executed do save back all the data.

How can I improve performance for that case? Is it possible to do all the operations only in memory and save them back with only one statement?

Update:

In response to HLGEM's answer:

I read through the bulk insert way, but it seems not to be very practical to me cause I have a lot of relations between the data... in order to put 100 data in a table I have to set the relations of those to other tables...

Is there a way to solve that? can I do encapsulated inserts?

Answer 1

When doing things to a database, NEVER work row by row, that is what is causing your problem. A bulk insert of some type will be much faster than a row by row process. Not knowing the database you are using it's hard to be more specific as to exactly how to do this. You should never even think about row-by-row processing but rather how to affect a set of data.

Answer 2

Well, it depends :)

• If the processing (before import) of the XML data is expensive, you can run the processing once, import to the DB, and then export plain SQL from the database. Then future imports can use this SQL for a bulk import, avoiding the need for XML processing.

• If the data import itself is expensive, then you probably want to get specific to your database in order to figure out how to speed it up. If you are using MySQL you might check out: http://serverfault.com/questions/37769/fast-bulk-import-of-a-large-dataset-into-mysql