Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Efficient database connections?

 
Louis Fatham
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm new to programming in Java, so please forgive what might be a dumb question.

I wrote an application that makes many calls to a relational database. When I first wrote the program, I didn't take a lot of care to use code efficiently, and ended up repeating the same statements over and over throughout, making a single database connection to perform multiple select statements, and return multiple resultsets. And the thing ran like a champ.

But since I'm trying to go back and clean it up, I decided to move all the database calls to a separate class file (makes sense, I thought). Since the calls would use the same format, but use the resultsets differently, I decided to return the resultset back to the calling program, and scan it there. That works, however the processing speed fell massively. I have verified that I am closing the resultsets after every call (and subsequent processing of that resultset in the calling program). I think it's probably due to the fact that before, I was limiting the number of connection statements by having the db-logic inline with the main code, whereas now, every database call requires a new connection. Could that be the cause of the massive slowdown?
If that's so, can you suggest how I should organize the logic? Is it normal to isolate the database calls to a class of their own? Is it orthodox to return a resultset to the calling program, if the processing of the resultset differs for each statement performing the database lookup?
I'm just trying to write good code....

Thanks!
 
Adeel Ansari
Ranch Hand
Posts: 2874
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Louis Fatham:
But since I'm trying to go back and clean it up, I decided to move all the database calls to a separate class file (makes sense, I thought).

Nice thought.


Every database call requires a new connection. Could that be the cause of the massive slowdown?


May be, but not massive. You can implement connection pooling mechanism.


Is it normal to isolate the database calls to a class of their own?


It is considered as best practice.


If that's so, can you suggest how I should organize the logic? Is it normal to isolate the database calls to a class of their own? Is it orthodox to return a resultset to the calling program, if the processing of the resultset differs for each statement performing the database lookup?
I'm just trying to write good code....


I suggest you to read a bit about DAO (Data Access Object) and VO (Value Object) in order to get the best.

Wish you luck.
 
Louis Fatham
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks, Adeel! I'll look into that.
 
Joe Nguyen
Ranch Hand
Posts: 161
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Perhaps, you haven't closed the connection soon enough. Consider creating a disconnected resultset (Value Object), using connection pool, and preparedStatement caching.
 
Ben Souther
Sheriff
Posts: 13411
Firefox Browser Redhat VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Getting the connection is usually the biggest bottle neck your app will face (unless it's a web app, in which the internet will be #1).

If you were re-using a single connection, your first shot was probably much more efficient that the cleaned up code.

As Adeel has stated, your best bet is to implement a connection pool.
http://jakarta.apache.org/commons/dbcp
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic