Last week, we had the author of TDD for a Shopping Website LiveProject. Friday at 11am Ranch time, Steven Solomon will be hosting a live TDD session just for us. See for the agenda and registration link
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Ron McLeod
  • Jeanne Boyarsky
  • Tim Cooke
Sheriffs:
  • Liutauras Vilda
  • paul wheaton
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Stephan van Hulst
  • Carey Brown
  • Frits Walraven
Bartenders:
  • Piet Souris
  • Himai Minh

Efficient database connections?

 
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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!
 
Ranch Hand
Posts: 2874
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks, Adeel! I'll look into that.
 
Ranch Hand
Posts: 161
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Sheriff
Posts: 13411
Firefox Browser VI Editor Redhat
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Do not set lab on fire. Or this tiny ad:
free, earth-friendly heat - a kickstarter for putting coin in your pocket while saving the earth
https://coderanch.com/t/751654/free-earth-friendly-heat-kickstarter
reply
    Bookmark Topic Watch Topic
  • New Topic