Solved

Process very slow for RETS/MYSQL script and stops after so many loops

Posted on 2014-09-12
4
589 Views
Last Modified: 2014-09-12
I found code to pull rets data and make a csv file (https://github.com/troydavisson/PHRETS/wiki/Connect,-download-listing-data-in-csv-format,-disconnect)

I moded this after getting it to work on my rets server to work with mysql (NOTE: it did work fine with csv files, and i was able to get the 300+ meg file to populate mysql with LOAD, but i ran into problems getting fields with commas and qoutes etc to parse correctly as well as a deliminator,(seams to be a common problem) I am now trying to directly inject to mysql with a modified version of this csv file.

 it takes forever to process, is there any way i can speed it up some? I have other tables to do.

and after 5-10 mins and 14,000 records it dies. trying to pull 118k+ records as a one time pull
i have in my max_execution_time and memory_limit .
please note it did work with the csv file - i just could not use it as i had major parse errors


Thank you in advance for any code or help you may provide.
Johnny


<?php 
ini_set("memory_limit","375M");
ini_set('max_execution_time', 12500); //7 mins is 420 seconds - 300 seconds = 5 minutes
require_once('functions.php'); 
include ("../../mysqli_ctx.php");

$rets_login_url = "xxxx"; 
$rets_username = "xxxxx"; 
$rets_password = "xxxxx"; 

// use http://retsmd.com to help determine the SystemName of the DateTime field which 
// designates when a record was last modified 
//$rets_modtimestamp_field = "LastModifiedDateTime"; RECORDMODDATE
$rets_modtimestamp_field = "RECORDMODDATE";
// use http://retsmd.com to help determine the names of the classes you want to pull. 
// these might be something like RE_1, RES, RESI, 1, etc. 
$property_classes = array("DE"); 

// DateTime which is used to determine how far back to retrieve records. 
// using a really old date so we can get everything 
$previous_start_time = "1980-01-01T00:00:00"; 


$sixmonths = date('Y-m-d\TH:i:s', time()-15778800); // get listings updated within last 6 months
////////////////////////////// 

require_once("../lib/phrets.php"); 

// start rets connection 
$rets = new phRETS; 

//$rets->AddHeader("RETS-Version", "RETS/1.7.2"); 
//$rets->AddHeader('User-Agent', "YOUR_USER_AGENT_HEADER_IF_NECESSARY"); 

$rets->SetParam("compression_enabled", true); 

echo " * Connecting to {$rets_login_url} as {$rets_username}<br>\n"; 
$connect = $rets->Connect($rets_login_url, $rets_username, $rets_password); 

if ($connect) { 
    echo "  * Connected<br>\n"; 
} 
else { 
        echo "  * Not connected:<br>\n"; 
        print_r($rets->Error()); 
        exit; 
} 

foreach ($property_classes as $class) { 

        echo " * Property class: {$class}<br>\n"; 

        $maxrows = true; 
        $offset = 1; 
        $limit = 1000; 
        $fields_order = array(); 
        while ($maxrows) { 
                //$query = "(LN=0+),(Status=|A,C),(County=|Barnstable,Dukes,Nantucket,Plymouth)";
                $query = '(RECORDMODDATE='.$sixmonths.'+)'; 
                // run RETS search 
                echo "  * Query: {$query}  Limit: {$limit}  Offset: {$offset}<br>\n"; 
                $search = $rets->SearchQuery(
                "Property",
                $class,
                $query,
                array('Limit' => $limit, 'Offset' => $offset, 'Format' => 'COMPACT-DECODED', 'Count' => 1)); 
                echo "  * Total found: {$rets->TotalRecordsFound()}<br>\n";
                echo "Proccessing...<br>";
                if ($rets->NumRows() > 0) { 
                        if ($offset == 1) { 
                                // print filename headers as first line 
                                $fields_order = $rets->SearchGetFields($search); 
                                //fputcsv($fh, $fields_order); 
                        } 
                        // process results 
                        while ($record = $rets->FetchRow($search)) { 
                                $this_record = array(); 
                                foreach ($fields_order as $fo) { 
                                        $this_record[] = '"'.mysqli_real_escape_string($mysqli,$record[$fo]).'"';
                                }//for each
                                //mysql inject
                                 $fields_order_l = implode(',', $fields_order);
                                 $fields_order_l = str_replace(",MOD,",",MODx,",$fields_order_l);
                                 $this_record = implode(',', $this_record);
				    $table="rets_property_de";
				    $query = "INSERT INTO `".$table."` (".$fields_order_l.") VALUES (".$this_record.")";
				        //echo $query;
				        $r = mysqli_query($mysqli,$query);
				        if(!$r )
					{
					  die('<br>Could not enter data: ' . mysqli_error($mysqli));
					}
					else 
					{
					//echo "<br>Entered data successfully<br>\n";
					// printf("%d Row inserted.<br>\n", $mysqli->affected_rows);
					//echo ".";
					}//if r
					//echo "<br>"; 
			} // WHILE
                        $offset = ($offset + $rets->NumRows()); 
                } // if num
                $maxrows = $rets->IsMaxrowsReached(); 
                $rets->FreeResult($search); 
        } // while max
 }// for each
mysqli_close($mysqli);
echo "- done<br>\n"; 
echo " * Disconnecting<br>\n"; 
$rets->Disconnect(); 

?> 

Open in new window

0
Comment
Question by:Johnny
  • 2
  • 2
4 Comments
 
LVL 34

Expert Comment

by:gr8gonzo
ID: 40319396
You can speed up bulk inserts by pushing multiple records into the database in one INSERT instead of one INSERT per record. This reduces a LOT of overhead when you're working with a ton of records.

Basically the syntax is:
INSERT INTO table
(fieldA,fieldB,fieldC,etc)
VALUES
(rec1A,rec1B,rec1C,etc),
(rec2A,rec2B,rec2C,etc),
(rec3A,rec3B,rec3C,etc);

So assuming the fields are always the same for each record (and in the same order), I would suggest filling an array with your records instead of inserting each time. Then, when the array gets to 100 or 500 or 1000 records (test out different volumes to see what works for you), construct the bulk query and run it and clear your array and let it keep filling up with more records.

If each individual record takes 0.3 seconds to insert (just a hypothetical), then 10 records should take 3 seconds to insert individually. However, you can likely insert those same 10 records in 1 second or less with the bulk insert syntax.
0
 
LVL 34

Accepted Solution

by:
gr8gonzo earned 500 total points
ID: 40319416
For the sake of simplicity, here's the adjusted while loop of your code. Basically, you:

1. Add a new $queue array before each while() loop that fetches your records.
2. Instead of querying, you add the $this_record to the $queue array.
3. Move the query to AFTER the end of the while() loop, and change the query to implode all values in $queue.

                        // Define a new, empty queue
                        $queue = array();

                        // process results 
                        while ($record = $rets->FetchRow($search)) { 
                                $this_record = array(); 
                                foreach ($fields_order as $fo) { 
                                        $this_record[] = '"'.mysqli_real_escape_string($mysqli,$record[$fo]).'"';
                                }//for each
                                //mysql inject
                                 $fields_order_l = implode(',', $fields_order);
                                 $fields_order_l = str_replace(",MOD,",",MODx,",$fields_order_l);
                                 $this_record = implode(',', $this_record);
				    $table="rets_property_de";

                                    // Add to queue instead of querying
                                    $queue[] = "(" . $this_record . ")";

                                     // REMOVED THE QUERY CODE FROM THE WHILE LOOP
			} // WHILE

                        // Now that the loop is finished...
				    $query = "INSERT INTO `".$table."` (".$fields_order_l.") VALUES " . implode(",",$queue);
				        //echo $query;
				        $r = mysqli_query($mysqli,$query);
				        if(!$r )
					{
					  die('<br>Could not enter data: ' . mysqli_error($mysqli));
					}
					else 
					{
					//echo "<br>Entered data successfully<br>\n";
					// printf("%d Row inserted.<br>\n", $mysqli->affected_rows);
					//echo ".";
					}//if r
					//echo "<br>"; 

Open in new window

0
 

Author Comment

by:Johnny
ID: 40319439
@gr8gonzo i have over 118k records im trying to inject here would storing them all up in one shot be able to handle it you mentioned firing off after so many in the array (how would i do that)
or would it be good to break apart the full query array in chunks after its built
no the field names do not change for the sets.
as there are 1000 records it pulls from rets server in blocks would that not be a good starting number to try?

thank you for your help
Johnny
0
 

Author Comment

by:Johnny
ID: 40319492
Actually that would fire off 1k blocks
im testing the code now

update: so far looks like its working out great MUCH faster - im going to accept as answer and if i have any more problem reference this post. Thank you again so much
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
This article discusses four methods for overlaying images in a container on a web page
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now