Insert Multiple Rows In Database Using Loop

Yii 1.1 General Discussion
1

Hello Friends.

For any application and product which deals with database performance is always matter. If we are inserting multiple rows in database at a time execution time always comes in picture.

Today I have compare execution time for inserting multiples rows at a time using createCommand and save() methods.

Here is my both action for inserting multiple rows




        public function actionTime1()  //insert 50000 records using createCommand method

	{

		$t1 = microtime(true); 

		echo $t1;

		$sql = 'INSERT into employee (`id`, `name`, `contact`, `address`, `city`) VALUES (NULL, \'Ravi\', \'9601263350\', \'Bhagwati chowk\', \'supedi\')';

		for($i=0;$i<50000;$i++)

		 $sql .= ',(NULL, \'Ravi\', \'9999999999\', \'Bhagwati chowk\', \'supedi\')';

		

		$connection = Yii::app() -> db;

		$command = $connection -> createCommand($sql);

		$command -> execute();

		echo "</br>";

		$execution_time = sprintf('It took %.5f sec', microtime(true)-$t1);

		echo $execution_time;

		exit; 

	}

        public function actionTime2()           //insert 500 recodrs using save() method

	{

		$model = new Employee;

		$t1 = microtime(true); 

		echo $t1;

		for($i=0;$i<500;$i++){

			$model->setIsNewRecord(true);

		 	$model->id = null;

			$model->name= "Ravi";

			$model->contact = "9999999999";

			$model->address = "Bhagwati chowk";

			$model->city = "supedi";

			$model->save();			

		}

		echo "</br>";

		$execution_time = sprintf('It took %.5f sec', microtime(true)-$t1);

		echo $execution_time;

		exit; 

	}

Action Time1 Give following output for 50000 records :

It took 1.47028 sec

Action Time2 Give following output for 500 records:

It took 44.25273 sec

Is there any better solution in Yii??

Thankx In Advance…

2

[size=2]Excellent Work…-[/size]:)

3

Hi

I had the same thought before 3 months

check this, works with 1.1.14 yii version

http://www.yiiframework.com/forum/index.php/topic/45623-multi-insert-rows/page__view__findpost__p__215297

compare the time and inform us :)

Also avoid raw sql query without params for security reasons!

certainly will not be faster than raw sql but more secured!

I am waiting for the results :)

4

Thankx for your response.

I have tried createMultipleInsertCommand also. But it take more time compared to sql query. As you told using sql query security leakage comes into picture. And one thing that i noticed that if i try to insert 20,000 records at a time it is not working. It is working up to 18,000 records. I dont know what wrong with that.

5

Please give us the results (with time compares)

Suse raw sql is faster than other methods… of course :)

What is the error message over 18000 records ?

6

No it is not give any error message. But records are not inserting. I think execution stops.

createMultipleInsertCommand takes 0.63355 sec for inserting 500 Records

7

Very fast comparing with model way, but slow enough comparing with raw sql! :)

Reference to the execution for too many inserts I think is the problem of php PDO driver or mysql database…

8

Ya, It can be possible… But I have not much idea about PDO. But it is enough right now. It rarely happened that we are inserting 10000+ record at a time.

Thank You Very Much KonApaz…

Nice To discuss with you…

9

You welcome! :)

May you have to split the inserts into parts. For example by 5000 insertions at the time.

I think there is a count mysql -or- PDO driver limitations by this way.

10

Ya… By this way it is possible. But now we conclude that for inserting multiple data createMultipleInsertCommand is better solution instead of insert data in loop and save method. what you say??

11

I think the golden ratio is createMultipleInsertCommand, because there are three important things

Fast,Secure and Professional.

If you need more speed you have to absolutely sure that the data is purified and safe for your database and for users of your website, in this case you could user a loop to make your query and then executes.

In general case I prefer createMultipleInsertCommand…

12

i would recommend to use createMultipleInsertCommand as it is much faster then single query insertion.

i tried with 1.5 Lakhs rows with single query insertion it took around 8-9 mins and at the other side when i used

createMultipleInsertCommand function then you can check the result below :

Starting Time : 1404215549.9961

Total Rows : 152061

It took 264.22411 sec

i used some other method to insert data like to fetch data in $data array variable then divide it into parts then use bulk insert with this method it will reduce the overhead from PDO also it will take less memory as well.

$dataArray = array_chunk($data, 5000);

    &#036;builder = &#036;model-&gt;getDbConnection()-&gt;schema-&gt;commandBuilder;


    &#036;total = 0;


    


    for(&#036;i=0; &#036;i &lt; count(&#036;dataArray); &#036;i++){


        &#036;command = &#036;builder-&gt;createMultipleInsertCommand(&#036;tableName, &#036;dataArray[&#036;i]);


        &#036;total = &#036;total + &#036;command-&gt;execute();


    }

happy coding :slight_smile:

checkout some other stuff over here : http://www.moodycoder.com/