Update/insert in chunks?

[eluser]TheFuzzy0ne[/eluser]
Right. OK then, scrap that idea...

What's the average size of the files you need to work with?

[eluser]codex[/eluser]
[quote author="TheFuzzy0ne" date="1234688429"]Right. OK then, scrap that idea...

What's the average size of the files you need to work with?[/quote]

The files differ greatly in size, but the largest I have come across is more than 80mb (167.000 products). I think I only need to deal with 10mb files max.

The temp table after insert of the 6000 records is approx. 4mb.

[eluser]codex[/eluser]
Is it possible to do the update in chunks? If so, how?

[eluser]TheFuzzy0ne[/eluser]
Yes, I'm certain it's possible to break it down, it's simply a question of how. I think I might have to leave this query open in the hope that someone else may have an answer for you. I'll gladly rethink about the strategy tomorrow, but at the moment is 3:22AM, so my brain functioning at peak efficiency.

Sorry I couldn't be of any more assistance, but rest assured that there is a solution, it's just a question of finding it. I think I might have to do a bit of Googling tomorrow.

[eluser]codex[/eluser]
[quote author="TheFuzzy0ne" date="1234689731"]Yes, I'm certain it's possible to break it down, it's simply a question of how. I think I might have to leave this query open in the hope that someone else may have an answer for you. I'll gladly rethink about the strategy tomorrow, but at the moment is 3:22AM, so my brain functioning at peak efficiency.

Sorry I couldn't be of any more assistance, but rest assured that there is a solution, it's just a question of finding it. I think I might have to do a bit of Googling tomorrow.[/quote]

Haha, here it's 4:22 ;-)

I'm googling away here, nothing yet.

[eluser]TheFuzzy0ne[/eluser]
Oh, and one more thing. You may be able to tell me where the bottlenecks are. Try using CodeIgniter's Benchmark class, and time the loops in your code. You'll instantly be able to get an idea of what's taking the longest, and that'll be where you need to optimise.

[eluser]TheFuzzy0ne[/eluser]
Can you confirm that a) all of your feeds use the same format, and b) offerid is unique. I'm thinking of using this as a sorting key.

[eluser]codex[/eluser]
[quote author="TheFuzzy0ne" date="1234689975"]Oh, and one more thing. You may be able to tell me where the bottlenecks are. Try using CodeIgniter's Benchmark class, and time the loops in your code. You'll instantly be able to get an idea of what's taking the longest, and that'll be where you need to optimise.[/quote]

Will try that. But do you think doing an 'in_array()' on a 6000 items array could be a bottleneck?

[eluser]codex[/eluser]
[quote author="TheFuzzy0ne" date="1234690988"]Can you confirm that a) all of your feeds use the same format, and b) offerid is unique. I'm thinking of using this as a sorting key.[/quote]

a) no, merchants are not reliable in delivering correct feeds
b) no, offer id's are not unique

Again, the file is not the problem (I think). It inserts fine into the temp table.

[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.