存储过程通过Java运行速度比直接在数据库上运行慢30％ [英] Stored proc running 30% slower through Java versus running directly on database

问题描述

我正在使用Java 1.6，JTDS 1.2.2（也只是尝试1.2.4无效）和SQL Server 2005来创建CallableStatement来运行存储过程（没有参数）。我看到运行相同存储过程的Java包装器比使用SQL Server Management Studio慢30％。我运行了MS SQL分析器，两个进程之间的I / O差别不大，所以我认为它与查询计划缓存无关。

I'm using Java 1.6, JTDS 1.2.2 (also just tried 1.2.4 to no avail) and SQL Server 2005 to create a CallableStatement to run a stored procedure (with no parameters). I am seeing the Java wrapper running the same stored procedure 30% slower than using SQL Server Management Studio. I've run the MS SQL profiler and there is little difference in I/O between the two processes, so I don't think it's related to query plan caching.

存储的proc不带参数，也不返回任何数据。它使用服务器端游标来计算填充表所需的值。

The stored proc takes no arguments and returns no data. It uses a server-side cursor to calculate the values that are needed to populate a table.

我无法看到从Java调用存储过程如何添加一个30％的开销，当然它只是数据库的一个管道，SQL被发送下来然后数据库执行它....数据库可以给Java应用程序一个不同的查询计划吗？

I can't see how the calling a stored proc from Java should add a 30% overhead, surely it's just a pipe to the database that SQL is sent down and then the database executes it....Could the database be giving the Java app a different query plan??

我已发布到 MSDN论坛和sourceforge JTDS论坛（主题：在JTDS中存储的proc比在DB中直接更慢）我想知道是否有人有任何关于为什么会发生这种情况的建议？

I've posted to both the MSDN forums, and the sourceforge JTDS forums (topic: "stored proc slower in JTDS than direct in DB") I was wondering if anyone has any suggestions as to why this might be happening?

提前致谢，

-James

（不用担心，我会在找到解决方案后整理其他论坛中的所有答案）

(N.B. Fear not, I will collate any answers I get in other forums together here once I find the solution)

Java代码片段：

sLogger.info("Preparing call...");
stmt = mCon.prepareCall("SP_WB200_POPULATE_TABLE_limited_rows");
sLogger.info("Call prepared.  Executing procedure...");
stmt.executeQuery();
sLogger.info("Procedure complete.");

我运行了sql profiler，发现了以下内容：

I have run sql profiler, and found the following:

Java应用程序：
CPU：466,514读取：142,478,387写入：284,078持续时间：983,796

Java app : CPU: 466,514 Reads: 142,478,387 Writes: 284,078 Duration: 983,796

SSMS：
CPU：466,973读取： 142,440,401写道：280,244持续时间：769,851

SSMS : CPU: 466,973 Reads: 142,440,401 Writes: 280,244 Duration: 769,851

（两个DBCC DROPCLEANBUFFERS在分析之前运行，并且都产生正确的行数）

(Both with DBCC DROPCLEANBUFFERS run prior to profiling, and both produce the correct number of rows)

所以我的结论是他们都执行相同的读写操作，只是他们这样做的方式不同，你们怎么想？

So my conclusion is that they both execute the same reads and writes, it's just that the way they are doing it is different, what do you guys think?

事实证明，不同客户端的查询计划明显不同（Java客户端在插入期间更新索引，而不是在更快的SQL客户端中，同样，它执行连接的方式也不同（嵌套） loop Vs.收集流，嵌套循环Vs索引扫描，唉！））。这就是为什么，我还不知道（当我确实到底时，我会重新发帖）

It turns out that the query plans are significantly different for the different clients (the Java client is updating an index during an insert that isn't in the faster SQL client, also, the way it is executing joins is different (nested loops Vs. gather streams, nested loops Vs index scans, argh!)). Quite why this is, I don't know yet (I'll re-post when I do get to the bottom of it)

结语

Epilogue

我无法让它正常工作。我尝试在Java和Mgmt studio客户端之间均匀化连接属性（ arithabort ， ansi_nulls 等）。它最终导致两个不同的客户端具有非常相似的查询/执行计划（但仍然具有不同的实际plan_ids）。我发布了一个我发现的摘要 MSDN SQL Server论坛因为我发现不仅在JDBC客户端和管理工作室之间，而且在Microsoft自己的命令行客户端SQLCMD之间也有不同的性能，我还检查了一些更激进的东西，如网络流量，或者将存储过程包装在另一个存储过程中，只是为了咧嘴笑。

I couldn't get this to work properly. I tried homogenising the connection properties (arithabort, ansi_nulls etc) between the Java and Mgmt studio clients. It ended up the two different clients had very similar query/execution plans (but still with different actual plan_ids). I posted a summary of what I found to the MSDN SQL Server forums as I found differing performance not just between a JDBC client and management studio, but also between Microsoft's own command line client, SQLCMD, I also checked some more radical things like network traffic too, or wrapping the stored proc inside another stored proc, just for grins.

我感觉问题出在光标执行方式的某个地方，而且它是以某种方式给出的上升到Java进程被暂停，但为什么一个不同的客户端应该在没有其他运行时产生这种不同的锁定/等待行为并且运行相同的执行计划有点超出我的技能（我不是DBA！） 。

I have a feeling the problem lies somewhere in the way the cursor was being executed, and it was somehow giving rise to the Java process being suspended, but why a different client should give rise to this different locking/waiting behaviour when nothing else is running and the same execution plan is in operation is a little beyond my skills (I'm no DBA!).

结果，我已经决定4天就足以让任何人浪费在这样的事情上，所以我会gr围绕它编写代码（如果我是诚实的，那么存储过程需要重新编码才能更加增量，而不是每周重新计算所有数据），然后将这个数据用于体验。我会把这个问题保持开放，非常感谢所有把帽子放在戒指上的人，这一切都很有用，如果有人想出更进一步的话，我很想听到更多的选择......如果有人发现的话这篇文章是在他们自己的环境中看到这种行为的结果，然后希望你可以尝试自己的一些指示，并希望比我们更充分地看到它。

As a result, I have decided that 4 days is enough of anyone's time to waste on something like this, so I will grudgingly code around it (if I'm honest, the stored procedure needed re-coding to be more incremental instead of re-calculating all data each week anyway), and chalk this one down to experience. I'll leave the question open, big thanks to everyone who put their hat in the ring, it was all useful, and if anyone comes up with anything further, I'd love to hear some more options...and if anyone finds this post as a result of seeing this behaviour in their own environments, then hopefully there's some pointers here that you can try yourself, and hope fully see further than we did.

我现在已经准备好了周末！

I'm ready for my weekend now!

-James

推荐答案

对不起，我没有找到正确的答案，所以我不想将其中的任何一个分配为正确，所以我打算将这个答案标记为正确，并祝愿任何人遇到类似的任何运气！

Sorry, I've not found a correct answer to this, so I don't want to allocate any of these as correct, so I am going to mark this answer as correct, and wish anyone luck who comes across anything similar!

这篇关于存储过程通过Java运行速度比直接在数据库上运行慢30％的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！