INSERT 6000 Rows - best practice

Question

I have a PHP script that calls an API method that can easily return 6k+ results.

I use PEAR DB_DataObject to write each row in a foreach loop to the DB.

The above script is batch processing 20 users at a time - and although some will only have a few results from the API others will have more. Worst case is that all have 1000's of results.

The loop to call the API seems to be ok, batches of 20 every 5 minutes works fine. My only concern is 1000's of mysql INSERTs for each user (with a long pause between each user for fresh API calls)

Is there a good way to do this? Or am I doing it a good way?!

Answer 1

Well, the fastest way to do it would be to do one insert statement with lots of values, like this:

INSERT INTO mytable (col1, col2) VALUES ( (?,?), (?,?), (?,?), ...)

But that would probably require ditching the DB_DataObject method you are using now. You'll just have to weigh the performance benefits of doing it that way vs. the "ease of use" benefits of using DB_DataObject.