Slow compared to php

[Ike]

I have a servlet, which essentially uses HttpServlet.service() to take an
SQL query from an applet, and access a database, handling BLOBS, etc.
returning it's results in Vectors, for both the column names, as well as the
data (in the latter case, returning a Vector of Vectors, i.e. a Vector of
rows from teh table). I also use a connection pool, the class for that
included below after the servlet class.

This has been running fine, without ever a problem. HOWEVER, I have a
competing means of accessing the data via php, using an HTTP POST to the php
page, which is much, much faster than my servlet below. I'm wondering if
there isn;t something, glaring, in my servlet code below, which should be
slowing it down.

Can anyone see something below that would be a major problem,
performance-wise, that could be causing this slowdown. I really want to
avoid having to go the php route. -Ike

import java.awt.*;
import java.net.URL;
import java.sql.*;
import java.io.*;
import java.lang.*;
import java.util.*;
import java.text.*;
import javax.servlet.*;
import javax.servlet.http.*;

/**
*
* applet-servlet-communication with streams
* send results of a query (from an applet) back to the applet
*/
public class TestServlet2 extends HttpServlet{
private ConnectionPool connectionPool;
/** if false, uses connection pool otherwise extablished new connection
each call to service()*/
private boolean useRawConnection;
private String driver,url,user,password;

/** Creates a new instance of TestServlet2 */
public TestServlet2() {
}
public void init(ServletConfig config) throws ServletException {
// This method initializes the servlet and only gets call once.
// Allocate all of the servlet resources here.
super.init(config);
useRawConnection=false;
setupsettings();
if(!useRawConnection)
setupConnectionPool();
}

protected void setupsettings(){
Properties props = new Properties();
ClassLoader loader = this.getClass().getClassLoader();
InputStream istream = loader.getResourceAsStream("my.properties");
try{
props.load(istream);
driver = props.getProperty("driver");
url = props.getProperty("url");
user = props.getProperty("user");
password = props.getProperty("password");
} catch ( java.io.IOException ex) {
//TODO: use default values!
}

}

protected void setupConnectionPool(){
try{
connectionPool = new ConnectionPool(driver,url,user,password,
10,//initial connections
50,//max connections
true);//waitIfBusy
}catch(java.sql.SQLException sqlex){}
}

public void destroy() {
// Once this method is called then any instance of this class can be
garbage collected
// Here is where all servlets resources can be deallocated.
if(!useRawConnection)
connectionPool.closeAllConnections();
}

// public synchronized void service (HttpServletRequest request,
HttpServletResponse response)
public void service(HttpServletRequest request, HttpServletResponse
response) throws ServletException, IOException {
execute(request, response);
}

