What is the best way to perform a whole bunch of MySQL inserts?
My situation is that I am calculating a bunch of dates for my event table in my app. For example, if something recurs monthly, I calculate a start date and end date for that interval (1st and last day of month). I am calculating the table out to 2015 at this point. So you basically have two tables. Thing HAS_MANY Event.
This results in a lot of rows to insert when I create an instance of Thing and its related recurring Event dates.
My first pass was to calculate the dates, then iterate over the array, creating a New Event model and save()ing for each item in the array. This was pretty slow and on some queries exceeded 30 seconds!
Second pass was to do the same but use DAO and parameter binding. In this example I am binding parameters in the loop which I think is incorrect. Performance is equally as bad as the AR approach. Now that I consider the problem further I don’t really need to use parameter binding as the input is not coming from the user - dates are calculated by the app.
I know that when inserting a row at a time mysql needs to recreate the index with each pass which can really slow things down. I don’t see a way around that except maybe to do a ‘multiline’ or batch insert.
I was googling how to do a batch insert but need to read up on that more to see how its done. Does anyone have a quick ‘prototype’ of how to do a batch insert with DAO?
My other thought was to use delayed inserts but that would be last resort.