Update/insert in chunks?

[eluser]TheFuzzy0ne[/eluser]
I'm not sure about the in_array(). Only you will be able to tell that when you benchmark your application.

The only thing I can suggest that will definitely speed things up, is that you have a model that will generate a query for you. You could then insert results say 500(?) rows at a time, and see how you do then. 120 insert/update calls is definitely a lot better than 60,000. Although you're still inserting the same amount of data, it's less transactions, or rather round trips.

Just bear in mind that when you are hammering the database with insert/update queries, there is a maximum query length. You can find out the maximum query length (in bytes) by using the following query:

Code:

SHOW VARIABLES LIKE 'max_allowed_packet';

The results is essentially the number of characters you're query can contain, unless they are Unicode characters, in which case divide by 2.

So basically, you're model will generate a query that will insert many rows in a single statement. I know it's not really a solution as such, but I've never had to do the operations you need to do with so many tables.

It is possible to take this into account, and keep a running track of how many characters your query string contains. If you're going to add another part to the query that will take it over the limit, fire the query at the database, and then continue by building another query, and repeat until you have all the data in the database that you need.

Knowing my luck, one of the senior members will post and offer a really simple and elegant solution. I hope they do because I am running out of ideas fast.