private void execute(HttpServletRequest request, HttpServletResponse
response) throws ServletException, IOException {
try {
String returnString = "OK";
Vector headers = new Vector();
Vector rows = new Vector();
Vector autogenkeys = new Vector(); //for getting the id's of
records added
// get an input stream from the applet
ObjectInputStream inputFromApplet = new
ObjectInputStream(request.getInputStream());
Object connArray[] = (Object[])inputFromApplet.readObject();
inputFromApplet.close();

String SQLString;
SQLString= (String)connArray[4];

// perform query
int numberOfRows = 0;
Statement st = null;
Connection con = null;
try {

if(!useRawConnection){
con = connectionPool.getConnection();
if(con==null){//something happened that may have killed
the pool!
try{
connectionPool.closeAllConnections();
setupsettings();
setupConnectionPool();
}catch(Exception dontstop){}
con = connectionPool.getConnection();
}
}else{
setupsettings();
Class.forName(driver).newInstance();
con = DriverManager.getConnection(url,user,password);
}
if(SQLString.indexOf("?")>-1 && connArray.length>=6){//this
is a prepared statement
ArrayList conntypes=((ArrayList)connArray[5]);
st =con.prepareStatement(SQLString);
for(int u=6;u<connArray.length;u++){
int t =
JavaClassNamesSQLTypes((String)conntypes.get(u-6));
switch(t){
case Types.VARCHAR:
((PreparedStatement)st).setString(u-5, (String)connArray);break;
case Types.INTEGER:
((PreparedStatement)st).setInt(u-5,
((Integer)connArray).intValue());break;
case Types.DOUBLE:
((PreparedStatement)st).setDouble(u-5,
((Double)connArray).doubleValue());break;
case Types.FLOAT:
((PreparedStatement)st).setFloat(u-5,
((Float)connArray).floatValue());break;
case Types.BOOLEAN:
((PreparedStatement)st).setBoolean(u-5,
((Boolean)connArray).booleanValue());break;
case Types.TIME:
((PreparedStatement)st).setTime(u-5, (java.sql.Time)connArray);break;
case Types.DATE:
((PreparedStatement)st).setDate(u-5, (java.sql.Date)connArray);break;
case Types.BLOB:
((PreparedStatement)st).setBytes(u-5,(byte[])connArray);break;
default:;
}
}
((PreparedStatement)st).execute();
}else{
st = con.createStatement();
st.execute(SQLString);
}


ResultSet rsk=null;
try{
rsk=st.getGeneratedKeys();
while (rsk.next())
autogenkeys.addElement(rsk.getObject(1));
}catch(Exception exx){}
if(rsk!=null)
rsk.close();
rsk=null;

int updRows = st.getUpdateCount();
if (updRows > 0) { // update,insert......
returnString = ("Rows affected: " + updRows);
}
else if (updRows == 0) { // no updates
returnString = ("Error, no rows affected");
}
else { // result of a sql-select
ResultSet rs = st.getResultSet();
ResultSetMetaData md = rs.getMetaData();
// headers
int numberOfColumns = md.getColumnCount();
for(int column = 0; column < numberOfColumns; column++)
{
headers.addElement(md.getColumnLabel(column+1));
}

// result
while (rs.next()) {
numberOfRows++;
Vector newRow = new Vector();
for (int i = 1; i <= numberOfColumns; i++) {
if(md.getColumnType(i)==Types.BLOB){
try{
Blob blob = rs.getBlob(i);
if(blob!=null){
int iLength = (int)(blob.length());
byte[] jack = blob.getBytes( 1,
iLength );

newRow.addElement(jack);
}else{
//dont bother - null Objects do not
get added to a Vector
newRow.addElement(new
javax.swing.ImageIcon().getImage());
}
}catch(Exception ex){returnString=
ex.toString();}
} else
newRow.addElement(rs.getObject(i));
}

rows.addElement(newRow);
}
rs.close();
if (numberOfRows == 0) returnString = "no rows
selected";
}
st.close();
if(!useRawConnection)
connectionPool.free(con);
else
con.close();
}
catch (SQLException e) {
if (st != null) st.close();
if (con != null) con.close();
returnString = e.toString();
}

// send objects back to applet
ObjectOutputStream outputToApplet = new
ObjectOutputStream(response.getOutputStream());
outputToApplet.writeObject(returnString); // sql-message
outputToApplet.writeObject(headers); // fieldnames
outputToApplet.writeObject(rows); // result-vector
outputToApplet.writeObject(autogenkeys);
outputToApplet.flush();
outputToApplet.close();
}
catch(Exception e) {
e.printStackTrace();
}
}

public static int JavaClassNamesSQLTypes(String sqltype){
if(sqltype.equals("java.lang.Boolean"))
//return Types.BIT;
return Types.BOOLEAN;
else if(sqltype.equals("java.math.BigInteger"))
return Types.BIGINT;
else if(sqltype.equals("java.math.BigDecimal"))
return Types.DECIMAL;
else if(sqltype.equals("java.lang.String"))
return Types.VARCHAR;
else if(sqltype.equals("java.lang.Integer"))
return Types.INTEGER;
else if(sqltype.equals("java.lang.Short"))
return Types.SMALLINT;
else if(sqltype.equals("java.lang.Byte"))
return Types.TINYINT;
else if(sqltype.equals("java.lang.Double"))
return Types.DOUBLE;
else if(sqltype.equals("java.lang.Float"))
return Types.FLOAT;
else if(sqltype.equals("java.sql.Time"))
return Types.TIME;
else if(sqltype.equals("java.sql.Timestamp"))
return Types.TIMESTAMP;
else
if(sqltype.equals("java.util.Date")||sqltype.equals("java.sql.Date"))
return Types.DATE;
else
return Types.BLOB;
}

}



import java.sql.*;
import java.util.*;

/** A class for preallocating, recycling, and managing
* JDBC connections.
* <P>
* Taken from Core Servlets and JavaServer Pages
* from Prentice Hall and Sun Microsystems Press,
* http://www.coreservlets.com/.
* &copy; 2000 Marty Hall; may be freely used or adapted.
*/

