Tomcat Servlet中的SQL执行时间比普通Java程序慢得多 [英] SQL execution time much slower in a Tomcat Servlet than in a normal Java program

问题描述

但是出于无法理解的原因，今天早上我的两个查询的性能都有所提高。我不知道为什么。

我对服务器没有权限，也许有人改变了一些东西。

问题不再是。

简而言之：

• s.executeQuery（sql）在服务器上的tomcat servlet中运行速度非常慢


• 相同的查询在没有servlet（简单的java程序）的情况下运行正常同一台机器


• 并非所有查询在servlet中都很慢。只有少数更大的


• 相同的servlet在另一台机器上快速运行

UPDATES

请阅读文本下面的更新！

我有一个 servlet 执行SQL请求并通过JSON发回结果。出于某种原因，某些请求需要花费大量时间来执行，但是当我在任何Oracle SQL客户端中运行它们时，它们很快就会被执行。

我是谈论相同SQL的1秒与5分钟的差异（这不是那么复杂）。

如何解释这个？
有没有办法提高基于java的SQL请求的性能？

我使用传统的执行查询的方式：

  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（）; 
 
 //获得结果
 while（rs.next（））{
 //收集结果
} 
 
 //关闭连接
  

我尝试使用ojdbc14和ojdbc6，但没有区别。

UPDATE 1 ：
我在客户端计算机上的本地Java项目（而不是servlet）中尝试了相同的SQL，我立即得到了结果。所以我假设问题来自我的servlet或tomcat配置？

UPDATE 2 ：
罪魁祸首确实是rs = s.executeQuery（MySQL的）;我尝试使用preparedStatement，但没有区别。

更新3 ：
我创建了一个新的Servlet本地Tomcat和Query快速恢复。因此问题来自我的生产服务器或Tomcat配置。任何想法配置项可能会影响这个？

更新4 ：
我在普通的java程序中尝试了相同的代码而不是一个servlet（仍然在同一台服务器上），结果很快。问题来自Servlet本身（或Tomcat？）。仍然不知道该怎么做，但我把它缩小了：）

更新5 ：
Jstack显示以下内容（它从我的servlet开始，我剪掉其余部分）

 http-8080-3守护进程prio = 3 tid = 0x00eabc00 nid = 0x2e runnable [0xaa9ee000] 
 java.lang.Thread.State：java.net.SocketInputStream.socketRead0的RUNNABLE 
（本机方法）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（T4CSock） etInputStreamWrapper.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) 
  -  lock ed< 0xe7198808> （a oracle.jdbc.driver.T4CConnection）
 at oracle.jdbc.driver.OracleStatementWrapper.executeQuery（OracleStatementWrapper.java:422）
  

所以我被困在 java.net.SocketInputStream.socketRead0（原生方法）？

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) ?

解决方案

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.

这篇关于Tomcat Servlet中的SQL执行时间比普通Java程序慢得多的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！