Posting in the Magento forums has been disabled pending the implementation of a new and improved forum solution which should better serve the community.

For new questions please post at magento.stackexchange.com, the community-run support site for the Magento community. We will be providing updates on the new forum solution soon. For questions or concerns please email community@magento.com.

Magento Forum

Poll
Do you think the product import in Magento is too slow?
Yes, it should be improved! 613
No, it’s fine. There are more important things to work on. 19
What are you talking about? 1
Total Votes: 633
You must be a logged-in member to vote
Import Speed / Performance optimization
 
saikku
Jr. Member
 
Total Posts:  11
Joined:  2008-09-20
 

I didn’t read this topic but if you have perf problems to write into database, use :

# see the innodb config docs, the other options are not always safe
innodb_flush_log_at_trx_commit 2

# O_DIRECT to avoid the OS to cache what innodb is already caching
innodb_flush_method=O_DIRECT

innodb_lock_wait_timeout 
50
into your my.cnf.

you mays loose about 1 second of data writing at system crash, but your writings process will be much faster !

 
Magento Community Magento Community
Magento Community
Magento Community
 
dweeves
Enthusiast
 
Total Posts:  877
Joined:  2010-06-26
FRANCE
 

in fact, the problem is really not database performace but magento way to do “mass” imports.
they’re recreating a full product object model for each product added , plus calling event handlers etc…
this is a safe way to do things but very unoptimized approach when dealing with 1000’s of instances.
my database receives very few request per second with the dataflow importer.

my script can handle up to several thousands records per minute which is several orders of magnitude faster than the current import dataflow, even using very optimized database settings.

 
Magento Community Magento Community
Magento Community
Magento Community
 
pastanislas
Member
 
Total Posts:  46
Joined:  2008-07-29
 

Woooohooo, Dweeves your script is pure power ! Thank’s a lot man !
I have just give it a quick shot (10 minutes) and I’m already convinced that your script will be the one !
To answer your previous question think you should create a separate subject for your script.

Here is my first feedback :
- PDO need to be compiled with php, your script will not work if pdo is loaded as an extension because of the use of PDO::ATTR_DEFAULT_FETCH_MODE constant ( but I might be the only one so stupid to have it as an extension)
- You should provide a csv sample for users within your package
- It would be cool if we can define csv separator and enclosure in the .ini file (function fgetcsv)
-using the reset parameter gave me an php fatal error:

PHP Fatal error:  Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active.  Consider using PDOStatement::fetchAll().  Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.' in /var/www/peyrabon/magento/magento_mass_importer.class.php:71
-The speed is simply AMAZING I have loaded 1000 product in less than 10 seconds, your script report 10669 rec/mn !

Congratulation for your work , keep going ! Thank very much man !
 
Magento Community Magento Community
Magento Community
Magento Community
 
dweeves
Enthusiast
 
Total Posts:  877
Joined:  2010-06-26
FRANCE
 

thanks for the report pastanislas.

for PDO not compiled with php,in fact it depends on PHP version.
PHP 5.3 comes with PDO integrated while 5.2 needs extension (it caused me some problems to have PDO working when i migrated my ubuntu to 10.04 which was passing PHP from 5.2 to 5.3)

i’ll add the options you suggest in .ini and would be interested to get your patched code (especially for using pdo as extension)
i’ll also create a post

btw,did you find any problems in your magento after import ?

 
Magento Community Magento Community
Magento Community
Magento Community
 
pastanislas
Member
 
Total Posts:  46
Joined:  2008-07-29
 

Sorry but I did not found a solution with pdo loaded as an extension, I have recompiled php 5.2.13 with

--enable-pdo --with-pdo-mysql .

I’m still testing, but there not seem to be any trouble in magento, all my products are loaded correctly, all attributes of any kind (text, dropdown...) seems OK.

I’ve got an encoding problem for some typo but I know this come from my csv encoding.

Some questions :
-Are you planning to have your script support multiple store / websites ? I’ve tried to use comma separated values in “websites” and “store” fields with no success

ARFFFFF JUST FOUND ONE BUG (but maybe it’s not from your script)

When I edit a product and want to add an UPSELL PRODUCT I’ve got the following error:

