Inserting Multiple rows in a MySQL Table : Threading vs BatchUpdate

Question

Suppose, we have to insert 20 rows in a mysql table through java code. Which of the following methods will be efficient and why.

1) Making all the insert statements into one batch using executeBatch and then using commit method of Preparedstatement to insert all rows in one go.

2)Creating threads for each insertion.Each thread inserts one row.

PS: The insertion is done through web API ,so we need the status (whether insertion failed or succedded ) in real time .

I would say executeBatch is much better option, Because it will do the insertion in one db call and performant, Multiple threading in itself makes these insertions slow, and could be possible multiple socket connection from DB pool will be used, And if you have only one connection, then all other threads will wait to use that connection. — Raman Sharma
– Raman Sharma, Commented Nov 6, 2017 at 9:46
Depends on how valuable data is probably. On some occasions when i need to archive something, when no real time data needed, I have a thread with linkedqueue which does writing into database and the main thread simply add record to that queue to process. — Marvin
– Marvin, Commented Nov 6, 2017 at 9:47
@Marvin .Does this mean that multi threading is slower than 1st option. I need it in real time. — Suryakant Pandey
– Suryakant Pandey, Commented Nov 6, 2017 at 9:56
I have single thread in my case to handle inserts sequential and not blocking main process. — Marvin
– Marvin, Commented Nov 7, 2017 at 0:57

Bikramjit Rajbongshi · Accepted Answer · 2017-11-06 09:47:31Z

1

Below description may answer.

Yes, generally bulk insertion is faster than single insert at a time as it avoids intermediate communication which was occurring for every insert statement.

But sometimes it leads to problems as insert/update statements acquire exclusive locks on tables/rows which means no other process/connection can use table at that time.

If you have multiple processes using DB at the same some of which are reading from table and some are writing then whole operations will be locked/stopped at the time of your bulk insertion

so bulk insertion will lock the table for more time than single insert which can cause issues for other processes if lock time is more and DB is not tuned for it.

If you are just inserting into the DB with no other operation, then go for bulk insert from a file (which is much faster) and if you have other processes in place adjust bulk insert frequency considering locking.

Answer is given here : Performance Multiple inserts or multiple values single insert

answered Nov 6, 2017 at 9:47

Bikramjit Rajbongshi

4523 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Suryakant Pandey Over a year ago

This answer I have seen.It does not talk compare multi threading by inserting one row via one thread.

Bikramjit Rajbongshi Over a year ago

Yes, but you have mentioned "Creating threads for each insertion" means multiple single insertion. So each thread will do single insertion and so DB will get hit that many times.

Suryakant Pandey Over a year ago

But these are not 'sequential ' multiple single insertion. So we cannot compare time efficiency from your answer.

Bikramjit Rajbongshi Over a year ago

Possibly you didn't get my answer. See, although your requests are coming asynchronously but SQL insertion will happen sequentially that means there is internal locking mechanism which will lock the row/table for each insertion and then process other requests. You can get more explanation here : dev.mysql.com/doc/refman/5.7/en/internal-locking.html

Suryakant Pandey Over a year ago

We can use row lock , and since multiple inserts are in different rows, Threads can execute in parallel.

Umair Rasool · Accepted Answer · 2017-11-06 10:49:16Z

This is how I add multiple instances to SQL database: Use php2java.com to convert this code according to java and see what you need.

global $db; //create database object
//if database tables does not exist already create them
        if($db->query('SELECT 1 from store_access') == FALSE) {
            $query = 'CREATE TABLE store_access (
                `access_id` bigint(20) NOT NULL AUTO_INCREMENT,
                `user_id` bigint(20) NOT NULL,
                `store_id` bigint(20) NOT NULL,
                `sales` bigint(20) NOT NULL,
                `purchase` bigint(20) NOT NULL,
                `vendors` bigint(20) NOT NULL,
                `clients` bigint(20) NOT NULL,
                `products` bigint(20) NOT NULL,
                `warehouse` bigint(20) NOT NULL,
                `returns` bigint(20) NOT NULL,
                `price_level` bigint(20) NOT NULL,
                `reports` bigint(20) NOT NULL,
                `expenses` bigint(20) NOT NULL,
                PRIMARY KEY (`access_id`)
            )'; 
            $result = $db->query($query) or die($db->error);
            echo 'Store Access Table created.<br>';
        } //creating user level table ends.

Collectives™ on Stack Overflow

Inserting Multiple rows in a MySQL Table : Threading vs BatchUpdate

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related