Re: Pooling Prepared Statements

Hi!

First at all. I apolagize because a made a mistake: it's 1 statement ->
1 resultset (thanks again Michael ;) )

Second. I you are correct when you said:

"Then wrap the connection in a class along with the prepared statement.
This would avoid the creation of the statement object but will still
use many connections."

I recommed you to see some code example about preparedstatement cache.
The usual is wrapping the connection and cache (usually a simple Hashtable) in MyConnection, for example.
You write all the methods that are usually used in a connection ( getPreparedStatement, executeQuery, etc).
But when someone try to execute a query, before you really execute it, you verify if its not in the cache.
Again, usally the cache is an Hashtable and the keys are SQL String (the query) and the values are the statements.
when you try to execute the query it will do something like this:

public synchronized PreparedStatement prepareStatement (String sql)
throws SQLException {
PreparedStatement preparedStmt;
preparedStmt = (PreparedStatement)preparedStmtCache.get(sql);
if (preparedStmt != null) {
preparedStmt.clearParameters();
}
else {
if (stmtCache.size() >= maxPreparedStmts) {
String key = (String)stmtCache.keys().nextElement();
((PreparedStatement) stmtCache.remove(key)).close();
}
preparedStmt = connection.prepareStatement(sql);
stmtCache.put(sql, preparedStmt);
}

return preparedStmt;

This is an example based in the preparedStatement cache in the Enhydra com.lutris.appserver.server.sql.StandardDBConnection.java .

The tip to not use to much db conncetion it's to use connections pool.
When you need a connection you ask the pool to allocate one for you and when dont use connection anymore (you finished processing the result of your query ) you realese the conncetion. The release of the connection dont close it but give it back to the pool.
The big advantages of using a pool:

- you need fewer conncetions because as soon as the connection is realesed, it's ready to be allocated by another thread.
- you dont need to be allways creating and destroying connections: the pool manage it for you.

Almost all (may be all) the application Servers use database conncetion pooling because they usally have a lot o threads that made very quick queries so they can easily share the connectins using the pool.

As an example, at the moment i have a site with 120 servlets (1400 active sessions) and they are using only 30 connections to the db.

Regards.

JP

G.Nagarajan wrote:

>hi,
>Thanks for your reply. Better I knew this early, else i would have
>spent hours trying to understand what went wrong!
>
>I have some more questions.Is this a restriction of postgres,
>the database driver or JDBC?
>
>I also have lots of code like this:
>
>sql = "select deptno, name from dept"
>rs = stmt.executeQuery( sql );
>while( rs.next() )
>{
> rs2 = "select empno, empname from emp where deptno = 1".
> while( rs2.next() )
> {
> // sometimes rs3..
> }
> rs2.close()
>}
>rs.close()
>
>they seem to work fine without any error messages. Here i am actually
>having two resultsets open in the same connection, but on different
>tables. Does it mean a bug waiting to occur at the right time?
>
> So, i think for implementing the prepared statement cache, i have to
>create the statements for each connection. something like
>
> connection1 - statement1, statement2, statement3
> connection2 - statement1, statement2, statement3
> connection3 - statement1, statement2, statement3
>
> Then wrap the connection in a class along with the prepared statement.
>This would avoid the creation of the statement object but will still
>use many connections.
>
>Regards,
>Nagarajan.
>
>
>
>>-----Original Message-----
>>From: pgsql-jdbc-owner(at)postgresql(dot)org
>>[mailto:pgsql-jdbc-owner(at)postgresql(dot)org]On Behalf Of João Paulo Caldas
>>Ribeiro
>>Sent: Thursday, August 29, 2002 7:45 PM
>>To: G.Nagarajan
>>Cc: pgsql-jdbc(at)postgresql(dot)org
>>Subject: Re: [JDBC] Pooling Prepared Statements
>>
>>
>>Hi!
>>
>>Im sorry but: 1 connection -> 1 resultset at time.
>>You can have many statement associated to a connection but when you
>>execute the statemente you can only have 1 resultset associated to the
>>connection at time.
>>You can execute 2 statement using the same connection but you have to:
>>
>>-> execute statement1
>>-> get the resultset1 and use it
>>-> close resultset
>>-> execute statement2
>>-> get the resultset2 and use it
>>-> close resultset
>>
>>
>>If you still using the resultset1 when you execute the statement2 you'll
>>destroy it.
>>This is why i told to close the resultset.
>>Rule is : Every time you finish using a statement or a resultset close it.
>>Exception: if you want to keep a cache of statements you only close them
>>when you remove them from the cache or before closing the dbconnection.
>>
>>If think you are trying to make a statement cache. Take a look to the
>>Enhydra (www.enhydra.org) DBLayer. They use preparedstatement cache for
>>every dbconnection.
>>
>>Regards.
>>João Paulo Ribeiro
>>
>>
>>G.Nagarajan wrote:
>>
>>
>>
>>>hi,
>>>i am planning to implement a pooling system for Prepared Statements.
>>>It will open a single connection during initialization and create
>>>many prepared statements using this connection. The connection will
>>>be always kept open so that the prepared statements are valid. To
>>>speed up things, there may be more than one prepared statement for
>>>the same query, ie,
>>>
>>>stmt1 = "select from table where field = ?";
>>>stmt2 = "select from table where field = ?";
>>>
>>>Will there be any issues when two statements are executed for the
>>>same table using the same connection? has anyone tried doing something
>>>like this or is there any open source tool which can do this?
>>>
>>>This will greatly improve the efficiency of the system as most
>>>
>>>
>>of the time,
>>
>>
>>>the connections are used for executing the same statements. Pooling the
>>>statements instead of the connection will save the processing needed
>>>for creating the statement objects for every query.
>>>
>>>Regards,
>>>Nagarajan.
>>>
>>>
>>>---------------------------(end of broadcast)---------------------------
>>>TIP 2: you can get off all lists at once with the unregister command
>>> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>>>
>>>
>>>
>>>
>>>
>>>
>>--
>>------------------------------------------------------------------
>>----------
>>MobiComp - Mobile Computing & Wireless Solutions
>>phone: +351 253 305 250 fax: +351 253 305 251
>>web: http://www.mobicomp.com
>>------------------------------------------------------------------
>>----------
>>
>>
>>
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 5: Have you checked our extensive FAQ?
>>
>>http://www.postgresql.org/users-lounge/docs/faq.html
>>
>>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: Have you searched our list archives?
>
>http://archives.postgresql.org
>
>
>
>

--
----------------------------------------------------------------------------
MobiComp - Mobile Computing & Wireless Solutions
phone: +351 253 305 250 fax: +351 253 305 251
web: http://www.mobicomp.com
----------------------------------------------------------------------------