Populate Large Data in xPDO?

☆ A M B ☆
183 Posts

lokamaya Reply #1, 12 years, 5 months ago

Hi all,

Is there any simple way to populate data in xPDO?

I'm creating new addon, something like "geo country, state, city, village". And I want to insert thousand data to database when installing this addon. How can I do that? Inserting row one by one using xPDO->save() would works, but too heavy.

Thank you for your help...

This question has been answered by okyanet. See the first response.

[ed. note: lokamaya last edited this post 12 years, 5 months ago.]

zaenal.lokamaya

discuss.answer

863 Posts

Lucas Reply #2, 12 years, 5 months ago

You mean like adding a whole table, pre-populated?

If you wanted to avoid ->save() for each record, I guess you could have your script loop through all your records and format them as a mySql import file, then simply import the whole table using phpMyAdmin. Run an export on one of your tables to see how it should be formatted.

Otherwise another forum member successfully ran ImportX for over 7000 records but needed to change the php timeout limit. That just runs save() for every record and seemed to work ok...

88 Posts

lazylegs Reply #3, 12 years, 5 months ago

I would not recommend using using xPDO objects directly to insert lot of data. Like stated above, you need to change php timeout to prevent dying of code.

So relating to this, run few test to see how fast it could actually run with xPDO::newObject() and to compare the results. Run second and third test with prepared statements with and without binding values with PDOStatement::bindValue() and directly setting the values to array PDOStatement::execute().

As what to insert I used Australian suburbs and their latitude and longitude values from csv file that has 63214 rows. The system used is Ubuntu server install on VMWare Player with 512Mb of memory and MODX Revolution version 2.1.3-pl. All settings for apache, php and mysql are defaults from install.

First runs with newObject() and save()

<?
/**
 * @author OrangeLine - Lazylegs
 * @copyright 2011
 * @package MultiInsert
 */
 
$path = MODX_CORE_PATH . 'components/geo/';
$result = $modx->addPackage('geo',$path . 'model/','geo_');
if (!$result) { return 'failed'; }

$time = explode(' ', microtime());
$time = $time[1] + $time[0]; // return array
$begintime = $time; //define begin time
$handler = fopen('/absolute/path/to/file/lazy_geo.csv', "r");
$i = 0;
while(($data = fgetcsv($handler, 512, ";", '"'))) {
    $q = $modx->newObject('Places', array("suburb" => $data[0], "latitude" => $data[1], "longitude" => $data[2]));
    $q->save();
    $i++;
}
$time = explode(" ", microtime());
$time = $time[1] + $time[0];
$endtime = $time; //define end time
$totaltime = ($endtime - $begintime); //decrease to get total time
return $totaltime.' seconds and ' . $i . ' rows were inserted'; //echo it to appear in browser

Results

Run #1: Timed out @ 51003 rows inserted
Run #2: Timed out @ 51224 rows inserted
Run #3: Timed out @ 51253 rows inserted
Run #4: Timed out @ 50945 rows inserted
Run #5: Timed out @ 51024 rows inserted

The results were not anything surprising to be honest. I have maximum execution time set to 30 seconds which is default for php install that I got. and it was intentionally left to default So you can insert quite lot of stuff even on pretty weak system with newObject().

Then tests using prepared statements

<?php

/**
 * @author OrangeLine - Lazylegs
 * @copyright 2011
 * @package MultiInsert
 */
$path = MODX_CORE_PATH . 'components/geo/';
$result = $modx->addPackage('geo',$path . 'model/','geo_');
if (!$result) { return 'failed'; }
$table = $modx->getTableName('Places');

