-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Coral,
IT Desk wrote:
> The site's jsp page may do up to 7 queries on the database. On each
> query, the statements are these:
>
> Driver DriverDB = (Driver)Class.forName(db_DRIVER).newInstance();
You should certainly skip this step. You can do it once for the entire
container, actually. Consider using a ServletContextListener to make
this call during startup of your webapp. (See below for a better idea).
> Connection ConnDB =
> DriverManager.getConnection(db_STRING,db_USERNAME,db_PASSWORD);
You should certainly be re-using connections -- especially within a
single request. Some webapps require that a connection "belong" to a
certain user, but usually all connections are created equal and can be
shared (though not at the same time ;).
If you need connections to be "owned" by a user, then consider sticking
it in the session and re-using it from there. If connections are not
user-aware, then use a JDBC connection pool. Jakarta Commons-DBCP is
pretty much the standard around here, and you can get a lot of help
using it on this list, too.
The nice thing about using DBCP with Tomcat is that you can set up a
data source in either your global Tomcat configuration or for your
webapp specifically, and Tomcat will take care of making sure that your
driver is loaded appropriately (which means that you don't need to go
calling Driver.newInstance yourself.
> PreparedStatement StatementDB = ConnDB.prepareStatement("SELECT * from
> table");
> ResultSet resultDB = StatementDB.executeQuery();
You can't avoid this :). Hopefully, you are also calling "close" at some
point on that connection, and appropriately handling SQLExceptions and
other things that could result in a connection leak if you aren't
careful. This is soooooo easy to do in JSP that it's not even funny.
> My 2 questions are:
> Does the forName call to load the driver get optimized out? Clearly the
> driver need only load once.
Java and JSP will never optimize anything out. If you call that method
in your code, it will be called at runtime.
> Does the getConnection reuse the same connection that was done in the
> previous call on the same jsp page?
Nope. If it does, it's a feature of the specific driver and you can't
rely upon it. Better to use DBCP and at least you won't be opening a new
connection to the database every single time.
> There are some performance problems and I'm wondering if I should try to
> clean the code up or if the jvm
> does it for me through optimization. It's running on Tomcat 5.5.20 and
> JVM 5.x.
Clean up your code. If you fix your code to do this:
Connection conn = ...;
// issue query 1
// issue query 2
// issue query 3
// issue query 4
// issue query 5
// issue query 6
// issue query 7
conn.close();
You will improve the performance of your application many many many
times more than any amount of configuration possibly can. The JVM will
never optimize anything like what you are doing.
> The client won't pay for any major redesign so I'm looking for something
> small that could make a big impact.
I suspect that calling DriverManager.getConnection is what it killing
you. If you could even re-use the same Connection object across each
call within the same JSP that would make a significant improvement.
Basically, your client is hosed. The code as written expects magic
optimizations to be performed by the JVM which will never happen. That
code requires a re-write.
This is why webapps are usually written with JSP as an interface and
with the major meat of the app in "real" Java classes. For instance, if
you were to take one of these JSP pages and move all of the database
stuff into a class that got a single database connection, performed all
queries, and returned the results to the JSP which would then display
them, then you would have your performance problems largely solved. You
would be using 1 connection per request instead of 1 connection per
query (ouch!).
Adding the use of a connection pool would significantly improve things
as well, though not as much as the previous fix. Some databases take a
long time to establish new connections (Oracle used to be one of them...
not sure anymore but I'm guessing nothing has changed). If you have a
second or two delay to set up a database connection and you are doing
*seven* of them in a single request, then you're talking about 14
seconds of nothing but waiting around for connections and not doing any
work.
A connection pool would allow you to re-use connections across requests,
so you only take the connection-establishment hit once instead of for
every request.
I hope that helps, and good luck.
- -chris
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQFFdHWI9CaO5/Lv0PARAjYhAJ99AihrLuSVVrxXTs3t5WrQONEz8QCgkyAw
0zpLUiD6PwzRkHI+OlDr/Jg=
=x4o6
-----END PGP SIGNATURE-----
---------------------------------------------------------------------
To start a new topic, e-mail: users@tomcat.apache.org
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