SQLSTATE[23000]Integrity constraint violation1452 Cannot add or update a child rowa foreign key constraint fails (`magento`.`catalog_product_link`, CONSTRAINT `FK_PRODUCT_LINK_TYPEFOREIGN KEY (`link_type_id`) REFERENCES `catalog_product_link_type` (`link_type_id`) ON DELETE CASCADE ON UPDATE CASCADE)
 
Magento Community Magento Community
Magento Community
Magento Community
 
dweeves
Enthusiast
 
Total Posts:  877
Joined:  2010-06-26
FRANCE
 

Hi all,

created a New topic for my script
with the latest version that fixes the bugs found by pastanislas

All further discussions for my script should be done on that topic

 
Magento Community Magento Community
Magento Community
Magento Community
 
Netismine
Sr. Member
 
Avatar
Total Posts:  174
Joined:  2008-10-28
 

Did anyone else had problems with setSpecialToDate setting empty values to product in Mag 1.4+ ? setSpecialFromDate works ok, but the first one sometimes sets empty values

 
Magento Community Magento Community
Magento Community
Magento Community
 
craigcriv
Sr. Member
 
Total Posts:  85
Joined:  2009-08-05
 

Please help with Mage.php, after installation can\’t seem to get this to run.

I just need it to update inventory and am using the code from here http://www.magentocommerce.com/wiki/3_-_store_setup_and_management/import_export/stock_level_import_script

I inserted my database and path info.
Path to Mage.php file
require_once \’/var/www/html/wildbirdnaturestore.com/app/Mage.php\’
Path to csv
$path = \’/var/www/html/wildbirdnaturestore.com/import/WebStockInput.csv\’

I also set up the temp table.

I installed the Mage.php file in my magento root folder.

When I try to run the file from my browser at www.wildbirdnaturestore.com/Mage.php

I get this error
Parse error: syntax error, unexpected T_DNUMBER in /homepages/41/d293336770/htdocs/magento/Mage.php on line 5
error when Mage.php is in root directory not in magento dir

Please Help
Craig

 
Magento Community Magento Community
Magento Community
Magento Community
 
Marius-H
Jr. Member
 
Total Posts:  3
Joined:  2009-10-09
 

My stock list contains lots of products that are not in the webshop. Will that cause any problems? Or will it just jump to the next product.

 
Magento Community Magento Community
Magento Community
Magento Community
 
Marius-H
Jr. Member
 
Total Posts:  3
Joined:  2009-10-09
 

Im using the first version of the script because it has all the functionality i need. But i get this error.

Parse error: syntax error, unexpected ‘}’ in /home/borselars/domains/borselars.no/public_html/import_script.php on line 74

 
Magento Community Magento Community
Magento Community
Magento Community
 
kimsmarkin
Jr. Member
 
Total Posts:  3
Joined:  2010-09-01
 

I recently installed Magento my imported products, the regulations Analytics, paypal, customized layouts, etc. Everything went pretty smooth. Love Magento! But I would like some 1400 items from the database and I need to update the quantity of data every 2 hours. Do not worry, I wrote a little script that will download the information on my software inventory control and create a special import Magento file.

 
Magento Community Magento Community
Magento Community
Magento Community
 
Brindehosperr
Jr. Member
 
Total Posts:  1
Joined:  2010-09-04
usa
 

When I read your entire comments then I really impress with your site.There are good information you share here . Thanks for sharing information.

Image Attachments
Good Images.jpg
 
Magento Community Magento Community
Magento Community
Magento Community
 
acastanon
Jr. Member
 
Total Posts:  2
Joined:  2008-06-17
 

Hello,

michael_g08 (and all), thanks for your usesful help.

On a hosting (cheap but slow), the script runs OK.

Now I have moove my magento store to a dedicated server on 1&1;, and.... only problems. The last is:

PHP Fatal error: Uncaught exception \’Zend_Db_Statement_Exception\’ with message \’SQLSTATE[28000]: Invalid authorization specification: 1045 Access denied for user \’sku\’@\’%\’ (using password: YES)\’ in /var/www/vhosts/velozze.com/httpdocs/store/lib/Zend/Db/Statement/Pdo.php:234\\nStack trace:\\n#0 /var/www/vhosts/velozze.com/httpdocs/store/lib/Zend/Db/Statement.php(300): Zend_Db_Statement_Pdo->_execute(Array)\\n#1 /var/www/vhosts/velozze.com/httpdocs/store/lib/Zend/Db/Adapter/Abstract.php(468): Zend_Db_Statement->execute(Array)\\n#2 /var/www/vhosts/velozze.com/httpdocs/store/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query(\’LOAD DATA INFIL...\’, Array)\\n#3 /var/www/vhosts/velozze.com/httpdocs/import/script.php(68): Zend_Db_Adapter_Pdo_Abstract->query(\’LOAD DATA INFIL...\’)\\n#4 /var/www/vhosts/velozze.com/httpdocs/import/script.php(20): updateTempTableFromFile(Object(Zend_Db_Adapter_Pdo_Mysql), \’/var/lib/mysql/...\’)\\n#5 {main}\\n thrown in /var/www/vhosts/velozze.com/httpdocs/store/lib/Zend/Db/Statement/Pdo.php on line 234

I have 2 db users, and get the same error. Looks like a problem with permissions.... Any has got the same error, or have any idea for the solution?

The script I use:

<?php
////////////////GLOBAL DEFINITIONS/////////////////////
//DATABASE SETTINGS 
$dbConfig = array(
    
'host'      => 'localhost',
    
'username'  => 'sku',
    
'password'  => 'XXXXXXX',
    
'dbname'    => 'XXXXXXX',
    
'driver_options'=> array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES UTF8'),
    
‘driver_options’=> array(PDO::MYSQL_ATTR_LOCAL_INFILE => true)
);

///// Path to Your CSV file
$path 'WebStockInput.csv';

// Path to Mage.php  
require_once '../store/app/Mage.php';
// Connect to the databases
$db_magento Zend_Db::factory('Pdo_Mysql'$dbConfig);

updateTempTableFromFile($db_magento$path);    
    
$query $db_magento->select()->from('temp_table');
$contents_array $db_magento->fetchAll($query);

echo 
"<table>";
  
// Run for each line
foreach ($contents_array as $line{
    
    $sku 
$line['sku'];
    
$qty $line['qty'];
            
    
$exists $db_magento->query("SELECT COUNT(sku) cnt FROM catalog_product_entity WHERE sku = '$sku' LIMIT 1");
    
$find_product = (($exists->fetchObject()->cnt) > 0) ? true false;

    if (
$find_product == true
        $entity_id 
getEntityID_bySKU($db_magento$sku);   
        
updateQTY ($db_magento$entity_id$qty); 
        echo 
"</tr><td>$sku</td><td>$qty</td></tr>";
    
}    
}

echo "</table>";

function 
getEntityID_bySKU($db_magento$sku{
    $entity_row 
$db_magento->query("SELECT entity_id FROM catalog_product_entity p_e WHERE p_e.sku = '$sku'")->fetchObject();
    
$entity_id  $entity_row->entity_id;
    return 
$entity_id;
}


function updateQTY($db_magento$entity_id$qty{
     $db_magento
->query("UPDATE cataloginventory_stock_item s_i, cataloginventory_stock_status s_s 
         SET   s_i.qty = '
$qty', s_i.is_in_stock = IF('$qty'>0, 1,0), 
               s_s.qty = '
$qty', s_s.stock_status = IF('$qty'>0, 1,0)
         WHERE s_i.product_id = '
$entity_id' AND s_i.product_id = s_s.product_id ");
}

function updateTempTableFromFile($db_magento$path){
    $db_magento
->query("CREATE TEMPORARY TABLE IF NOT EXISTS temp_table (sku CHAR(20), qty INT(32))");  
    
    
$db_magento->query("TRUNCATE TABLE temp_table");
  
    
$db_magento->query("LOAD DATA INFILE '$path'
                      INTO TABLE temp_table
                      FIELDS TERMINATED BY ','
                      LINES TERMINATED BY 'n'
                      (`sku`, `qty`)"
);
}

function getAttributeEAV_id ($db_magento$attribute_name)
{
    $result 
$db_magento->query("SELECT attribute_id FROM eav_attribute WHERE entity_type_id = 4 AND attribute_code = '$attribute_name'")->fetchObject()->attribute_id;
    return 
$result;
}

Regars from Spain,

Alex

 
Magento Community Magento Community
Magento Community
Magento Community
 
coldlamper
Member
 
Total Posts:  35
Joined:  2009-02-01
NC, USA
 

I have spent weeks discovering a huge memory leak in magento.  It duplicates an array of products over and over.  So for example you tried to import 1000 products you would have an array containing 1000 products 1000 times.  It eats up memory but also a lot of cpu parsing the info. I have cut down my script import time dramatically. There are other memory leaks that deal with object clean up that haven’t been able to pin-point but have a method to fix that too.  The fix is only 1 line of code.  If you are willing to compensate me I will give you the fix.  I usually would just post it but I really had to spend a lot of time and compile php into debug mode and run it thru valgrind and then use a php debugger.

If you import more than 100 products this fix really helps and it speeds up regular magento operation as well.  I contacted magento about this and they did not reply.

message me or email if interested

brian@ewtechnologies.com

I also sell a couple of modules at http://ewtechnologies.com/store/magento

 
Magento Community Magento Community
Magento Community
Magento Community
 
Roberts Distributors
Jr. Member
 
Total Posts:  2
Joined:  2010-09-16
 

Hello all!

I’m tearing my hair out over here. Like everyone else, I need a faster way to update stock quantity, but I can’t seem to get michael_g08’s wonderful little script working. I finally got the database permissions set, and everything runs up to loading data into the temp table, but I can’t seem to get it to copy into the active table. I’m running Enterprise, 1.9, and I’m wondering if maybe there’s something different in the DB for that that would be causing the problem? Any help or ideas anyone has would be wonderful, and I can provide any information I need to to help clarify my problem, I’m just pretty new to this and don’t know what I need to offer up front.

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top