$sql = "INSERT INTO {$table} (suburb, latitude, longitude) VALUES (?, ?, ?)";
$q = $modx->prepare($sql);
$time = explode(' ', microtime());
$time = $time[1] + $time[0]; // return array
$begintime = $time; //define begin time
$handler = fopen('/absolute/path/to/file/lazy_geo.csv', "r");
$i = 0;
while(($data = fgetcsv($handler, 512, ";", '"'))) {
    $q->bindValue(1, $data[0]);
    $q->bindValue(2, $data[1]);
    $q->bindValue(3, $data[2]);
    $q->execute();
    $i++;
}
$time = explode(" ", microtime());
$time = $time[1] + $time[0];
$endtime = $time; //define end time
$totaltime = ($endtime - $begintime); //decrease to get total time
return $totaltime.' seconds and ' . $i . ' rows were inserted'; //echo it to appear in browser

Results

Run #1: 6.1418168544769 seconds and 63214 rows were inserted
Run #2: 5.9312469959259 seconds and 63214 rows were inserted
Run #3: 5.4812779426575 seconds and 63214 rows were inserted
Run #4: 5.7925748825073 seconds and 63214 rows were inserted
Run #5: 5.6238942146301 seconds and 63214 rows were inserted

And same run but changing the $q->bindValue()'s to array on $q->execute(array($data[0], $data[1], $data[2]));

Results

Run #1: 5.6559760570526 seconds and 63214 rows were inserted
Run #2: 5.5258798599243 seconds and 63214 rows were inserted
Run #3: 5.4761080741882 seconds and 63214 rows were inserted
Run #4: 5.466236114502 seconds and 63214 rows were inserted
Run #5: 5.6207709312439 seconds and 63214 rows were inserted

So quick sum up, if you need to just insert data to database in big lumps. Do avoid newObject() unless you are 100% sure you can insert all data within boundaries or break down the insert into smaller amounts at a time. [ed. note: lazylegs last edited this post 12 years, 5 months ago.]

Almost retired from web-development industry but still randomly writing at Lazylegs.info and on schedule hopefully in near future to finish Oracle and PostgreSQL ports of MODX

☆ A M B ☆
183 Posts

lokamaya Reply #4, 12 years, 5 months ago

Quote from: okyanet at Nov 15, 2011, 01:32 PM

You mean like adding a whole table, pre-populated?

Otherwise another forum member successfully ran ImportX for over 7000 records but needed to change the php timeout limit. That just runs save() for every record and seemed to work ok...

Yes, I need pre-populated data on my addon table.

Seem I have to try ImportX for that. Thank's for the suggestion.

zaenal.lokamaya

☆ A M B ☆
183 Posts

lokamaya Reply #5, 12 years, 5 months ago

Quote from: lazylegs at Nov 16, 2011, 07:29 AM

I would not recommend using using xPDO objects directly to insert lot of data. Like stated above, you need to change php timeout to prevent dying of code.

Results

Run #1: 6.1418168544769 seconds and 63214 rows were inserted

Run #2: 5.9312469959259 seconds and 63214 rows were inserted

Run #3: 5.4812779426575 seconds and 63214 rows were inserted

Run #4: 5.7925748825073 seconds and 63214 rows were inserted

Run #5: 5.6238942146301 seconds and 63214 rows were inserted

And same run but changing the $q->bindValue()'s to array on $q->execute(array($data[0], $data[1], $data[2]));

Results

Run #1: 5.6559760570526 seconds and 63214 rows were inserted

Run #2: 5.5258798599243 seconds and 63214 rows were inserted

Run #3: 5.4761080741882 seconds and 63214 rows were inserted

Run #4: 5.466236114502 seconds and 63214 rows were inserted

Run #5: 5.6207709312439 seconds and 63214 rows were inserted

So quick sum up, if you need to just insert data to database in big lumps. Do avoid newObject() unless you are 100% sure you can insert all data within boundaries or break down the insert into smaller amounts at a time.

Hi lazylegs, Thanks for the posting. It was very insightful.

Look like xPDO::Object::save is pretty fast, condisering you ran that query on 512's vmware machine.

My addon, Geo-Indonesia, has 33 provinces, 497 districts, 6094 sub-districts, and 73077 administrative areas. It has never been tested on the real server, except on my windows xp machine. And I have to add set_timelimit(0) to make sure it did not die when installing this addon.

zaenal.lokamaya

Answered Populate Large Data in xPDO?