CODEWITHSUNDEEP
phpmysql
liu jin long
liu jin long

Reputation: 69

PHP Maximum execution time of 30 seconds exceeded on a 100k row query

Is it possible to write a php code that will fetch a serialize data, unserialize it and write contents on a new database schema with NO

ini_set("max_execution_time", 0);

I think I already optimize my codes but that really cannot be avoided.

I use the following code with iteration. 

$queryCount ="SELECT count(*) FROM APPLICATION where PRO_UID='$PRO_UID' 
                AND APP_STATUS != 'DRAFT' 
                AND APP_STATUS != 'CANCELLED'";
    $resultCount = $mysqli->query($queryCount);
    $count = (mysqli_fetch_array($resultCount));

    for($x=0;$x < $count; $x += 1000){


        $queryData = "SELECT * FROM wf_workflow.APPLICATION 
                        where PRO_UID='$PRO_UID' 
                        AND APP_STATUS != 'CANCELLED' 
                        AND APP_STATUS != 'DRAFT' 
                        not in (SELECT APP_NUMBER FROM export_workflow.CARDS_CONTACT_DETAILS_LOOKUP) LIMIT 2000";


        $resultData = $mysqli->query($queryData);

        while($row = mysqli_fetch_array($resultData)){

            $data = unserialize($row['APP_DATA']);
            $APP_NUMBER = $row['APP_NUMBER'];
            $PERSON_NAME = $data["PersonalInformation"][1]["FirstName"] . " " . $data["PersonalInformation"][1]["MiddleName"] ." " . $data["PersonalInformation"][1]["LastName"];
            $MOBILE = $data["ContactDetails"][1]["MobileNo"];
            $OFFICE_PHONE = $data["EmploymentInformation"][1]["OfficeTelNo"];
            $HOME_PHONE = $data["ContactDetails"][1]["TelephoneNo1"];

            $sql = "INSERT INTO CARDS_CONTACT_DETAILS_LOOKUP (APP_NUMBER , HOME_PHONE , MOBILE , OFFICE_PHONE, PERSON_NAME)
                    VALUES ('$APP_NUMBER' , '$HOME_PHONE' , '$MOBILE' , '$OFFICE_PHONE' , '$PERSON_NAME')";

            if (mysqli_query($mysqli, $sql)) {
                echo "New record created successfully FROM dev";
            } else {
                echo "Error: " . $sql . "<br>" . mysqli_error($mysqli);
            }

        }

    echo "finished the dev";

    }

Upvotes: 0

Views: 811

Answers (1)

Kickstart
Kickstart

Reputation: 21513

The first query appears to just get a count that is used for the for loop. But the for loop does not appear to be required.

