Search code examples
javaperformancetomcat6ojdbc

SQL execution time much slower in a Tomcat Servlet than in a normal Java program


For inexplicable reasons however, this morning the performance increased for two of my Queries that used to be slow. I have no idea why.
I have no authority over the server, maybe someone changed something.
The problem is no more.

In a nutshell:

  • s.executeQuery(sql) runs extremely slowly within a tomcat servlet on server
  • Same query runs fine without servlet (simple java program) on the same machine
  • Not all queries are slow within the servlet. Only a few bigger ones do
  • Same servlet runs fast on another machine

UPDATES

Please read the updates below the text !

I have a servlet that executes SQL requests and sends back the results via JSON. For some reason, some requests take a huge amount of time to execute, but when I run them in any Oracle SQL Client, they are executed in no time.

I am talking about a difference of 1 second vs 5 minutes for the same SQL (that is not that complex).

How can this be explained ? Is there a way to improve the performance of a java based SQL request ?

I am using the traditional way of executing queries:

java.sql.Connection conn = null;
java.sql.Statement s = null;
ResultSet rs = null;

String dbDriver = "oracle.jdbc.driver.OracleDriver";
String dbConnectionString = "jdbc:oracle:thin:@" + dbHost + ":" + dbPort + ":" + dbSid;

Class.forName(dbDriver).newInstance();
conn = DriverManager.getConnection(dbConnectionString, dbUser, dbPass);
s = conn.createStatement();
s.setQueryTimeout(9999);
rs = s.executeQuery(newStatement);
ResultSetMetaData rsmd = rs.getMetaData();

// Get the results
while (rs.next()) {
// collect the results
}

// close connections

I tried with ojdbc14 and ojdbc6 but there was no difference.

UPDATE 1: I tried the same SQL in a local Java project (not a servlet) on my client machine, and I get the results immediately. So I assume the problem is coming from my servlet or the tomcat configuration ?

UPDATE 2: The culprit is indeed rs = s.executeQuery(mySql); I tried to use preparedStatement instead, but there is no difference.

UPDATE 3: I created a new Servlet running on a local Tomcat and the Query comes back fast. The problem is therefore coming from my production server or Tomcat config. Any ideas what config items could affect this ?

UPDATE 4: I tried the same code in a normal java program instead of a servlet (still on the same server) and the results are coming fast. Ergo the problem comes from the Servlet itself (or Tomcat ?). Still don't know what to do, but I narrowed it down :)

UPDATE 5: Jstack shows the following (It starts where my servlet is, I cut the rest)

    "http-8080-3" daemon prio=3 tid=0x00eabc00 nid=0x2e runnable [0xaa9ee000]
   java.lang.Thread.State: RUNNABLE
        at java.net.SocketInputStream.socketRead0(Native Method)
        at java.net.SocketInputStream.read(SocketInputStream.java:129)
        at oracle.net.ns.Packet.receive(Packet.java:311)
        at oracle.net.ns.DataPacket.receive(DataPacket.java:105)
        at oracle.net.ns.NetInputStream.getNextPacket(NetInputStream.java:305)
        at oracle.net.ns.NetInputStream.read(NetInputStream.java:249)
        at oracle.net.ns.NetInputStream.read(NetInputStream.java:171)
        at oracle.net.ns.NetInputStream.read(NetInputStream.java:89)
        at oracle.jdbc.driver.T4CSocketInputStreamWrapper.readNextPacket(T4CSocketInputStreamWrapper.java:123)
        at oracle.jdbc.driver.T4CSocketInputStreamWrapper.read(T4CSocketInputStreamWrapper.java:79)
        at oracle.jdbc.driver.T4CMAREngineStream.unmarshalUB1(T4CMAREngineStream.java:429)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:397)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257)
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587)
        at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:210)
        at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:30)
        at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:762)
        at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:925)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1104)
        at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1309)
        - locked <0xe7198808> (a oracle.jdbc.driver.T4CConnection)
        at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:422)
        

So i am Stuck at java.net.SocketInputStream.socketRead0(Native Method) ?


Solution

  • For inexplicable reasons however, this morning the performance increased and my problem is no more. I have no idea why. I have no authority over the server, maybe someone changed something.