TCPDF Not able to generate beyond 4000 lines

[newbie14]

Dear All,
I have a problem where I can query more then 4000 lines the problem I already drill down is here. The problem is during the generation of the lines some how it takes some time then I get a not found page error. What can I do to over come this?

$totalHeight1 = ceil($pdf->getStringHeight( 20,$eventMessage,$reseth = false,   $autopadding = true,$cellpadding = '',  $border = 1));  
$totalHeight2 = ceil($pdf->getStringHeight( 15,$eventLocation,$reseth = false, $autopadding = true,$cellpadding = '',  $border = 1));  

    if($totalHeight1>$totalHeight2)
       $totalHeight = $totalHeight1;
    else
      $totalHeight = $totalHeight2;
    $pdf->Cell(10, $totalHeight, $count, 1, 0, 'L', 0, '', 0);
    $pdf->Cell(15, $totalHeight, $row['latitude'], 1, 0, 'L', 0, '', 0);
    $pdf->Cell(16, $totalHeight, $row['longitude'], 1, 0, 'L', 0, '', 0);
    $pdf->Cell(25, $totalHeight, $row['dateTimer'], 1, 0, 'L', 0, '', 0);
    $pdf->Cell(25, $totalHeight, $row['dataInsertDateTime'], 1, 0, 'L', 0, '', 0);
    $pdf->MultiCell(20, $totalHeight, $eventMessage, 1, 'L', 0, 0, '', '', true);
    $pdf->Cell(15, $totalHeight, $eventSource, 1, 0, 'L', 0, '', 0);
    $pdf->MultiCell(15, $totalHeight, $eventLocation, 1, 'L', 0, 0, '', '', true);
    $pdf->Cell(15, $totalHeight, $row['stat'], 1, 0, 'L', 0, '', 0);
    $pdf->Cell(10, $totalHeight, $row['speed'], 1, 1, 'L', 0, '', 0);

[fobos]

TCPDF does not have physical limitations. You have to increase the memory in your php.ini

[broj1]

You can do that just for the current script with the ini_set() function.

// on the beginning of your script save original memory limit
$original_mem = ini_get('memory_limit');

// then set it to the value you think you need (experiment)
ini_set('memory_limit','640M');

// at the end of the script set it to it's original value 
// (if you forget this PHP will do it for you when performing garbage collection)
ini_set('memory_limit',$original_mem);

You might do the same with time limit setting

ini_set('max_execution_time', 120); // 120 seconds (2 minutes)

[newbie14]

Dear Broj1,
This is how I did it. The problem just for 6K of data it took me 4 minutes and 40 seconds that is really a lot right. What should I do? Thank you.

// on the beginning of your script save original memory limit
$original_mem = ini_get('memory_limit');
 
// then set it to the value you think you need (experiment)
ini_set('memory_limit','640M');
 
ini_set('max_execution_time', 300);
require_once('tcpdf/config/lang/eng.php');
require_once('tcpdf/tcpdf.php');
session_start(); 
require_once('config.php');

[broj1]

You must find where a bottleneck is in your code. Is it the web server which maybe has trouble generating such long document in memory? What are the specs of the server (RAM; processor, other applications that run on it)? Maybe there are too many queries (is your code efficient when reading from the database or maybe you could use a stored procedure for processing etc). I had similar trouble when trying to generate Excel file with 170 000 rows in PHP and it couldn't be done even with memory increased to 2GB and timeout streched to 20 mins (which were both unacceptable anyway). Then I realized that I was trying to construct a HTML code for 170 000 rows in poor server's memory and that this approach was not OK. I found a solution where I create CSV file writing each row to a file immediately and now it takes me 20 seconds without increasing any php.ini parameters. My suggestion is: first try to optimize your code, increase php.ini parameters even more or try to find other ways like generating many PDF files etc. I was going to direct you also to the TCPDF forum but have noticed that you posted there already.

You may also post the complete code maybe someone here (including myself :-) can find more suggestions.

[newbie14]

Dear Broj1,
According to my research the code which I posted is where the bottleneck is where earlier I could not even generate the report but now it could generated but delayed. I agree the server I am using is very small is just 1Gb but just me using it and no one else using and just this particular query is running. So what else could be a problem then?

