CODEWITHSUNDEEP
javamysqlmultithreadingjdbcprepared-statement
Suryakant Pandey
Suryakant Pandey

Reputation: 374

Inserting Multiple rows in a MySQL Table : Threading vs BatchUpdate

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 .

Upvotes: 1

Views: 1428

Answers (2)

Umair Rasool
Umair Rasool

Reputation: 41

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.

Upvotes: 0

Bikramjit Rajbongshi
Bikramjit Rajbongshi

Reputation: 452

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

Upvotes: 1

Related Questions