Impact of direct insertion to a physical table in while loop

Most of the times in order to meet the deadlines we try to avoid the extra code whereas It can really blow up the performance if the same practice is followed across entire application. It has been observed that physical tables can be a bottleneck when we are continuously inserting in the table.

If there are thousands of users accessing the same table for insertion then that will get locked for only one user and nobody else would able to perform the insertion.

Environment : Intel Core i3 2.2GHz with 4GB RAM
Database : MySql

Here’s the case study that how a physical table can be spoiling the performance.

Insertion in physical table

Result – For inserting 5000 records in mysql it took around 100 Seconds on developer’s machine

Note – It can vary based on the machine configuration but It would always be super slow as compared to the temporary table

Insertion in temporary table

Result – For inserting 5000 records in mysql it took only 1 second on developer’s machine. Yes just one second.

Conclusion – Wherever you are inserting directly into physical table in while loop change it to a temporary table. The possible reason of this slowness can be acquiring the locks and releasing it frequently.

[Update by AJAY]- I don’t know why but I could see only Half performance gain on specific server. For instance Direct insertion was taking 30 sec and Temp insertion was taking 15 seconds so updating the answer..
where as on local machine it was performing same way as described above.

Leave a Reply

Your email address will not be published. Required fields are marked *