CODEWITHSUNDEEP
javaperformanceoracle-databasejdbc
aamer
aamer

Reputation: 21

java - jdbc performance

I need one help from you guys regarding JDBC performance optimization. One of our pojo is using jdbc to connect to a oracle database and retrieve the records. Basically the records are email addresses basing upon which emails will be sent to the users. The problem here is the performance. This process happens every weekend and the records are very huge in number, around 100k.

The performance is very slow and it worries us a lot. Only 1000 records seem to be fetched from the database every 1 hour, which means that it will take 100 hours for this process to complete (which is very bad). Please help me on this.

The database server and the java process are in two different remote servers. We have used rs_email.setFetchSize(1000); hoping that it would make any difference but no change at all. The same query executed on server takes 0.35 seconds to complete. Any quick suggestion would of great help to us.

Thanks, Aamer.

Upvotes: 2

Views: 1987

Answers (4)

dublintech
dublintech

Reputation: 17785

My answer to your question, in summary is: 1. Check network 2. Check SQL 3. Check Java code.

It sounds very slow. First thing to check would be to see if you have a slow network. You can do this pretty quickly by just pinging the database server. Or run the database server on the same machine as your JVMM. If it is not the network, get an explain plan for your SQL and ensure you are not doing table scans when you don't need to be. If it is not the network or the SQL, then it's time to check your Java code. Are you doing anything like blocking when you shouldn't be?

Upvotes: 0

sbrattla
sbrattla

Reputation: 5386

Not sure about the Oracle driver, but I do know that the MySQL driver supports two different results retrieval methods: "stream" and "wait until you've got it all".

The streaming method lets you start process the results the moment you've got the first row returned from the query, whereas the other method retrieves the entire resultset before you can start work on it. In cases where you deal with huge recordsets, this often leads to memory exceptions, or slow performance because java hit the "memory roof" and the garbage collector can't throw away "used" records like it can in the streaming mode.

The streaming mode doesn't let you navigate/scroll the resultset the way the "normal"/"wait until you've got it all" mode...

Anyway, not sure if this is of any help but it might be worth checking out.

Upvotes: 0

Vadzim
Vadzim

Reputation: 26180

Use log4jdbc to get the real sql for fetching single record. Then check speed and plan for that sql. You may need a proper index or even db defragmentation.

Upvotes: 0

Chris Aldrich
Chris Aldrich

Reputation: 1922

First look at your queries. Analyze them. See if the SQL could be made more efficient (ie, ask the database for what you want, not for what you don't want -- makes a big difference). Also check to see if there are indexes on any fields in your where and join clauses. Indexes make a big difference. But it can't be just any indexes. They have to be good indexes (ie, that the fields that make up the index provide enough uniqueness for the database to retrieve things appropriately). Work with your DBA on this. Look for either high run time against the db or check for queries with high CPU usage (even if the queries run sub-second). These are the thing that can kill your database.

Also from a code perspective, check to see if you are opening and closing your connections or if you are re-using them. Can make a big difference too.

It would help to post your code, queries, table layouts, and any indexes you have.

Upvotes: 1

Related Questions