aspose file tools*
The moose likes JDBC and the fly likes Database Tuning for Performance Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Database Tuning for Performance" Watch "Database Tuning for Performance" New topic
Author

Database Tuning for Performance

Saurabh Agrawal
Ranch Hand

Joined: Oct 07, 2003
Posts: 244
Hello all,
I am having an insert query which inserts 5000 records one by one in the database table.So it takes lots of time.So i just wanted to know that if i can do this some other way so that all 5000 records can be inserted at one go rarther than 5000 insert statements which doesnt take time.

Another alternate approach will also me appreciated.

So please let me know as its urgent requirement in one of my project.

Saurabh


Success is not doing extraordinary things but doing ordinary things extraordinarily well.
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1121

Saurabh,
If your environment supports it, I suggest you try batch updates. Have a look at Making Batch Updates in the Java Tutorial.

Good Luck,
Avi.
Saurabh Agrawal
Ranch Hand

Joined: Oct 07, 2003
Posts: 244
Hey all,
Has anyone used batch update of JDBC2.0. .If yes can someone tell me which of the following scneraio would be faster and how fast would it be?

Scneario:

I have 1,00,000 records which needs to be inserted in database.

Approach1: Use 1,00,000 insert staements using prepared statement object

Approach2: Use a single bacth update after adding 1,00,000 inserts into a single batch and executing the batch at one go?

I know approach 2 would be faster somehow but can anyone tell me how fast it could get ? Since my projects wants that there should be drastic time difference in inserts when number of records are more.

Please help me up if someone has used such kind of scneraio ? Also any other alternative would be appreciated.

Thanks in advance,
Saurabh
Satish SN
Ranch Hand

Joined: Apr 19, 2005
Posts: 70
hi Saurabh,

i think u should go ahead with secnario 2 for inserting the records.

regarding speed there are some many aspects come into picture like the network speed and concurrent usage of the application by users and stuff like this.

i have suggestion to know the speed u can keep the check it up with the system.getTime() or some java time stamp and u could get how much time it is taking for the insertion int secnario 2

check it up and pls post the time difference ...


Satish SN<br />SCJP 1.4 & SCWCD 1.4
Saurabh Agrawal
Ranch Hand

Joined: Oct 07, 2003
Posts: 244
Originally posted by kumar satish:
hi Saurabh,

i think u should go ahead with secnario 2 for inserting the records.

regarding speed there are some many aspects come into picture like the network speed and concurrent usage of the application by users and stuff like this.

i have suggestion to know the speed u can keep the check it up with the system.getTime() or some java time stamp and u could get how much time it is taking for the insertion int secnario 2

check it up and pls post the time difference ...


Hi Satish,
Yeah i also know the scenario 2 would be better off.But for testing speed i need to write the code all again.The thing is i cant test is now due to some issues.So thats why i wanted to know from someone how efficient the second approach could be?

Morever,If the differece in approach 1 & 2 is not significant then i have to look out for some other alternative.

I am not considering factors like conceurrent access,network speed,etc since they would be same in case of approach 1 & 2, so i am not bothered about them.

So anyone who has actually tested or can confidently tell me what would be the difference in both approaches in some quantitavie figure.

Thanks,
Saurabh
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 39547
    
  27
Why don't you just do a few test runs and measure for yourself? It's not a lot of code to write, and would be more meaningful to you because your database setup and network specifics may be very different from those of other people, and thus the results more accurate in your situation.


Ping & DNS - updated with new look and Ping home screen widget
Saurabh Agrawal
Ranch Hand

Joined: Oct 07, 2003
Posts: 244
Originally posted by Ulf Dittmer:
Why don't you just do a few test runs and measure for yourself? It's not a lot of code to write, and would be more meaningful to you because your database setup and network specifics may be very different from those of other people, and thus the results more accurate in your situation.


Hello ULF,
I appreciate your thoughts but i am having some constraints owing to which i cant change the code myself right now for time being and henceforth i cant test it.

Thats why i asked people who must have used such a scenario or any other alternative they can site me for the same.

Thanks anyways,
Saurabh
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

I found significant performance gains when moving to batch updates/inserts ( approximately 20, 000 records ) using jdbc 2.0 to an Oracle 8i database using the Classes12.zip thin driver. But, it did have some drawbacks. when you process each statement one at a time, you can check that each insert/update was successfully completed, and if there was an error, you could log it, and move on. You could then deal with the few errored situations in a clean up process. As I remember it ( from 3 years ago ), when using a batch update, you can't deal with the errors in a meaningful way. It's almost an all or nothing process. It is difficult if not impossible to log an error that occurred on batch update statement number 17,351. You just lose some flexibility and functionality in the name of performance.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Database Tuning for Performance
 
Similar Threads
ResultSet Manipulation
Fetching and Manipulating Huge data from the database
help me!! about Tomcat and Apache
batch insert to different tables for one transaction
How can I Insert vector elements into a DB??