Hi David
I have been trying to parse a large csv file, 13000 lines and 19 fields. the code below is bits from the forum I have adapted and works fine apart from timing out after 19 minutes, it is updating and adding records right until it times out. have you any ideas why it is very slow?
I have checked the csv file and all is ok, and is not from a url but sent via push to my server which I access from the path in the code.
for info
the 'company' part of the update/insert is due to having feeds from a couple of suppliers in the same DB table this is so they can be uniquley updated & the .jpg variable is because the part number is the image name plus .jpg
your help is appreciated
amanda
<?php
set_time_limit(0);
require ("MagicParser.php");
//database connection
$conn = mysql_connect("localhost", "user", "pass");
mysql_select_db("database", $conn);
$link = "./folder/product.csv";
//variables
$jpg = ".jpg";
//set delete me to only delete old stock
$sql = "UPDATE cubecart_inventory SET Delete_Me = '1' WHERE Company_name = 'company'";
mysql_query($sql);
function myRecordHandler($record) {
global $jpg;
$sql = "SELECT productId FROM cubecart_inventory WHERE productCode = '".mysql_escape_string($record["midw_part_no"])."'";
$result = mysql_query($sql);
if (mysql_num_rows($result)) {
$sql = "UPDATE cubecart_inventory SET
stock_level = '".mysql_escape_string($record["stock"])."',
Delete_Me = '".mysql_escape_string("0")."'
WHERE productCode = '".mysql_escape_string($record["midw_part_no"])."'";
} else {
$sql = "INSERT INTO cubecart_inventory SET
Company_name = '".mysql_escape_string("company")."',
productCode = '".mysql_escape_string($record["midw_part_no"])."',
price = '".mysql_escape_string($record["cost"])."',
stock_level = '".mysql_escape_string($record["stock"])."',
name = '".mysql_escape_string($record["product_name"])."',
prodWeight = '".mysql_escape_string($record["weight"])."',
eanupcCode = '".mysql_escape_string($record["barcode"])."',
image = '".mysql_escape_string($record["midw_part_no"])."$jpg',
description = '".mysql_escape_string($record["long_desc"])."',
cat_id = '".mysql_escape_string($record["category"])."',
Delete_Me = '".mysql_escape_string("0")."'
";
}
mysql_query($sql);
}
MagicParser_parse($link, "myRecordHandler", "csv|44|1|0");
//delete products missing from feed
$sql = "DELETE FROM cubecart_inventory WHERE Delete_Me = '1'AND Company_name = 'company'";
mysql_query($sql);
mysql_close($conn);
?>
Hi david
it is a 500 error internal server error.
Hi Amanda,
It would still be worth trying with the output generating modification; but another idea I forget to mention previously is that it could be the process being killed by the server for taking up too many resources.
Something that often works for Price Tapestry users (my other script specifically for working with affiliate product feeds) is to insert a 1 second sleep every 100 products or so.
To do this, based on the modification described above simply add:
sleep();...immediately after the call to flush().
Hope this helps!
Cheers,
David.
Hi david
with the code added and the mysql query uncommented as in the code above each 100 records is taking 40 to 50 seconds to complete. what sort of time would you expect?
thanks
Hi Amanda,
That sounds quite long for 100 inserts.
Did you try everything commented out and no sleep() - how long does that take? (i.e. the raw parse time)
How large (how many rows) is the table?
Cheers,
David.
David
Apologies!dodgy clock, its 10 seconds for 100 records with everything live and 3 seconds with the queries commented out.
the sleep is set sleep(1); rather than () in your code as it produced an error.
That sounds more like it, so by quick calculation it should take around 21 minutes - which is close to what it is actually taking - so I'm wondering if it is actually completing - do you have any way to check this easily?
Another test may be to remove the DELETE operation (just comment out the last mysql_query() line) as that can be very slow on very large tables...
Cheers,
David.
David
with the sleep(1); it is now completing the update/insert.
many thanks for the help really is appreciated.
Amanda
Hi Amanda,
It's unusual to have a server configured timeout at around 19 minutes - do you actually get a PHP timeout or does the connection just drop? (i.e. blank screen?)
The first thing I would do is to run your script without any of the database activity (simply comment out each of your mysql_query() lines). Then run the script; which will give a good indication of the ratio or parse to database activity going on - 13000 lines should parse quite quickly on its own.
Next, I would make sure that the script is generating and flush()'ing output every so often; as the timeout problem may be part of the network infrastructure (e.g. a proxy server at your ISP) that is closing the connection rather than your server.
To do this; at the top of your record handler function; add a line to generate some output every, say, 100 records; for example:
function myRecordHandler($record) {global $counter;
$counter++;
if (!($counter % 100))
{
print $counter."\n";
flush();
}
...
If you also add at the very top of your script:
header("Content-Type: text/plain");...it will make it easier on the eye when viewing the process in your browser...
Hope this helps!
All the best,
David.