hi
I have been using import.php via cron for some time now without any problems, however today I tried to access our site while the program was importing data, the site seemed to lock up and then after awhile work again, I monitored the importing via ssh and found a pattern, the site would not load when the import started a new feed, but as soon as it showed products being imported the site went back to normal. I assume the time from when the feed shows up to the time it starts to import products is when the script deletes the old products from the database before inserting the new ones, when there are only a couple of hundred this is just a second or so, however when importing a large amount of products this can be several minutes, at which time the site is inaccessible.
Does anyone know where the issue may be and if the is any solution.
thanks
Brent
Hi Brent,
As you correctly identify, this will be caused by the database becoming busy with the large DELETE query to remove all products before import. This is done by the following code, starting at line 387 of includes/admin.php
$sql = "DELETE FROM `".$config_databaseTablePrefix."products` WHERE merchant='".database_safe($admin_importFeed["merchant"])."'";database_queryModify($sql,$insertId);
An alternative to this is to perform the deletion in blocks of, say, 500 products, with a 2 second sleep state in-between each DELETE. You could of course modify these parameters until you obtain the performance required - simply change the values of $delete_block and $delete_sleep. Here's the code:
$sql = "SELECT COUNT(*) AS productCount FROM `".$config_databaseTablePrefix."products` WHERE merchant='".database_safe($admin_importFeed["merchant"])."'";database_querySelect($sql,$rows);
$delete_count = $rows[0]["productCount"];
$delete_block = 500;
$delete_sleep = 2;
while($delete_count > 0)
{
$sql = "DELETE FROM `".$config_databaseTablePrefix."products` WHERE merchant='".database_safe($admin_importFeed["merchant"])."' LIMIT ".$delete_block;
database_queryModify($sql,$insertId);
sleep($delete_sleep);
$delete_count = $delete_count - $delete_block;
}
Hope this helps!
Cheers,
David.