This week's book giveaways are in the Refactoring and Agile forums.
We're giving away four copies each of Re-engineering Legacy Software and Docker in Action and have the authors on-line!
See this thread and this one for details.
Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

database hit

 
Hendra Kurniawan
Ranch Hand
Posts: 239
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


I'm quite sure that there is a database hit occuring in line 3. What about line 5 and the other lines? any more database hit on other lines of code? thanks
 
Martin Vajsar
Sheriff
Pie
Posts: 3751
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It is at least in part driver/database dependant. My wild guess would be lines 3 (but some JDBC drivers have a connection pool under the hood!), 5 (but if you're calling it a second time, no hit!), 20, 21, 25, 29 (perhaps), 30.

Why do you need to know? You cannot actually drop any of these lines, so even if there is a "database hit", you can't avoid it.
 
Hendra Kurniawan
Ranch Hand
Posts: 239
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
so, no database hit from 7 - 19 right? I just wanted to confirm it. I'm reviewing my code for improvement, minimizing database hits, so I know what to eliminate. as for acquiring connection, I'm pretty sure it would certainly require database hit, pooling or not.
I have two scenarios:
1. if I put line 19 to line 4, would it add any extra overhead?
2. and what about if I move line 5 - 15 into the try block (squeeze them between line 19 and 20)? would that any extra overhead?
thanks
 
Martin Vajsar
Sheriff
Pie
Posts: 3751
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well, it really, really depends.

Getting a connection from a pool hits the database only if the pool was configured to "verify" the connection it hands out. In any case, connection pool specific again, and not really related to your question.

Question 1: setting the autocommit does not incur DB hit in my opinion. I would always suggest setting it immediately after obtaining a connection, especially if you're using update batching.

Question 2: certainly no additional overhead. I would probably put all code in that method into one try block, so that I could handle all potential SQLExceptions in one place.

I think you're micro-optimizing your code far beyond reasonable levels. If anything, you should read your JDBC driver documentation (if available), there might be some sections related to performance.

And for example, if you were using JDBC Oracle driver which comes with Oracle 10g, using batches does not yield topmost performance, as there is separate, Oracle batching mechanism for that. In Oracle 11g driver, JDBC batching does provide the best performance. Just to illustrate there may be things beyond the code that might affect the performance much, much more.
 
Hendra Kurniawan
Ranch Hand
Posts: 239
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I see. thank you very much for the insight. it helps a lot.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic