Discussions

General J2EE: connection pooling question

Bookmark and Share Get thread feed

  1. connection pooling question (4 messages)

    • Posted by: Jonny Wray
    • Posted on: November 14 2002 15:48 EST
    I have a question that's been nagging at me for a while. I'm currently using connection pooling in a servlet/jsp based web application. The pooling is implemented by the
    application server, and I get a get a connection via a specific datasource.

    When the application needs a particular set of data, a connection is obtained from the pool, statements created and executed, result set processed then all statements are closed and the connection returned to the pool. All this works fine.

    However, how does this approach match with the use of PreparedStatements? With this approach I need to create the ps everytime I obtain a connection from the pool, as opposed to reusing the ps and obtaining their benifits. I might as well be using a regular statement.

    Anyone seen this addressed anywhere, or am I missing something obvious (wouldn't be the first time)?

    Jonny
    Reply to this

    Threaded Messages (4)

  2. connection pooling question[ Go to top ]

    Jonny,

    Interesting question. The answer is that PreparedStatement is good for security purposes and that you still can use a cache of PreparedStatements, but you should have a cache of opened connections first (and don't forget that different databases deal differently with transactions on opened connections - some of them may be will not like transactions opened for long time) and create your own implementation of pooled datasource to do that. However, some of the appservers/containers (WLS for example) can do this management inside itself, so you don't have to care about that. That depends on particular container's DataSource implementation.

    I think that prime task of PreparedStatement is to provide security (in case of type-checking of arguments before they're sent to the database). Also, you can easily reuse it in the vicinity of one connection (I mean that you can have one opened connection and use the same PreparedStatement multiple times with it - but better during only one user request, if otherwise - refer to the passage above).

    Alex
    Reply to this Reply to original

  3. connection pooling question[ Go to top ]

    If you look at JBoss as en example, the datasource pooling also has a setting to cache prepared statements "behind the scenes" so even if you think you're creating a new one each time, it's just re-using an already open one. This is probably available in other servers as well, but this is the only example I know of.
    Reply to this Reply to original

  4. connection pooling question[ Go to top ]

    Thanks for the replys.

    I wasn't thinking about the security aspect of the prepared
    statements (obviously this is still there even if you are
    creating new ones every time) but rather the performance
    advantage of the database being able to "compile" the statement
    once and reuse the execution path.

    My apps don't really have a performance bottleneck which is why this has only been nagging me - no real need to fix it by, as you mention, implementing my own connection pooling.

    I will look into the app server documentation more to see if it mentions ps caching as JBoss does.

    Jonny
    Reply to this Reply to original

  5. connection pooling question[ Go to top ]

    Caching prepared statements definitely improves performance. For example in Weblogic ps cache may give you up to 40-60% of speed increase. Though, implementing ps cache is much more complex task compared to let say connection pooling. So I'd check with the application server vendor. It's quite possible it's implemented already.
    Reply to this Reply to original