SQL query via Medoo taking too long to finish

Question

My script allows you to upload a zip file and then inserts the individual file information into a database using Medoo, after extracting them. My script takes way too long to finish, even after I've set the max execution time to 5 minutes I get the notice saying the max execution time has been exceeded.

There are only about 650 files in the zips that will be upload-able and the script only manages to extract and insert about half in to the DB before it times out. Is this query more memory intensive than I realize?

EDIT: I should mention that it only hangs with zip files with a larger amount of files, like the 650 figure I mentioned above, the program seems to execute fine with a small number of files.

Code (Offending query near bottom of script):

<?php

    ini_set('max_execution_time', 300);
    require_once 'vendor/medoo.min.php';
    require_once 'scripts/class.file.php';

    $database = new medoo([
        'database_type' => 'mysql',
        'database_name' => 'invoice_files',
        'server' => 'localhost',
        'username' => 'root',
        'password' => 'pass',
        'charset' => 'utf8'
    ]);

    $file = new File();
    $file->set("filename", $_FILES['uploaded-file']['name']);
    $file->set("category", "Invoice Statement");
    $file->set("file_temp_path", $_FILES["uploaded-file"]["tmp_name"]);
    $file->set("uploadedFilePath", $file->path("uploads/") . basename($file->get("filename")));
    $counter = 0;

    if($file->getPathInfo()["extension"] == "zip")
    {
        $zip = new ZipArchive;
        $zipFile = $file;
        echo "Source: " . $zipFile->get("file_temp_path") . "<br>";
        if($zip->open($zipFile->get("file_temp_path")))
        {
            for($i = 0; $i < $zip->numFiles; $i++)
            {
                $zipName = $zip->getNameIndex($i);
                $zipFile->set("uploadedFilePath", $file->path("uploads/"));
                $zipFile->set("filename", $zipName);

                for($x = 0; $x < $zip->numFiles; $x++)
                {
                    $extension = $zip->getNameIndex($x);
                    $pathInfo = pathinfo($extension);
                    if($pathInfo["extension"] != "pdf" && $pathInfo["extension"] != "xls")
                    {
                        echo "Non PDF or excel sheet detected<br>";
                        return false;
                    }
                    if($pathInfo["extension"] == "xls")
                    {
                        $excelFile = $extension;
                        $excelFlag = true;
                    }
                    else
                    {
                        $excelFlag = false;
                    }
                }

                if($zip->extractTo($zipFile->get("uploadedFilePath")))
                {
                    $pathInfo = pathinfo($zipName);
                    $database->insert('files',[
                            'name' => $zipFile->get("filename"),
                            'category' => $zipFile->get("category"),
                            'date' => $zipFile->setDate(),
                            'extension' => $pathInfo["extension"],
                            'size' => filesize($zipFile->get("uploadedFilePath") . $zipFile->get("filename")) / 1000 . 'KB',
                            'path' => $zipFile->get("uploadedFilePath") . $zipFile->get("filename")
                    ]);
                }
                else
                {
                    echo "Failure to extract<br>";
                }
            }
        }
        if($excelFlag)
        {
            $url = "insert-new-clients.php?excelfile=" . urlencode($excelFile);
            //header("location:$url");
        }
    }
    else
    {
        echo "File not in zip format";
        return false;
    }

?>

score 2 · Accepted Answer · answered Aug 07 '15 at 14:10

I figured it out. I realized that $zip->extractTo($zipFile->get("uploadedFilePath")) was attempting to extract 650 files for each iteration of the loop, which is 650 times.

I just moved the extraction code outside the loop and the script executed quickly.

SQL query via Medoo taking too long to finish

1 Answers1