The primary performance benefit to be gained from prepared statements is from the SQL package that some DB systems support. Your first step would be to investigate this with your DB and find out if package support is offered - if not, prepared statements will probably offer you some organizational and security benefit but little more. SQL package support works like so: When a prepared statement is passed to the DB the sql optimizer kicks in and figures out the best way to run the query. If the query is a prepared statement rather then just an SQL string, the optimizer takes a bit more time on this procss to ensure that it's done it right. Then it writes the prepared statement and the optimization results to a cache file where it will remain until the optimizer decides that it requires updating. On every call afterward with the same prepared statement, the optimizer doesn't need to kick in, it just uses the optimization from the cache. You definitely see a performance gain when this happens, especially under heavy load because that is the condition under which memory and processing power are taxed and less available for the optimizer. Again, I use DB2 on iSeries and it supports SQL packages but I don't know what other platforms (if any) also support them.
To answer your original question, using the connection pool once it is set up in Tomcat is as simple as accessing the JNDI datasource within your code - dbcp handles the rest for you automatically. You will know that you've done it correctly if you display network resources and see only the number of connections to the DB as you have configured for the pool. However, at Tomcat's startup you may see some additional connections that Tomcat used to authenticate to the DB if you have security set up and you may also see another spare connection in there if you're using session persistance to the same db. So for example, if everything is working correctly, on a Windows box you could do a netstat -a from cmd prompt and see a list of open tcp connections (you can do this on other platforms too, but I don't know the exact syntax on any other then windows). You should see the name or IP address of your db in there several times. If you configured your pool to use 5 db connections to start with, you should see 5 ESTABLISHED connections to the db. If security is set up you may also see several connections to the db on TIME-WAIT (or some variant syntax). These will go away in a short time. Now if you open a browser and click around your webapp a few times, ensuring that you have caused some db accesses you should be able to do netstat -a again and still see only the same 5 connections as before. If there are more TIME-WAIT connections than there were previously, something is wrong and connection pooling is not being used properly. -marc --- Khawaja Shams <[EMAIL PROTECTED]> wrote: > Hello, > Benchmarking is precisely what I am trying to > do. However, if I > prepare a statement every time I need to execute the > statement, it would not > be an accurate measurement of optimizations that > preparedStatements can > offer. In fact, this would probably be worse than > just executing the > statement once and for all. This is why I am > wondering if this is possible > so I can test for performance before integrating > this with our solution. > PreparedStatement can POTENTIALLY offer a great deal > of optimizations, if > used PROPERLY. > Also, from what I have read so far, pre > compiled statements do a very > nice job of preventing SQL Injections. I am > certainly open to hearing > better suggestions from you. Although you sound > like a manager, kindly note > that I am not your employee. > > Sincerely, > Khawaja Shams > > > On 1/13/06, George Sexton <[EMAIL PROTECTED]> > wrote: > > > > Let me just ask one simple question: > > > > Have you benchmarked your queries with prepared > and unprepared statements > > to > > see if there is really a performance difference? > > > > Many people blindly follow "common knowledge" > about performance, and this > > "common knowledge" is often wrong. People waste > time and energy creating > > "optimized solutions" to issues that weren't > broken. > > > > If you were my employee, I would first tell you to > prove to me that there > > is > > a genuine difference, and that the difference was > large enough to justify > > the additional maintainability/stability issues > that might arise out of > > any > > solution. > > > > Lord Kelvin said: > > > > "If you can not measure it, you can not improve > it." > > > > > > George Sexton > > MH Software, Inc. > > http://www.mhsoftware.com/ > > Voice: 303 438 9585 > > > > > > > -----Original Message----- > > > From: Khawaja Shams [mailto:[EMAIL PROTECTED] > > > Sent: Thursday, January 12, 2006 11:12 PM > > > To: Tomcat Users List > > > Subject: Re: PreparedStatement w/ Connection > Pooling > > > > > > I am guessing the main attribute to change is > the following: > > > poolPreparedStatements="true" > > > > > > Suppose that this is set to true, how do I > access these > > > pooled statement? My > > > setup is exactly as you described, so this > modification seems > > > minor, but I > > > would appreciate guidance on how to get a hold > of these > > > pooled prepared > > > statement. Thanks for your help. > > > > > > Sincerely, > > > Khawaja Shams > > > > > > On 1/12/06, Marc Richards <[EMAIL PROTECTED]> > wrote: > > > > > > > > If you're already using pooling then I imagine > you > > > > already have a resource defined for the pool > in either > > > > server.xml or in the context xml file. To > pool > > > > prepared statements, which is desireable, you > should > > > > configure it to look something like this in > Tomcat > > > > 5.5: > > > > > > > > > > > > <Resource > > > > type="javax.sql.DataSource" > > > > auth="container" > > > > name="jdbc/cPool" > > > > username="user" > > > > password="pwd" > > > > > > > > > driverClassName="com.ibm.as400.access.AS400JDBCDriver" > > > > url="jdbc:as400://serverName;extended > > > > dynamic=true;package > > > > library=pkgLib;package=pkgName;package > > > > add=true;package cache=true" > > > > initialSize="5" > > > > removeAbandoned="true" > > > > removeAbandonedTimeout="600" > > > > maxActive="0" > > > > maxIdle="10" > > > > minIdle="2" > > > > maxWait="10" > > > > poolPreparedStatements="true" > > > > logAbandoned="false" > > > > /> > > > > > > > > Note that in this example I'm using an IBM > iSeries DB2 > > > > database driver, not my sql, but the rest of > the > > > > config should be close to the same with the > exception > > > > that on a production machine you'll want to > have your > > > > initialSize, maxIdle and minIdle set much > higher > > > > depending on the number of concurrent > connections that > > > > you expect to make. > > > > Also note the url parameter - DB2's jdbc > driver and > > > > database support dynamic package support with > prepared > > > > statements, which can sometimes offer huge sql > > > > performance gains due to the system's ability > to offer > > > > better optimization for prepared statements > and cache > > > > the optimization so that the SQL optimizer > need not be > > > > initialized for every subsequent statement. I > don't > > > > know if my sql supports this or not, but you > would > > > > definitely want to look into it. > > > > Lastly, note that this configuration is only > > > > applicable to Tomcat 5.5 and possibly later > versions. > > > > Tomcat 5.0 and previous require a different > syntax, > > > > using a more structured xml format, which can > be > > > > reviewed here: > > > > > > > > > > > > http://tomcat.apache.org/tomcat-5.0-doc/jndi-datasource-exampl > > > es-howto.html > > > > The best possible database configuration is as > > > > described above - using connection pooling > with > > > > prepared statements and SQL package support - > in cases > > > > where stored procedures are more difficult to > manage. > > > > Stored procedures work great when you have > several > > > > queries that are required to run in sequence > (and > > > > usually offer better performance in such > cases), but > > > > may end up a tad overkill from a maintenance > > > > perspective when you have a few isolated > queries here > > > > and there. > > > > > > > > -marc > > > > > > > > > > > > > > > > > > > > --- George Sexton <[EMAIL PROTECTED]> > wrote: > > > > > > > > > Generally speaking, if you were using most > SQL > > > > > databases the approach would > > > > > be to use a stored procedure. The stored > procedure > > > > > compilation saves the > > > > > various query plans as part of the data of > the > > > > > stored procedure. I doubt > > > > > MySQL does it, but you could ask on their > > > > > development list. > > > > > > > > > > George Sexton > > > > > MH Software, Inc. > > > > > http://www.mhsoftware.com/ > > > > > Voice: 303 438 9585 > > > > > > > > > > > > > > > > -----Original Message----- > > > > > > From: Khawaja Shams > [mailto:[EMAIL PROTECTED] > > > > > > Sent: Thursday, January 12, 2006 2:19 PM > > > > > > To: Tomcat Users List > > > > > > Subject: PreparedStatement w/ Connection > Pooling > > > > > > > > > > > > Hello all, > > > > > > I am working on a web application that > will > > > > > manage > > > > > > assignments/students/classes for the > teachers at a > > > > > school. > > > > > > Most of my pages > > > > > > in the application need to access our > mysql > > > > > database very > > > > > > frequently. For > > > > > > this and for other security reason, I > would like > > > > > to use > > > > > > preparedStatements. > > > > > > However, from what I understand, > preparedStatement > > > > > are > > > > > > prepared on top of a > > > > > > connection. If I am using connection > pooling, > > > > > this seems to > > > > > > be useless for > > > > > > performance as I would be preparing a > statement > > > > > each time I get a > > > > > > connection. Is there any way to get > around this? > > > > > Is there > > > > > > such thing as a > > > > > > preparedStatement pool? Would you > recommend > > > > > implementing this > > > > > > on my own? > > > > > > Thanks for your time and help. > > > > > > > > > > > > > > > > > > Best Regards, > > > > > > Khawaja Shams > > > > > > > > > > > > > > > > > > > > > > > > > > > > > --------------------------------------------------------------------- > > > > > To unsubscribe, e-mail: > > > > > [EMAIL PROTECTED] > > > > > For additional commands, e-mail: > > > > > [EMAIL PROTECTED] > > > > > > > > > > > > > > > > > > > > > > > __________________________________________________ > > > > Do You Yahoo!? > > > > Tired of spam? Yahoo! Mail has the best spam > protection around > > > > http://mail.yahoo.com > > > > > > > > > > > > --------------------------------------------------------------------- > > > > To unsubscribe, e-mail: > [EMAIL PROTECTED] > > > > For additional commands, e-mail: > [EMAIL PROTECTED] > > > > > > > > > > > > > > > > > > --------------------------------------------------------------------- > > To unsubscribe, e-mail: > [EMAIL PROTECTED] > > For additional commands, e-mail: > [EMAIL PROTECTED] > > > > > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
