Solved

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

Posted on 2014-09-12
4
642 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 35

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 35

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
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…

696 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