The main query is using AND APP_STATUS != 'DRAFT' not in (SELECT which isn't checking a value / column is in the the result set of the sub query. Suspect this will just check if true or false (the result of APP_STATUS != 'DRAFT' ) is a returned value from the sub query. Looking at your code I think you meant to have AND APP_STATUS != 'DRAFT' AND APP_NUMBER not in (SELECT . However NOT IN can perform quite badly so it might be better to recode it as a join.

Taking this in to account, something like this (not tested):-

<?php

    $queryData = "SELECT a.APP_NUMBER, a.APP_DATA 
                    FROM wf_workflow.APPLICATION a
                    LEFT OUTER JOIN export_workflow.CARDS_CONTACT_DETAILS_LOOKUP b
                    ON a.APP_NUMBER = b.APP_NUMBER
                    WHERE a.PRO_UID='$PRO_UID' 
                    AND a.APP_STATUS != 'CANCELLED' 
                    AND a.APP_STATUS != 'DRAFT' 
                    AND b.APP_NUMBER IS NULL";


    $resultData = $mysqli->query($queryData);

    while($row = mysqli_fetch_array($resultData))
    {

        $data = unserialize($row['APP_DATA']);
        $APP_NUMBER = $row['APP_NUMBER'];
        $PERSON_NAME = $data["PersonalInformation"][1]["FirstName"] . " " . $data["PersonalInformation"][1]["MiddleName"] ." " . $data["PersonalInformation"][1]["LastName"];
        $MOBILE = $data["ContactDetails"][1]["MobileNo"];
        $OFFICE_PHONE = $data["EmploymentInformation"][1]["OfficeTelNo"];
        $HOME_PHONE = $data["ContactDetails"][1]["TelephoneNo1"];

        $sql = "INSERT INTO CARDS_CONTACT_DETAILS_LOOKUP (APP_NUMBER , HOME_PHONE , MOBILE , OFFICE_PHONE, PERSON_NAME)
                VALUES ('$APP_NUMBER' , '$HOME_PHONE' , '$MOBILE' , '$OFFICE_PHONE' , '$PERSON_NAME')";

        if (mysqli_query($mysqli, $sql)) 
        {
            echo "New record created successfully FROM dev";
        } 
        else 
        {
            echo "Error: " . $sql . "<br>" . mysqli_error($mysqli);
        }
    }

    echo "finished the dev";

You could also probably improve the performance of the script by batching up the INSERTs (ie, insert say 100 rows at a time). This probably won't make a big difference to the script timing out (time spent waiting for MySQL shouldn't affect the scripts time limit) but might speed it up for you. Something like this:-

$queryData = "SELECT a.APP_NUMBER, a.APP_DATA 
                FROM wf_workflow.APPLICATION a
                LEFT OUTER JOIN export_workflow.CARDS_CONTACT_DETAILS_LOOKUP b
                ON a.APP_NUMBER = b.APP_NUMBER
                WHERE a.PRO_UID='$PRO_UID' 
                AND a.APP_STATUS != 'CANCELLED' 
                AND a.APP_STATUS != 'DRAFT' 
                AND b.APP_NUMBER IS NULL";


$resultData = $mysqli->query($queryData);

$inserts = array();

while($row = mysqli_fetch_array($resultData))
{

    $data = unserialize($row['APP_DATA']);
    $APP_NUMBER = $row['APP_NUMBER'];
    $PERSON_NAME = $data["PersonalInformation"][1]["FirstName"] . " " . $data["PersonalInformation"][1]["MiddleName"] ." " . $data["PersonalInformation"][1]["LastName"];
    $MOBILE = $data["ContactDetails"][1]["MobileNo"];
    $OFFICE_PHONE = $data["EmploymentInformation"][1]["OfficeTelNo"];
    $HOME_PHONE = $data["ContactDetails"][1]["TelephoneNo1"];

    $inserts[] =  "'$APP_NUMBER' , '$HOME_PHONE' , '$MOBILE' , '$OFFICE_PHONE' , '$PERSON_NAME')";

    if (count($inserts) > 100)
    {
        $sql = "INSERT INTO CARDS_CONTACT_DETAILS_LOOKUP (APP_NUMBER , HOME_PHONE , MOBILE , OFFICE_PHONE, PERSON_NAME)
                VALUES ".implode(', ', $inserts);
        $inserts = array();
        if (mysqli_query($mysqli, $sql)) 
        {
            echo "New records created successfully FROM dev";
        } 
        else 
        {
            echo "Error: " . $sql . "<br>" . mysqli_error($mysqli);
        }
    }
}

if (count($inserts) > 0)
{
    $sql = "INSERT INTO CARDS_CONTACT_DETAILS_LOOKUP (APP_NUMBER , HOME_PHONE , MOBILE , OFFICE_PHONE, PERSON_NAME)
            VALUES ".implode(', ', $inserts);
    $inserts = array();
    if (mysqli_query($mysqli, $sql)) 
    {
        echo "New records created successfully FROM dev";
    } 
    else 
    {
        echo "Error: " . $sql . "<br>" . mysqli_error($mysqli);
    }
}

echo "finished the dev";

You might be well advised to escape your data though!

Upvotes: 1

Related Questions