Exporting large data into text file - PHP MySQL

[shergar1983]

Hi there,

 

I have a system which has many reports exporting data. My chosen method is simple, just using fopen and then a fwrite inside a loop of the selected rows. The text is then written as tab delimited by use of \t.

 

This all works and works fine, it even compresses itself into a zip for download afterwards, the problem is the speed of the actual writing (the zip part is fine). Most often the first say 2,000 records run very quickly often a couple seconds. Gradually however it slows to almost a halt after about 15 seconds outputting just a couple of rows every second.

 

I have no idea to speed this up, can anyone help?

[wildteen88]

Well we'll need to see your code. Without seeing your code we cant suggest improvements.

[shergar1983]

Okay no problem, I've pasted the guts of the script below. This particular one returns about 18,000 results in csv format. The first 2-3000 run in about 1 second with the remainder gradually slowing down. I appreciate there are linked tables here however even against one table the issue is much the same.

 

<?set_time_limit(0);
$date=date("Y-m-d");
$filename="test.csv";
$myFile = "../Export/Reports/$filename";
$fh = fopen($myFile, 'w') or die("can't open file");

$stringData="\"Our Ref\",\"Ref2\",\"Client Type\",\"Segment\",\"Customer Name\",\"Add 1\",\"Add 2\",\"Add 3\",\"Add 4\",\"Add 5\",\"Post Code\",\"Phone available\",\"Birthday\",\"Last Actioned\",\"Balance\",\"Last Payment\",\"Mode\",\"Status\"\r\n";
fwrite($fh, $stringData);
include("../../files/library/connection.php");
$query="SELECT * FROM ids,details,adds,short_status WHERE details.name_id=ids.id AND details.address_id=adds.add_id AND short_status.short_status=details.short_status and details.birthday>='2006-01-01'";
$result=mysql_query($query);

$num=mysql_numrows($result);
$i=0;
while ($i < $num) {
$ref_1=mysql_result($result,$i,"ref_1");
$ref_1 = stripslashes($ref_1);
$ref_2=mysql_result($result,$i,"ref_2");
$client_type=mysql_result($result,$i,"client_type");
$case_type=mysql_result($result,$i,"case_type");
$add_1=mysql_result($result,$i,"add_1");
$add_2=mysql_result($result,$i,"add_2");
$add_3=mysql_result($result,$i,"add_3");
$add_4=mysql_result($result,$i,"add_4");
$add_5=mysql_result($result,$i,"add_5");
$post_code=mysql_result($result,$i,"post_code");
$phones=mysql_result($result,$i,"phones");
$fullname=mysql_result($result,$i,"fullname");
if ($phones>='1')
{$phones='Yes';}
else
{$phones='No';}
$birthday=mysql_result($result,$i,"birthday");
if ($birthday=='0000-00-00')
{$birthday='Error';}
$last_action=mysql_result($result,$i,"last_action");
if ($last_action=='0000-00-00')
{$last_action='Never';}
$balance=mysql_result($result,$i,"balance");
$balance=sprintf("%01.2f",$balance);
$last_pay_d_g=mysql_result($result,$i,"last_pay_d_g");
$mode=mysql_result($result,$i,"mode");
$short_status=mysql_result($result,$i,"desc");
$status=mysql_result($result,$i,"status");

$stringData="\"$ref_1\",\"$ref_2\",\"$client_type\",\"$case_type\",\"$fullname\",\"$add_1\",\"$add_2\",\"$add_3\",\"$add_4\",\"$add_5\",\"$post_code\",\"$phones\",\"$birthday\",\"$last_action\",\"$last_pay_d_g\",\"$mode\",\"$short_status\",\"$status\"\r\n";
fwrite($fh, $stringData);
++$i;}
fclose($fh);

[wildteen88]

mysql_result is rather old fashioned. Use mysql_fetch_assoc instead allows for much cleaner code:

<?php
set_time_limit(0);

$date     = date("Y-m-d");
$filename = 'test.csv';
$myFile   = '../Export/Reports/'.$filename;
$fh       = fopen($myFile, 'w') or die('can\'t open file');

$stringData = '"Our Ref","Ref2","Client Type","Segment","Customer Name","Add 1","Add 2","Add 3","Add 4","Add 5",'.
              '"Post Code","Phone available","Birthday","Last Actioned","Balance","Last Payment","Mode","Status"'."\r\n";

fwrite($fh, $stringData);

include '../../files/library/connection.php';

$query = 'SELECT * FROM ids, details, adds, short_status WHERE details.name_id=ids.id AND details.address_id=adds.add_id AND short_status.short_status=details.short_status and details.birthday>=\'2006-01-01\'';

$result = mysql_query($query);

while ($row = mysql_fetch_assoc($result))
{
    $row['phones']      = ($row['phones'] >= '1') ? 'Yes' : 'No';
    $row['birthday']    = ($row['birthday'] == '0000-00-00') ? 'Error' : $row['birthday'];
    $row['last_action'] = ($row['last_action'] == '0000-00-00') ? 'Never' : $row['last_action'];
    $row['balance']     = sprintf("%01.2f", $row['balance']);

    $stringData = '"' . implode('","', $row) . '"'."\r\n";
    fwrite($fh, $stringData);
}

fclose($fh);

?>

 

If results are still slow you may need to optimize your SQL query, such as rather than doing SELECT * FROM ..etc... list only the rows you are going to be pulling from the database.

[shergar1983]

I feel like an amateur, but thanks you're right this looks much better indeed.