[Resolved] 29 sec execution time for 930 inserts

[spstieng]

I'm reading a csv file that contains 930 lines.
That takes but a second to read into an array.

Walking through the array and inseting into the database though, takes 29 seconds and the diskdriver works like hell!

Inserting 930 rows shouldn't take that long????

Here's the code I use

$fp2 = fopen ("data/bransjekoder.csv","r");

while ($arrData = fgetcsv ($fp2, 1000, ";"))
{
	$arrBusinessCodes[$i][0] = $arrData[0];
	$arrBusinessCodes[$i][1] = $arrData[1];
	$i++;
}
fclose ($fp2);

run_query($arrBusinessCodes, 'businesstype', null);

run_query function

function run_query($arrObj, $table, $arrObj2)
{
  $objDataAccess = new dao();
  for  ($count=0; $count < sizeof($arrObj); $count++)
  {
	  $arrTmp = $arrObj[$count];
	  $strQuery = $objDataAccess->csv_insert_into_table($table,$arrTmp, NULL);
	  $objDataAccess->_mysql_query($strQuery);
  }
}

mysql_query

function _mysql_query($strQuery)
{
	$result = mysql_query($strQuery) or die (mysql_error());
	return $result;
}

[nemonoman]

Is the disk thrashing on your local hard drive? What manner of computer is this? What's your configuration?

How many indexes do have on the table receiving the INSERTS. If this a follow-on to your earlier questions, I know you have at least one, and id type field demanding unique values. Maybe more indexes as well? With each Insert, all indexes also must be updated.

Try adding a LOCK TABLE command before the series of inserts. This should stop index updates until you UNLOCK TABLE.

What is the content of the function: csv_insert_into_table():

The MUCH, MUCH faster way to do this is to use a direct mysql command, rather than individual mysql queries record by record.

Use PHP to translate your CSV file into a series of semicolon separated SQL INSERT queries. (NOTE: you can quickly prequalify each array entry for duplicate with a SELECT; only create an insert for non duplicates.)

INSERT INTO mytable (1,2,3,4);
INSERT INTO mytable (3,4,5,6);

etc. Save these as a file, and load them with some sort of exec command:

exec ('mysql -uUser -pPassword database.name < myBigFile.sql');

This should be very, very fast; even faster if you LOCK TABLES before the inserts...

[spstieng]

Wow, that was quick :p

Computer details:
Pentium 4 2.88GHz
512 RAM
Seagate Barracuda HDD

CREATE TABLE businesstypelist (
businessID int(11) NOT NULL default '0',
orgID int(11) NOT NULL default '0',
PRIMARY KEY (businessID,orgID)
) TYPE=MyISAM;

Try adding a LOCK TABLE command before the series of inserts. This should stop index updates until you UNLOCK TABLE.

I will try that.

As forreading the CSV file, I do with this code:

$fp = fopen ("data/".$filename,"r");

while ($arrData = fgetcsv ($fp, 1000, ";"))
{
	$i = 	$_SESSION['_i'];
		$arrOrganization[$i][0] = $arrData[0];
		.
		.
		$arrOrganization[$i][6] = $organisationForm; 

So I put each field separeted by semicolon in an array.

This is in my dao.class.php file
csv_insert_into_table()

function csv_insert_into_table($strTableName, $arrFieldArray, $strCondition)
{
  # TODO: Check to see if $arrFieldValues has equal number of fields as table

  $arrFieldNames = $this->get_table_fieldnames($strTableName);

  foreach ($arrFieldArray as $arrKey)  {
	$strFieldValues .= '"'.$arrKey.'",';
  }

  foreach ($arrFieldNames as $key)  {
	$strFieldNames .= $key.',';
  }

  $strFieldValues = substr($strFieldValues,0,-2);
  $strFieldNames = substr($strFieldNames,0,-1);

  $sqlQuery = ('insert into '.$strTableName.' ('.$strFieldNames.') values ('.$strFieldValues.'") ');

  return $sqlQuery;
}

And in my csvpopulate.php file:

This one: run_query($arrBusinessCodes, 'businesstype', null) I only do ones. It just populates a table that is not frequently updated.

These however are executed every time I run the script:

### run queries and populate DB tables

run_query($arrOrganization, 'organisation', null);
run_query($arrAddress, 'address', null);
run_query($arrBusinessArea, 'businesstypelist', null);
run_query($arrPerson, 'person', $arrOrganization);

Just for the hell of it, and if you guys have the time to look them through, I have attached the two files 🙂

[superwormy]

If the script is always running for exactly 29 seconds, changes are it's simply timing out after the 30 second time limit.

set_time_limit(0);

Above code should solve that problem.

Not sure why its taking so long though... time each part of your script maybe and see what takes the most time, whether its actually the queries or whether its something else...

[spstieng]

Thanks superwormy.

I'll try your suggestion.
And thanks to you other guys!