File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Performance and the fly likes Performance issue with Jdbc in OSGI Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Java » Performance
Bookmark "Performance issue with Jdbc in OSGI" Watch "Performance issue with Jdbc in OSGI" New topic
Author

Performance issue with Jdbc in OSGI

Naseem Afzal
Greenhorn

Joined: Oct 04, 2013
Posts: 1



To insert 500 records into oracle my code is taking more than 1 minute.

I am using the below:
1) dbcp connection pool
2) Jdbc, autocommit off
3) preparestatment
4) batchupdate
5) deployed in fuse servicemix

Important thing : Table doesnot have index.

Please help me to improve the performance.

Below is the code snippet..
// Added for Transaction
conn.setAutoCommit(false);
conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
PreparedStatement dataStmt= null;

dataStmt= conn.prepareStatement("insert statement");

for (Value value : valueList){
{
dataStmt.setLong(1,measurementId);
dataStmt.setLong(2,deviceId);
dataStmt.addBatch();
batchCnt++;
if (batchCnt== 100){
dataStmt.executeBatch();
batchCnt=0;
}


dataStmt.executeBatch();

}


dataStmt.close();
conn.commit();
conn.close();





fred rosenberger
lowercase baba
Bartender

Joined: Oct 02, 2003
Posts: 11497
    
  16


1) Why doesn't the table have an index?
2) Use a profiler to determine where the real slowdown is.


There are only two hard things in computer science: cache invalidation, naming things, and off-by-one errors
Luan Cestari
Ranch Hand

Joined: Feb 07, 2010
Posts: 163

Like Fred said, you need to get more deep into the problem, like getting some thread dumps and see which thread is using more CPU, how is your I/O, network and so on. After that you can do more effetive changes driven by the data you just collected, like maybe increase the batch size or change the transaction isolation and etc.


Please, visit me for some cool tech post at www.ourdailycodes.com
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42913
    
  68
What's the connection to OSGi?
Winston Gutkowski
Bartender

Joined: Mar 17, 2011
Posts: 8418
    
  23

Naseem Afzal wrote:Important thing : Table does not have index.

In which case, insertion should be relatively quick, timewise - at least, close to linear with respect to rows added; unless there are foreign key dependencies that you haven't told us about - but I have to admit, I'm no expert on 'prepareStatement', or how it works with inserts.

The only thing I can suggest: Have you tested it with 500 similar inserts, with transactions every 100 rows, in the database itself?

Winston

Isn't it funny how there's always time and money enough to do it WRONG?
Articles by Winston can be found here
Salil Vverma
Ranch Hand

Joined: Sep 06, 2009
Posts: 255

Luan Cestari wrote:Like Fred said, you need to get more deep into the problem, like getting some thread dumps and see which thread is using more CPU, how is your I/O, network and so on. After that you can do more effetive changes driven by the data you just collected, like maybe increase the batch size or change the transaction isolation and etc.


Hey Luan,

I believe, isolation levels have performance impact on select queries not on insert queries.
Can you please help me in understanding how isolation level impacts the insert query performance ?


Regards
Salil Verma
Luan Cestari
Ranch Hand

Joined: Feb 07, 2010
Posts: 163

Sure =)

I'm considering the example provided is an simplification of his scenario, where the real one can have queries inside the insert statement, which will have that issue as you know. In Oracle documentation they say something very similar http://docs.oracle.com/cd/B28359_01/server.111/b28318/consist.htm

Hope it helped

Best regards,
Luan
Salil Vverma
Ranch Hand

Joined: Sep 06, 2009
Posts: 255

Luan : Thanks for sharing the link.

Naseem : Oracle documentation mentions following thing for read-committed isolation -
Many high-performance environments with high transaction arrival rates require more throughput and faster response times than can be achieved with serializable isolation. Other environments that supports users with a very low transaction arrival rate also face very low risk of incorrect results due to phantoms and nonrepeatable reads. Read committed isolation is suitable for both of these environments.


Considering this, I think, this isolation level is worth trying to check for performance improvement.

Please let us know how your application responded after changing the batch size and isolation levels

Regards
Salil Verma

Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

This being Oracle, I'd suggest turning on sql trace with wait events to see where do the waits occur. It may be problematic in production environment, though. Also, make sure that you're using the latest Oracle JDBC driver, full support for JDBC batching was implemented only in the 11g version of the driver. Also trying other batch sizes (I'd even try smaller number) might be worth it.

In Oracle, isolation levels are not achieved by locking, and given that the original code contains INSERT INTO ... VALUES statement, not INSERT INTO ... SELECT one, I'd say that isolation level should not have any impact on its speed (writes are always done using the "current" view of the data, not the consistent view).
Salil Vverma
Ranch Hand

Joined: Sep 06, 2009
Posts: 255

trying other batch sizes (I'd even try smaller number) might be worth it.


Hey Martin,

As for as I know performing operation in batch improves the performance minaly because of two reasons -
1- Connection creation is an expensive operation, application performance improves because of less number of times connection is created.
2- When we call execute batch, a set of operation are sent to database. As in most of the cases database use to be in seperate machine in network. The impact of network latency in complete opration gets subsided if operations are sent in batch rather than non-batch.

Considering these reasons, I believe, choosing a bigger batch size (not a smaller one) will improve the performance.

Can you please suggest, if you are referring to some specific scenario or some other reasoning where overall application performance would get improved by reducing the batch size ?
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

The first reason is not relevant. Connection pooling reduces the overhead of creating new connections, but in this case, we're working on a single connection already.

The second reason is relevant. We indeed want to avoid network latency. But there are other factors at play. If we hoard a big lump of data before sending them to the database, we might hit the network bandwidth instead. In an ideal case, we'd send the batch to the database every time it fills one packet of data (this would probably be optimal from the network perspective alone). I believe that in reality the optimal batch size will be one which will create several packets of data on every go, because sizing the batch so that it always precisely fills one network packet is not possible. However, some operations in the database (writing the data to redo logs is certainly relevant to this case) happen asynchronously; if you send too much data in one go, you might have to wait for these processes needlessly. You actually want to keep all parts - the application, the network, the database - busy all the time.

In any case, there's a law of diminishing returns clearly at play. Increasing batch size from 1 to 10 cuts down the time much more than increasing it from 10 to 20. And since I've experienced exceptions when setting the batch size too high, I'd rather settle for a smaller number that provides reasonable performance instead of trying to shave one more millisecond by increasing the batch size tenfold.

In this particular example, I don't think the problem lies in the batch size. Perhaps I shouldn't have mentioned it at all. At this point, using sqltrace is probably the easiest way to see what's really happening there.
Luan Cestari
Ranch Hand

Joined: Feb 07, 2010
Posts: 163

Another approach you can use to improve the performance is to run the batch in parallel (creating some threads). The number of threads is bonded by the throughput your database and your machine.
Salil Vverma
Ranch Hand

Joined: Sep 06, 2009
Posts: 255

Hey Nazeem,

Are you getting this performance issue in production or development environment too?
Are you noticing such performance all the times or some times, it much faster than you explained?
Do you have multiple database on the same machine in which your database resides ?
Do you see your database processor having almost 100% CPU utilization when such performance is noticed ?

I think, answers of these question might help you choosing the right direction to solve your problem.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Performance issue with Jdbc in OSGI