File Viewer

Choose a file to view: index.php or go back to the demo

string(2672) "<?php
//First generate 1000000 random numbers and place into an array
echo "Generating Array with 1000000 elements ";
$dataArray = new SplFixedArray(1000000);
for($i = 0; $i < 1000000; $i++)
{
	$dataArray[$i] = rand(0,2);
}

//Connect to DB 
echo "Connecting to DB ";
$mysqli = new mysqli("testdb.bavanyo.com", "bavanyodbuser01", "bavanyoDBPass01!", "bavanyo_example");
if ($mysqli->connect_errno) {
 echo"Connect failed: ".$mysqli->connect_error." ";
 exit();
}

//////////////////////////////////////////////////////////////////////////////////////////////////////////////
/*
According to the MySQL literature, the best way to insert a large data set
is to break it into chunks...
 
Quoted from literature: 
"If you are inserting many rows from the same client at the same time, use INSERT statements with multiple VALUES 
lists to insert several rows at a time. This is considerably faster (many times faster in some cases) 
than using separate single-row INSERT statements. If you are adding data to a nonempty table, you can tune 
the bulk_insert_buffer_size variable to make data insertion even faster. See Section 5.1.4, �Server System Variables�. 
(See http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html)

I will demonstrate that technique
You can vary $numChunks to see it's affect
*/
//////////////////////////////////////////////////////////////////////////////////////////////////////////////

$numChunks = 1000; //Arbitrarilly choose 1000 (Square root of 1000000...)
$chunkSize = ceil(sizeof($dataArray)/$numChunks);
echo "Inserting ".$numChunks." Chunks in ".$chunkSize." element increments ";

$start = microtime(true);
//Do the work
for($j = 0; $j < $numChunks; $j++) //each chunk
{
	$query = "INSERT INTO `chunk_test` (`numbers`)	VALUES ";
	for($i = $chunkSize*$j; $i < $chunkSize*($j + 1) and $i < sizeof($dataArray) ; $i++ ) //create query
	{
	 $query .= "('".$dataArray[$i]."'),";
	}
	
	$query[strlen($query)-1] = ""; //remove last comma
	
	if(!$mysqli->query($query)) //Insert Chunk
	{
		echo $mysqli->error." ";
		echo $query." ";
	}

}
$end = microtime(true);

echo "Done with Chunks ";
echo "It took " . round((floatval($end) - floatval($start)),3) . " seconds to isert the 1000000 records ";

//clean up test data
echo "Cleaning up test records"." ";
$query = "DELETE FROM `chunk_test`";
if(!$mysqli->query($query)) 
{
	echo $mysqli->error." ";
	echo $query." ";
}
	
echo "Disconnecting from DB"." ";	
$mysqli->close();
?>

<a href="../index.php">[Back to demos home]</a>  <a href="fileView.php">[View source code]</a>"