server error executing a large file

Question

I have created a script which reads an XML file and adds it to the database. I am using XML Reader for this. The problem is that my XML contains 500,000 products in it. This causes my page to time out. is there a way for me to achieve this?

My code below:

$z = new XMLReader;
$z->open('files/NAGardnersEBook.xml');

$doc = new DOMDocument;

# move to the first node
while ($z->read() && $z->name !== 'EBook');

# now that we're at the right depth, hop to the next <product/> until the end of the tree
while ($z->name === 'EBook')
{

    $node = simplexml_import_dom($doc->importNode($z->expand(), true));

    # Get the value of each node
    $title = mysql_real_escape_string($node->Title);
    $Subtitle = mysql_real_escape_string($node->SubTitle);
    $ShortDescription = mysql_real_escape_string($node->ShortDescription);
    $Publisher = mysql_real_escape_string($node->Publisher);
    $Imprint = mysql_real_escape_string($node->Imprint);

    # Get attributes
    $isbn = $z->getAttribute('EAN');

    $contributor = $node->Contributors;
    $author = $contributor[0]->Contributor;
    $author = mysql_real_escape_string($author);

    $BicSubjects = $node->BicSubjects;
    $Bic = $BicSubjects[0]->Bic;

    $bicCode = $Bic[0]['Code'];

    $formats = $node->Formats;
    $type  = $formats[0]->Format;
    $price = $type[0]['Price'];
    $ExclusiveRights = $type[0]['ExclusiveRights'];
    $NotForSale = $type[0]['NotForSale'];


    $arr[] = "UPDATE onix_d2c_data SET is_gardner='Yes', TitleText = '".$title."', Subtitle = '".$Subtitle."', PersonName='".$author."', ImprintName = '".$Imprint."', PublisherName = '".$Publisher."', Text = '".$ShortDescription."', BICMainSubject = '".$bicCode."', ExcludedTerritory='".$NotForSale."', RightsCountry='".$ExclusiveRights."', PriceAmount='".$price."', custom_category= 'Uncategorised', drm_type='adobe_drm' WHERE id='".$isbn."' ";

    # go to next <product />

    $z->next('EBook');
    $isbns[] = $isbn;
}


foreach($isbns as $isbn){

    $sql = "SELECT * FROM onix_d2c_data WHERE id='".$isbn."'";

    $query = mysql_query($sql);

    $count = mysql_num_rows($query);
    if($count >0){

    } else{
        $sql = "INSERT INTO onix_d2c_data (id) VALUES ('".$isbn."')";               
        $query = mysql_query($sql);
    }

}



foreach($arr as $sql){
    mysql_query($sql);
}

Thank you,

Julian

score 1 · Answer 1 · answered Aug 07 '13 at 08:51

1

You could use the function set_time_limit to extend the allowed script execution time or set max_execution_time in your php.ini.

answered Aug 07 '13 at 08:51

ciruvan

5,143
1
26
32

Shankar Narayana Damodaran · Answer 2 · 2013-08-07T09:05:48.497

0

Have you tried adding set_time_limit(0); on top of your PHP file ?

EDIT :

ini_set('memory_limit','16M');

Specify your limit there.

edited Aug 07 '13 at 09:05

answered Aug 07 '13 at 08:51

Shankar Narayana Damodaran

68,075
43
96
126

Yes, it did not work. I think it is due to running out of memory. My guess would be to split the array into multiple chunks? Not sure how to go about it – Julian Aug 07 '13 at 09:03
@Julian, then you need to increase memory limit. Check my edit. – Shankar Narayana Damodaran Aug 07 '13 at 09:05
No - OP is assembling queries into an array before executing them sequentially. If he executes each query as he constructs it then memory shouldn't be an issue. – Aug 07 '13 at 09:11

score 0 · Answer 3 · answered Aug 07 '13 at 08:52

0

You need to set these vaiables.Mare sure you have permission to change them

set_time_limit(0);
ini_set('max_execution_time', '6000');

answered Aug 07 '13 at 08:52

Garry

595
4
19

Leo · Answer 4 · 2013-08-07T09:07:16.203

0

if you don't want to change the max_execution time as proposed by others, then you could also split up your tasks into several smaller tasks and let the server run a cron-job in several intervals.

E.g. 10.000 products each minute

edited Aug 07 '13 at 09:07

answered Aug 07 '13 at 08:58

Leo

101
3

score 0 · Answer 5 · answered Aug 07 '13 at 09:02

You're executing two queries for each ISBN, just to check whether the ISBN already exists. Instead, set the ISBN column to unique (if it isn't already, it should be) then just go ahead and insert without checking. MySQL will return an error if it detects a duplicate which you can handle. This will reduce the number of queries and improve performance.
You're inserting each title with a separate call to the database. Instead, use the extended INSERT syntax to batch up many inserts in one query - see the MySQL manual for the ful syntax. Batching, say, 250 inserts will save a lot of time.
If you're not happy with batching inserts, use mysqli prepared statements which will reduce parsing time and and transmission time, so should improve your overall performance
You can probably trust Gardners list - consider dropping some of the escaping you're doing. I wouldn't recommend this for user input normally, but this is a special case.

score 0 · Answer 6 · answered Aug 07 '13 at 14:46

Thank you all for such fast feedback. I managed to get the problem sorted by using array_chunks. Example below:

$thumbListLocal = array_chunk($isbns, 4, preserve_keys);
$thumbListLocalCount = count($thumbListLocal);


while ($i <= $thumbListLocalCount):
    foreach($thumbListLocal[$i] as $index => $thumbName):
        $sqlConstruct[] = "INSERT IGNORE INTO onix_d2c_data (id) VALUES ('".$thumbName."')";

    endforeach;
    foreach($sqlConstruct as $processSql){
        mysql_query($processSql);
    }
    unset($thumbListLocal[$i]);
    $i++;
endwhile;

I hope this helps someone.

Julian

server error executing a large file

6 Answers6