Update/insert in chunks?

[eluser]codex[/eluser]
Ok guys, I really need your help on this one.

Let me explain:

I have a database which hold 2 tables 'products' and 'products_temp'. The idea is to get a datafeed (CSV) from a merchant (affiliate marketing), copy that to the server, read in the file and insert it into the temp table.

Then, I get all records from the products table, put that into an array, get records from temp table, compare them to the array. If temp id is found in the array it means the record exists, then check some fields to see if there are any differences. If so, the data in the temp is new and the old record needs to be updated. If not, no update is done. If the temp id is not found in the array the record does not yet exist and is inserted into the products table. We're talking about max 60.000 records.

Unfortunately the server chokes on this (it times out, I think it's has something to do with memory?). I can imagine because there's a lot going on at once.

The question is how to approach this. I read something about doing the update/insert in chunks (http://ellislab.com/forums/viewthread/104646/) but I don't know how to achieve this.

Any ideas on how to do this? Help is greatly appreciated!

[eluser]codex[/eluser]
The code. Maybe you can spot something wrong here.

Code:

function copy_temp_to_live($feed_id)
    {
        // First get data from temp
        $this->CI->db->group_by('pId'); // GROUP BY to ignore duplicates
        $query = $this->CI->db->get('products_temp');
        
        if ($query->num_rows() > 0)
        {
            // WE HEBBEN TEMP PRODUCTEN!
            // Temp products
            $res = $query->result_array();
            
            // Get products
            $this->CI->db->where('pFeedId', 11);
            $query2 = $this->CI->db->get('products');
            
            if ($query2->num_rows() > 0)
            {
                // WE HEBBEN PRODUCTEN!
                $new = $query2->result_array();
                
                // Stop in array
                foreach($new as $n) // products
                {
                    $arr[] = $n['pId'];
                }
            
                foreach($res as $r)
                {
                    if (in_array($r['pId'], $arr))
                    {
                        // The id already exists in the db
                        // Update with new data
                        if (($n['pTitle'] != $r['pTitle'])    
                                or ($n['pUrl']             != $r['pUrl'])
                                or ($n['pDescription']     != $r['pDescription'])
                                or ($n['pImgSmall']     != $r['pImgSmall'])
                                or ($n['pImgLarge']     != $r['pImgLarge'])
                                or ($n['pPrice']         != $r['pPrice'])
                                or ($n['pPriceOld']     != $r['pPriceOld'])
                                )
                        {
                            $update = array('pUrl'                 => $r['pUrl'],
                                            'pTitle'             => $r['pTitle'],
                                            'pTitleUrlSafe'     => normalize_string($r['pTitle'], '-'),
                                            'pDescription'         => $r['pDescription'],
                                            'pDescription2'     => $r['pDescription2'],
                                            'pImgSmall'         => $r['pImgSmall'],
                                            'pImgLarge'         => $r['pImgLarge'],
                                            'pPrice'             => $r['pPrice'],
                                            'pPriceOld'         => $r['pPriceOld'],
                                            'pCategory'         => $r['pCategory'],
                                            'pCategoryPath'     => $r['pCategoryPath'],
                                            'pSubCategory'        => $r['pSubCategory'],
                                            'pSubCategoryPath'     => $r['pSubCategoryPath'],
                                            'pStock'             => $r['pStock'],
                                            'pPriceShipping'    => $r['pPriceShipping'],
                                            'pTimeToShip'         => $r['pTimeToShip'],
                                            'pEAN'                 => $r['pEAN']
                                            );
                        
                            $this->CI->db->where('pId', $r['pId']);
                            $this->CI->db->update('products', $update);
                        }
                    }

[eluser]codex[/eluser]
continue:

Code:

else
                    {
                        // insert
                        $insert = array('pFeedId'             => $r['pFeedId'],
                                        'pVendor'             => $r['pVendor'],
                                        'pId'                 => $r['pId'],
                                        'pUrl'                 => $r['pUrl'],
                                        'pTitle'             => $r['pTitle'],
                                        'pTitleUrlSafe'     => normalize_string($r['pTitle'], '-'),
                                        'pDescription'         => $r['pDescription'],
                                        'pDescription2'     => $r['pDescription2'],
                                        'pImgSmall'         => $r['pImgSmall'],
                                        'pImgLarge'         => $r['pImgLarge'],
                                        'pPrice'             => $r['pPrice'],
                                        'pPriceOld'         => $r['pPriceOld'],
                                        'pCategory'         => $r['pCategory'],
                                        'pCategoryPath'     => $r['pCategoryPath'],
                                        'pSubCategory'        => $r['pSubCategory'],
                                        'pSubCategoryPath'     => $r['pSubCategoryPath'],
                                        'pStock'             => $r['pStock'],
                                        'pPriceShipping'    => $r['pPriceShipping'],
                                        'pTimeToShip'         => $r['pTimeToShip'],
                                        'pEAN'                 => $r['pEAN']
                                        );
                    
                        $this->CI->db->insert('products', $insert);
                    }
                }
            }
            else
            {
                foreach($res as $r)
                {
                    // insert
                    $insert = array('pFeedId'             => $r['pFeedId'],
                                    'pVendor'             => $r['pVendor'],
                                    'pId'                 => $r['pId'],
                                    'pUrl'                 => $r['pUrl'],
                                    'pTitle'             => $r['pTitle'],
                                    'pTitleUrlSafe'     => normalize_string($r['pTitle'], '-'),
                                    'pDescription'         => $r['pDescription'],
                                    'pDescription2'     => $r['pDescription2'],
                                    'pImgSmall'         => $r['pImgSmall'],
                                    'pImgLarge'         => $r['pImgLarge'],
                                    'pPrice'             => $r['pPrice'],
                                    'pPriceOld'         => $r['pPriceOld'],
                                    'pCategory'         => $r['pCategory'],
                                    'pCategoryPath'     => $r['pCategoryPath'],
                                    'pSubCategory'        => $r['pSubCategory'],
                                    'pSubCategoryPath'     => $r['pSubCategoryPath'],
                                    'pStock'             => $r['pStock'],
                                    'pPriceShipping'    => $r['pPriceShipping'],
                                    'pTimeToShip'         => $r['pTimeToShip'],
                                    'pEAN'                 => $r['pEAN']
                                    );
                
                    $this->CI->db->insert('products', $insert);
                }
            }
            
            // Set imported products number
            $this->CI->db->set('num_products', count($arr));
            $this->CI->db->where('id', $feed_id);
            $this->CI->db->update('datafeeds');
            
            // Clean up temp
            $this->CI->db->query('TRUNCATE TABLE products_temp');
            
            return TRUE;
        }
        else
        {
            return FALSE;
        }
    }

[eluser]TheFuzzy0ne[/eluser]
There must be an easier way. You get a feed from the merchant. Does that contain 60,000 rows?

If I understand you correctly, you are effectively comparing 2 tables to one another. If the tables are different, then it's time for an update, otherwise don't update. Is that right?

[eluser]codex[/eluser]
Thanks for your reply!

[quote author="TheFuzzy0ne" date="1234686322"]There must be an easier way. You get a feed from the merchant. Does that contain 60,000 rows?
[/quote]

Yes, there's one that large, but I have the same trouble with 6000 records.

If I understand you correctly, you are effectively comparing 2 tables to one another. If the tables are different, then it's time for an update, otherwise don't update. Is that right?

Exactly.

If youexamine the code, do you see overhead somewhere?

[eluser]TheFuzzy0ne[/eluser]
Is there any reason why you can't compare an MD5 hash of the feed to the feed that came before it? I'd imagine that would be faster, and most certainly less resource intensive. Obviously this will only work if the data is always sorted in some kind of order.

[eluser]TheFuzzy0ne[/eluser]
Is there any chance of obtaining the first few lines from a few of these feeds? If I can see the format of the feed, I might be able to suggest a better solution.

[eluser]codex[/eluser]
[quote author="TheFuzzy0ne" date="1234686643"]Is there any reason why you can't compare an MD5 hash of the feed to the feed that came before it? I'd imagine that would be faster, and most certainly less resource intensive. Obviously this will only work if the data is always sorted in some kind of order.[/quote]

Hmm, please elaborate.

[eluser]codex[/eluser]
[quote author="TheFuzzy0ne" date="1234686723"]Is there any chance of obtaining the first few lines from a few of these feeds? If I can see the format of the feed, I might be able to suggest a better solution.[/quote]

I guess I can open the file, but it's not about the file. That goes perfectly. It's the update/insert of the tables.

Lemme check if I can copy the first lines.

[eluser]codex[/eluser]
"url" "title" "description" "offerid" "image" "price" "category" "subcategory" "stock" "timetoship" "ean" "price_shipping" "price_old" "vendor" "category_path"
"http://clicks.m4n.nl/_c?aid=12778&adid=442539&turl=http://www.acmetrendz.com/disney-pixar-russell-mini-action-figure-p-12861.html?osCsid=f49ce6bc73c63f6dd350f4afd5c6c7bc&_df=true" "Disney: Pixar Up! Russell Mini Action Figure" "From the new upcoming Disney/Pixar's movie Up! comes a new toy line from Jun Planning." "12861" "http://www.acmetrendz.com/images/films/computeranimatie/up/russellminifigure_sm.jpg" "16.76" "Up!" "" "Pre-order" "" "" "" "16.76" "Jun" ""
"http://clicks.m4n.nl/_c?aid=12778&adid=442539&turl=http://www.acmetrendz.com/disney-pixar-carl-soft-vinyl-figure-p-12863.html?osCsid=f49ce6bc73c63f6dd350f4afd5c6c7bc&_df=true" "Disney: Pixar Up! Carl Soft Vinyl Figure" "From the new upcoming Disney/Pixar's movie Up! comes a new toy line from Jun Planning." "12863" "http://www.acmetrendz.com/images/films/computeranimatie/up/carlsoftvinyl_sm.jpg" "20.97" "Up!" "" "Pre-order" "" "" "" "20.97" "Jun" ""
"http://clicks.m4n.nl/_c?aid=12778&adid=442539&turl=http://www.acmetrendz.com/disney-pixar-russell-soft-vinyl-figure-p-12864.html?osCsid=f49ce6bc73c63f6dd350f4afd5c6c7bc&_df=true" "Disney: Pixar Up! Russell Soft Vinyl Figure" "From the new upcoming Disney/Pixar's movie Up! comes a new toy line from Jun Planning." "12864" "http://www.acmetrendz.com/images/films/computeranimatie/up/russellsoftvinyl_sm.jpg" "20.97" "Up!" "" "Pre-order" "" "" "" "20.97" "Jun" ""
"http://clicks.m4n.nl/_c?aid=12778&adid=442539&turl=http://www.acmetrendz.com/disney-pixar-kevin-mini-action-figure-p-12862.html?osCsid=f49ce6bc73c63f6dd350f4afd5c6c7bc&_df=true" "Disney: Pixar Up! Kevin Mini Action Figure" "From the new upcoming Disney/Pixar's movie Up! comes a new toy line from Jun Planning." "12862" "http://www.acmetrendz.com/images/films/computeranimatie/up/kevinminifigure_sm.jpg" "16.76" "Up!" "" "Pre-order" "" "" "" "16.76" "Jun" ""
"http://clicks.m4n.nl/_c?aid=12778&adid=442539&turl=http://www.acmetrendz.com/disney-pixar-soft-vinyl-figure-p-12865.html?osCsid=f49ce6bc73c63f6dd350f4afd5c6c7bc&_df=true" "Disney: Pixar Up! Dug Soft Vinyl Figure" "From the new upcoming Disney/Pixar's movie Up! comes a new toy line from Jun Planning." "12865" "http://www.acmetrendz.com/images/films/computeranimatie/up/dugsoftvinyl_sm.jpg" "20.97" "Up!" "" "Pre-order" "" "" "" "20.97" "Jun" ""