JDBC batch insert - control the insert speed

Question

I have a simple JDBC batch insert program. I am fetching data from one database table and inserting these records to a table in another database after processing. But the target database is having some performance issues. So the target db admin requests an option in our program to control the number of records inserted per second.

It may not be dynamic. If he wants to change the insert speed, he can change the config property accordingly and restart the application.

Our source and target databases are MQ SQL databases.

Is it possible?

Throttling any sequence of commands isn't necessarily difficult.. I'm not sure I know what you're asking for advice on. It would just be a matter of staging the inserts such that only a certain amount of them could be written to the target db per second, and make that value configurable at run-time. — user3062946
– user3062946, Commented Sep 9, 2014 at 16:52
@user3062946 You are correct. It should be configurable. But not necessarily be at run-time. What I am looking for is a configuration at JDBC driver level to control the number of inserts flushed to database per second. — Ani
– Ani, Commented Sep 10, 2014 at 15:30
@Luiggi Mendoza I know that its odd, but this is our requirement because of some limitations in target database. Can you tell me how to do this? — Ani
– Ani, Commented Sep 10, 2014 at 15:32

ares · Accepted Answer · 2014-09-09 19:32:22Z

1

You can put the thread to sleep once you've inserted your specified number of records.

 int recordsPerSecond = 100; int totalRecords = 1000; long sleepTime = 900; // in milis (Assuming that 100 mili seconds lapsed in inserting 100 rows) for(int i=1; i<=totalRecords; i++){ if(i%recordsPerSecond==0) Thread.sleep(sleepTime); DAO.insert(); }

answered Sep 9, 2014 at 19:32

ares

4,4236 gold badges38 silver badges65 bronze badges

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

2 Comments

Ani Over a year ago

Thank you for your answer. Do you know is there any configuration available at JDBC driver level to control the number of inserts flushed to database per second.

ares Over a year ago

I'm not aware if any JDBC driver implements this sort of thing. But alternatively you can pause your program to reduce load on the target server. This question explains well about batch insert.

Collectives™ on Stack Overflow

JDBC batch insert - control the insert speed

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related