public class ConnectionPool implements Runnable {
private String driver, url, username, password;
private int maxConnections;
private boolean waitIfBusy;
private Vector availableConnections, busyConnections;
private boolean connectionPending = false;

public ConnectionPool(String driver, String url,
String username, String password,
int initialConnections,
int maxConnections,
boolean waitIfBusy)
throws SQLException {
this.driver = driver;
this.url = url;
this.username = username;
this.password = password;
this.maxConnections = maxConnections;
this.waitIfBusy = waitIfBusy;
if (initialConnections > maxConnections) {
initialConnections = maxConnections;
}
availableConnections = new Vector(initialConnections);
busyConnections = new Vector();
for(int i=0; i<initialConnections; i++) {
availableConnections.addElement(makeNewConnection());
}
}

public synchronized Connection getConnection() throws SQLException {
if (!availableConnections.isEmpty()) {
Connection existingConnection =
(Connection)availableConnections.lastElement();
int lastIndex = availableConnections.size() - 1;
availableConnections.removeElementAt(lastIndex);
// If connection on available list is closed (e.g.,
// it timed out), then remove it from available list
// and repeat the process of obtaining a connection.
// Also wake up threads that were waiting for a
// connection because maxConnection limit was reached.
if (existingConnection.isClosed()) {
notifyAll(); // Freed up a spot for anybody waiting
return(getConnection());
} else {
busyConnections.addElement(existingConnection);
return(existingConnection);
}
} else {

// Three possible cases:
// 1) You haven't reached maxConnections limit. So
// establish one in the background if there isn't
// already one pending, then wait for
// the next available connection (whether or not
// it was the newly established one).
// 2) You reached maxConnections limit and waitIfBusy
// flag is false. Throw SQLException in such a case.
// 3) You reached maxConnections limit and waitIfBusy
// flag is true. Then do the same thing as in second
// part of step 1: wait for next available connection.

if ((totalConnections() < maxConnections) &&
!connectionPending) {
makeBackgroundConnection();
} else if (!waitIfBusy) {
throw new SQLException("Connection limit reached");
}
// Wait for either a new connection to be established
// (if you called makeBackgroundConnection) or for
// an existing connection to be freed up.
try {
wait();
} catch(InterruptedException ie) {}
// Someone freed up a connection, so try again.
return(getConnection());
}
}

// You can't just make a new connection in the foreground
// when none are available, since this can take several
// seconds with a slow network connection. Instead,
// start a thread that establishes a new connection,
// then wait. You get woken up either when the new connection
// is established or if someone finishes with an existing
// connection.

private void makeBackgroundConnection() {
connectionPending = true;
try {
Thread connectThread = new Thread(this);
connectThread.start();
} catch(OutOfMemoryError oome) {
// Give up on new connection
}
}

public void run() {
try {
Connection connection = makeNewConnection();
synchronized(this) {
availableConnections.addElement(connection);
connectionPending = false;
notifyAll();
}
} catch(Exception e) { // SQLException or OutOfMemory
// Give up on new connection and wait for existing one
// to free up.
}
}

// This explicitly makes a new connection. Called in
// the foreground when initializing the ConnectionPool,
// and called in the background when running.

private Connection makeNewConnection()
throws SQLException {
try {
// Load database driver if not already loaded
Class.forName(driver);
// Establish network connection to database
Connection connection =
DriverManager.getConnection(url, username, password);
return(connection);
} catch(ClassNotFoundException cnfe) {
// Simplify try/catch blocks of people using this by
// throwing only one exception type.
throw new SQLException("Can't find class for driver: " +
driver);
}
}

public synchronized void free(Connection connection) {
busyConnections.removeElement(connection);
availableConnections.addElement(connection);
// Wake up threads that are waiting for a connection
notifyAll();
}

public synchronized int totalConnections() {
return(availableConnections.size() +
busyConnections.size());
}

/** Close all the connections. Use with caution:
* be sure no connections are in use before
* calling. Note that you are not <I>required</I> to
* call this when done with a ConnectionPool, since
* connections are guaranteed to be closed when
* garbage collected. But this method gives more control
* regarding when the connections are closed.
*/

public synchronized void closeAllConnections() {
closeConnections(availableConnections);
availableConnections = new Vector();
closeConnections(busyConnections);
busyConnections = new Vector();
}

private void closeConnections(Vector connections) {
try {
for(int i=0; i<connections.size(); i++) {
Connection connection =
(Connection)connections.elementAt(i);
if (!connection.isClosed()) {
connection.close();
}
}
} catch(SQLException sqle) {
// Ignore errors; garbage collect anyhow
}
}

public synchronized String toString() {
String info =
"ConnectionPool(" + url + "," + username + ")" +
", available=" + availableConnections.size() +
", busy=" + busyConnections.size() +
", max=" + maxConnections;
return(info);
}
}

 

[Thomas Kellerer]

I have a servlet, which essentially uses HttpServlet.service() to take an
SQL query from an applet, and access a database, handling BLOBS, etc.
returning it's results in Vectors, for both the column names, as well as the
data (in the latter case, returning a Vector of Vectors, i.e. a Vector of
rows from teh table). I also use a connection pool, the class for that
included below after the servlet class.

This has been running fine, without ever a problem. HOWEVER, I have a
competing means of accessing the data via php, using an HTTP POST to the php
page, which is much, much faster than my servlet below. I'm wondering if
there isn;t something, glaring, in my servlet code below, which should be
slowing it down.