[broj1]

I don't know the answer. Maybe the calculation of $totalHeight1 and $totalHeight takes considerable amount of processing time. Try to comment out the lines 1 to 7 and set $totalHeight to some constant value and see if there is any significant increase in speed. You won't get the document formatted the way you want but you will find out if there is a bottleneck. If there is no difference then you will have to search somewhere else.

If it is possible post the whole script here. It might be easier to judge.

[newbie14]

Dear Broj1,
I have tried to comment it out but still the same. Below is a strip down of the codes.Only missing is 3 big if else statement for the eventMessage,eventSource and eventLocation due to long list I just remove it easier for you to check.

// on the beginning of your script save original memory limit
$original_mem = ini_get('memory_limit');
 
// then set it to the value you think you need (experiment)
ini_set('memory_limit','640M');
 
ini_set('max_execution_time', 300);
require_once('tcpdf/config/lang/eng.php');
require_once('tcpdf/tcpdf.php');
session_start(); 
require_once('config.php');
$aT=$_GET["aT"];
$aID=$_GET["aID"];
// create new PDF document
$pdf = new TCPDF(PDF_PAGE_ORIENTATION, PDF_UNIT, PDF_PAGE_FORMAT, true, 'UTF-8', false);
$pdf->setPrintHeader(false);
// set document information
/*$pdf->SetCreator(PDF_CREATOR);
$pdf->SetAuthor('ss');
$pdf->SetTitle('GPS Details Data');
$pdf->SetSubject('');
$pdf->SetKeywords('');*/

// set default header data
/*$pdf->SetHeaderData('', '', 'Test', '');

// set header and footer fonts
$pdf->setHeaderFont(Array(PDF_FONT_NAME_MAIN, '', PDF_FONT_SIZE_MAIN));
$pdf->setFooterFont(Array(PDF_FONT_NAME_DATA, '', PDF_FONT_SIZE_DATA));*/

// set default monospaced font
$pdf->SetDefaultMonospacedFont(PDF_FONT_MONOSPACED);

//set margins
//$pdf->SetMargins(PDF_MARGIN_LEFT, PDF_MARGIN_TOP, PDF_MARGIN_RIGHT);
//$pdf->SetHeaderMargin(PDF_MARGIN_HEADER);
$pdf->SetFooterMargin(PDF_MARGIN_FOOTER);

//set auto page breaks
$pdf->SetAutoPageBreak(TRUE, PDF_MARGIN_BOTTOM);

//set image scale factor
$pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);

//set some language-dependent strings
$pdf->setLanguageArray($l);

// ---------------------------------------------------------

// set font
//$pdf->SetFont('times', '', 10);

// add a page
$pdf->AddPage();

// set cell padding
//$pdf->setCellPaddings(1, 1, 1, 1);

// set cell margins
//$pdf->setCellMargins(1, 1, 1, 1);

// set color for background
$pdf->SetTextColor( 0, 1, 1 );
$pdf->SetFont( 'helvetica', '', 17 );
$pdf->Cell(0, 0, 'GPS Detail Data', 0, 1, 'C', 0, '', 0);


	$link = mysql_connect(dbHost, dbUser, dbPassword);
	if(!$link)
	{
			die('Failed to connect to server: ' . mysql_error());
	}
		
	//Select database
	$db = mysql_select_db(dbDatabase);
	if(!$db) 
	{
			die("Unable to select database");
	}
	
	
$pdf->Ln(5);  
//$pdf->SetTextColor( );
$pdf->SetFillColor( 204, 204, 204);  
$pdf->SetFont( 'helvetica', 'B', 7 ); 
$pdf->Cell(10, 12, 'No', 1, 0, 'L', 0, '', 0); 
$pdf->Cell(15, 12, 'Lat', 1, 0, 'L', 0, '', 0); 
$pdf->Cell(16, 12, 'Long', 1, 0, 'L', 0, '', 0); 
$pdf->Cell(25, 12, 'Event Date Time', 1, 0, 'L', 0, '', 0); 
$pdf->Cell(25, 12, 'Insert Date Time', 1, 0, 'L', 0, '', 0);
$pdf->Cell(20, 12, 'Event', 1, 0, 'L', 0, '', 0); 
$pdf->Cell(15, 12, 'Source', 1, 0, 'L', 0, '', 0);
$pdf->Cell(15, 12, 'Location', 1, 0, 'L', 0, '', 0); 
$pdf->Cell(15, 12, 'Status', 1, 0, 'L', 0, '', 0);
$pdf->Cell(10, 12, 'Speed', 1, 1, 'L', 0, '', 0);


