• Post Reply Bookmark Topic Watch Topic
  • New Topic

preferred way to hold on database connection  RSS feed

 
Hadi Zeftin
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Howdy, first post on ranch,

first of all, I tried to search on the forum (googling also), and still cannot find anything that could answer my curiosity, so here goes...

what is the most prefered way to hold on to database connection (using connection pool).

my current approach, everytime a request come a service class is instantiated with a connection from the pool



I'm thinking something like this might work, the idea is the servlet will hold one connection without getting/creating it each time request come. also service class use a lot of prepared statement, instantiating it once and using it many time will surely improve performance and stuff




the problem with the second listing is that connection will expired, especially when the servlet is idle after sometime, so what would you guys do ? what is the best way to hold a connection (or recreating/re-getting it when needed) ? most importantly am I getting this right ?

yes I'm new with servlet and all, I hope I've made my self clear, English is not my first language
 
Ben Souther
Sheriff
Posts: 13411
Firefox Browser Redhat VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You may actually hurt performance by holding the connection this way.

Servlets are multithreaded.
If the servlet has only one connection and multiple requests come in at once, they're either going to have to queue up for that single connection or, they will crash if there is no queueing mechanism in place.

If you're not actually having performance issues, I would avoid trying to optimize this way so early.
 
Hadi Zeftin
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
thanks ben,

yeah, I didn't have any performance issue until now (although I'm sure things can be a little more efficient here). it's just the service class, I'm using a lot of prepared statement because that what most people suggest to me more safe etc.. I'm thinking if I recreate the service class for each request, it'll beat the whole purpose of prepared statement.

is there any suggested pattern for this kind of spec of having a service class in a servlet which rely on some resource -database- without building it for each request.

thanks anyway guys.
 
David Newton
Author
Rancher
Posts: 12617
IntelliJ IDE Ruby
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Object creation is *fast*. Connection creation is *slow* (relatively). Pooling should be far and away the biggest performance boost you'll get.

You definitely do *not* want to hold on to connections, for a variety of reasons.

That said, there's rarely a need to create a new service class, unless you've coded them in a stateful way--they can usually be singletons. Performance-wise there might not be any compelling reason, though.
 
Hadi Zeftin
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
thanks David,

yes, I understand, supposedly I could code my service in a stateful way, say as a singleton, each time the service trying to serve a request it takes the connection as argument too, something like


but the service class has so many query/dml and all, it use a prepared statement, if the service using different connection each time it trying to serve a request, the prepared statement will need to be re-created right ? or should i just drop the prepared statement ?

suggestion is very welcome (pseudo code would be awesome though),


thanks guys.
 
Ben Souther
Sheriff
Posts: 13411
Firefox Browser Redhat VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I wouldn't drop the prepared statements.
There are a lot of advantages to using them besides the slight performance gain you get when you can re-use one.

They take care of escaping quotes for you.
They help protect you from SQL injection.

And, in cases where you need to perform a database action within a loop, they can still give you the performance boost by cutting out the need to prepare the execution plan with each iteration.

What were you expecting to gain by dropping them?
 
Hadi Zeftin
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
yes, exactly, what I know is that if the statement was prepared then extra database call was made or something like that, the point is it cost more instead of just create the plain statement and execute them. since I should not hold to a connection in a servlet instance, then most of the time those prepared statement will only be used once, because each request will probably use a different connection (unless of course if theres a loop that using the same statement inside the service methods). I'm not sure in what degree the performance will increase if i'm using plain statement instead of prepared one, again those are thoughts without actual research.


Ok, now I'm not sure whether my question still on the right sub-forums, anyway basically my curiosity is, is my first listing (my current approach, refer to my first post in this thread) are OK, I mean regardless the performance boost I'll get which may not so significant, since I'm not so familiar with all the available option for servlet and stuff, how would you guys do it.


thanks so much for the response guys.
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!