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, Sep 09 '14 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, Sep 10 '14 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, Sep 10 '14 at 15:32

score 1 · Accepted Answer · answered Sep 09 '14 at 19:32

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 09 '14 at 19:32

ares

3,921
4
31
54

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. – Ani Sep 10 '14 at 15:35
1

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](http://stackoverflow.com/questions/2227596/jdbc-batch-insert-outofmemoryerror) question explains well about batch insert. – ares Sep 10 '14 at 16:10

JDBC batch insert - control the insert speed

1 Answers1