$count=0;	  
	  $selectQuery1 = "SELECT tblData.latitude,tblData.longitude,tblData.geoFenceAlertIDIn,tblGFAIn.geoFenceAlertEntryStatus As inStatus,tblGFAOut.geoFenceAlertEntryStatus As outStatus,
                    tblData.geoFenceAlertIDOut,tblGeoFenceAlertIn.geoFenceName as geoFenceNameIn,tblGeoFenceAlertOut.geoFenceName as geoFenceNameOut,
                    CAST(Date_Add(tblData.dateTimer , Interval '".$gmtValue."' hour_minute) AS CHAR) As dateTimer,CAST(tblData.dataInsertDateTime  AS CHAR) As scvDataInsertDateTime ,tblEventAlert.eventAlertType,tblData.stat,tblData.speed,tblPartner.partnerSerialNumber
                    FROM tblData
                    LEFT JOIN tblGeoFence As tblGeoFenceAlertIn  ON  tblData.geoFenceInID=tblGeoFenceAlertIn.geoFenceID 
						 		    LEFT JOIN tblGeoFence As tblGeoFenceAlertOut  ON tblData.geoFenceOutID=tblGeoFenceAlertOut.geoFenceID 
                    LEFT JOIN tblGeoFenceAlert As tblGFAIn ON tblData.geoFenceAlertIDIn=tblGFAIn.geoFenceAlertID
                    LEFT JOIN tblGeoFenceAlert AS tblGFAOut ON tblGFAOut.geoFenceAlertID = tblData.geoFenceAlertIDOut 
                    LEFT JOIN tblEventAlert ON tblData.eventAlertID=tblEventAlert.eventAlertID 
                    LEFT JOIN tblPartner ON tblEventAlert.partnerID=tblPartner.partnerID 
                    Where tblData.aID=".$aID." Order By tblData.dateTimer  Asc "; 
                    
                    
$result1 = mysql_query($selectQuery1);
			$n1 = mysql_num_rows($result1);
			
		  
			while($row = mysql_fetch_array($result1, MYSQL_ASSOC))
		  {
		    $count++;
		    
		    $aID=$row['aID'];
		    $geoFenceAlertIDIn=$row['geoFenceAlertIDIn'];
		    $inStatus=$row['inStatus'];
		    $geoFenceAlertIDOut=$row['geoFenceAlertIDOut'];
		    $outStatus=$row['outStatus'];
		    $eventAlertType=$row['eventAlertType'];
		    $partnerSerialNumber=$row['partnerSerialNumber'];
		    
		    
		    /*$totalHeight1 = ceil($pdf->getStringHeight( 20,$eventMessage,$reseth = false, $autopadding = true,$cellpadding = '',  $border = 1));  
		    $totalHeight2 = ceil($pdf->getStringHeight( 15,$eventLocation,$reseth = false, $autopadding = true,$cellpadding = '',  $border = 1));  
		    
		    if($totalHeight1>$totalHeight2)
           $totalHeight = $totalHeight1;
        else
          $totalHeight = $totalHeight2;*/
        $totalHeight = 5;  
        $pdf->Cell(10, $totalHeight, $count, 1, 0, 'L', 0, '', 0);
        $pdf->Cell(15, $totalHeight, $row['latitude'], 1, 0, 'L', 0, '', 0);
        $pdf->Cell(16, $totalHeight, $row['longitude'], 1, 0, 'L', 0, '', 0);
        $pdf->Cell(25, $totalHeight, $row['dateTimer'], 1, 0, 'L', 0, '', 0);
        $pdf->Cell(25, $totalHeight, $row['dataInsertDateTime'], 1, 0, 'L', 0, '', 0);
        $pdf->MultiCell(20, $totalHeight, $eventMessage, 1, 'L', 0, 0, '', '', true);
        $pdf->Cell(15, $totalHeight, $eventSource, 1, 0, 'L', 0, '', 0);
        $pdf->MultiCell(15, $totalHeight, $eventLocation, 1, 'L', 0, 0, '', '', true);
        $pdf->Cell(15, $totalHeight, $row['stat'], 1, 0, 'L', 0, '', 0);
        $pdf->Cell(10, $totalHeight, $row['speed'], 1, 1, 'L', 0, '', 0);
		  }  
			mysql_close($link);
			