[snipped code]

I haven't analyzed your code in detail, but the first thing that I can see as a
potential performance problem is the usage of the Vector class. It has a
synchronized implementation which slows down its usage. If you don't need the
synchronization, try using an ArrayList.

Secondly: are you using an Applet in the PHP implementation as well? If you used
an Applet in the PHP page as well, then I would be very surprised that the PHP
solution is faster.

If your PHP solution does not use an Applet I would think that transferring the
data to the Applet slows down the whole thing.
Additionally, in the applet you most probably build up an AWT or Swing table to
display the data, which takes some time as well (and is totally dependent on the
performance of the client).

What if creat HTML directly in the servlet and send that back to the client,
removing the applet alltogether?


Thomas

 

[Angus Parvis]

data (in the latter case, returning a Vector of Vectors, i.e. a Vector of

I didn't have a closer look on your code, but: be aware that
java.util.Vector is synchronized, thus working with a Vector of Vectors
could really slow you down.
If synchronization is not necessary, try to use java.util.ArrayList -
it's not synchronized and it implements the same interface like Vector
(java.util.AbstractList).

Hope that helps.

Greetings, Angus

 

[Ike]

Thanks Thomas,

I haven't analyzed your code in detail, but the first thing that I can see as a
potential performance problem is the usage of the Vector class. It has a
synchronized implementation which slows down its usage. If you don't need the
synchronization, try using an ArrayList.

Yes, I;ve looked at that, using the non-synchornized ArrayList, but the
applet itself - which has to do some rather involved processing of the
queried data, requires a Vector -- so I would have to convert to Vector at
the applet end.

Secondly: are you using an Applet in the PHP implementation as well? If you used
an Applet in the PHP page as well, then I would be very surprised that the PHP
solution is faster.

No, the applet itself sits in a typical HTML page. The php page merely is
another page which the applet performs an http post to, performs a query on
the database, then does a (php) "print" of that data which my applet then
reads and parses (into those Vectors again).

If your PHP solution does not use an Applet I would think that transferring the
data to the Applet slows down the whole thing.
Additionally, in the applet you most probably build up an AWT or Swing table to
display the data, which takes some time as well (and is totally dependent on the
performance of the client).

Unquestionably. I can even live with that if I could get rid of this
seeming, momentary (1-3 second) hang between the time I send a request to my
servlet, and it starts sending data back.

What if creat HTML directly in the servlet and send that back to the client,
removing the applet alltogether?

Utlimately, I would love to do that. But I have way to much processing to do
of that data itself to go to a completely thin implementation like that.

Thanks Thomas, Ike

 

[Thomas Kellerer]

Ike wrote on 11.08.2004 18:24:

No, the applet itself sits in a typical HTML page. The php page merely is
another page which the applet performs an http post to, performs a query on
the database, then does a (php) "print" of that data which my applet then
reads and parses (into those Vectors again).

So the PHP page serves as the "backend", right?

Does the PHP page apply the same logic as the Servlet, when retrieving the
data?

What database/driver are you using? JDBC drivers tend to be very different
in terms of speed. Maybe there is another driver for your DBMS which is faster.

In my experience DatabaseMetaData is pretty slow for some DBMS. Did you try
retrieving the metadata yourself? I have also made the experience that
ANALYZING Oracle's catalog tables often gives a performance boost while
retrieving metadata.

Did you time your servlet to find out which piece of your code is actually
slowing down the whole thing?

Thomas

 

[Ike]

Ike wrote on 11.08.2004 18:24:

So the PHP page serves as the "backend", right?
Yes


Does the PHP page apply the same logic as the Servlet, when retrieving the
data?

In effect, yes. It performs the SQL query, and packs it up to come back to
the applet

What database/driver are you using? JDBC drivers tend to be very different
in terms of speed. Maybe there is another driver for your DBMS which is faster.

org.gjt.mm.mysql.Driver


In my experience DatabaseMetaData is pretty slow for some DBMS. Did you try
retrieving the metadata yourself? I have also made the experience that
ANALYZING Oracle's catalog tables often gives a performance boost while
retrieving metadata.

Did you time your servlet to find out which piece of your code is actually
slowing down the whole thing?

No - I dont have Tomacat installed locally. I suppose best I install it, and
profile this servlet.

 

[Bryce]

protected void setupConnectionPool(){
try{
connectionPool = new ConnectionPool(driver,url,user,password,
10,//initial connections
50,//max connections
true);//waitIfBusy
}catch(java.sql.SQLException sqlex){}
}

On top of everyone else's suggestions, I gotta ask why you are setting
up your own connection pooling? Tomcat comes with a pretty decent
connection pool implementation.

 

[Ike]

Hmmm......wasn't aware of that at all. Thank You!