$pdf->Ln(4);

//Close and output PDF document
$pdf->Output('example_005.pdf', 'I');

// at the end of the script set it to it's original value 
// (if you forget this PHP will do it for you when performing garbage collection)
ini_set('memory_limit',$original_mem);

[broj1]

I adapted your script to use static data instead of database rows (I haven't set up a database). It creates 193 pages PDF file of 10000 rows in 33 seconds. The size of the file is 1.5 MB. I didn't change the required memory at all but had to increase max execution time to 60 seconds.

I suggest you try the same. Prepare first test script which is similar to the one above but instead of reading a database just assign a $row array some arbitrary values. Then change the while loop into a for loop and try say 10000 iterations and measure execution time. If it is still very long it must be something with your server setup.

Then prepare a second test script where you read data from a database but do not write anything to PDF file just to test if maybe reading 4000 rows from the database is causing the bottleneck. If this is the case optimize the database (use indexes, denormalize, try to optimize the query...).

In other words try to localize a porblem (is it PDF generation or database reads) and work from there. Your query seems to be complex (many joins) and I do not know how well mysql is optimized for working with geospational data.

[newbie14]

Dear Broj1,
I tried your first method is scored around 1minute 40 seconds on the 1Gb machine then I tried on another machine 8 cpus and 16Gb ram it was just 43 seconds. So could it be due to hardware?

For the second method I close the while loop where it start to do the writing process it just took 11 seconds so I guess the bottle neck is the writing to PDF.

[broj1]

I guess hardware is quite important here. It takes some effort to create a line of PDF document so CPU is important. And as PDF file is growing RAM is equally important since everything is kept there until the document is created (but it seems that RAM isn't such a big issue here). So at the end of the day for complex tasks you have to have quite powerful server.

[newbie14]

Dear Broj1,
Do you think the bigger server should be doing it better then 43 seconds as it have bigger memory? Must I tweak the smaller server? Thank you.

[fobos]

Well you just answered your problem. The 1GB machine is slower so it will take longer to process the information.

[broj1]

The server I ran it on is HP Proliant ML1010 G6 with 4 GB of RAM and Intel i3 processor with Apache and MySql. Noting fancy but OK. But from here onwards we are all only guessing. You will have to try it on machines that are available to you.

[newbie14]

Dear Broj1,
I would like to ask why I find that FPDF runs much faster then TCPDF any specific tweaking needed for TCPDF to make it run faster based on your experience?

[broj1]

I started with FPDF but soon find out that it is not being maintained. I also had some troubles with character sets. So I soon switched to TCPDF and would not know why the difference. I haven't done any tweaking to TCPDF.

[newbie14]

Dear Broj1,
Just to share with you and the rest I tested my problem with fpdf wow it could generate 12K lines within 40second on the 1Gb Ram machine. So since Tcpdf originates from fpdf I am sure some tweaking can be done to improve it right?

[broj1]

I think you are right. Please let us know if you find out where the trouble was in your case.

[newbie14]

Dear Broj1,
I have done the changes as suggested here http://sourceforge.net/projects/tcpdf/forums/forum/435311/topic/3785743 regarding the $font = $this->_getTrueTypeFontSubset($font, $subsetchars); but still the same.

[broj1]

Have you tried the suggestion in the post No. 14 on that page? I still have some problems getting to terms with the stuff about fonts especially in my language (slovene, cp1250).

[newbie14]

Dear Broj1,
Yes I have tried those too but not changes to the speed. I am quite surprise it was based on fpdf but is very slow in comparison to its original source.

[broj1]

It is obviously due to the functions supporting UTF8 (just guessing).