即使启用了 MARS，对 MSSQL 的 SQL 查询也会出现暂停
我们正在测试来自 jTDS 和 Microsoft 的 JDBC 驱动程序，并且我们在查询执行中遇到了不必要的暂停。我们的应用程序打开许多结果集，并从每个结果集中仅获取几行。每个查询选择大约 100k 行，但我们只获取大约 50 行（足以填满一页）。问题是，第一个查询之后的每个查询都包含大约 2 秒的暂停，在此期间驱动程序将前一个 ResultSet 中的所有行加载到临时存储（内存或磁盘）中，以便稍后可以遍历它们。因为在最坏的情况下我们有大约 6 个查询，所以会有大约 10 秒的暂停，这使得应用程序对用户没有响应。 MSSQL 版本是 2005。
为了消除此类暂停，我们尝试通过 Microsoft JDBC 驱动程序的连接字符串参数启用 MARS（多个活动结果集）（由于缺乏文档，我们尝试了 https://sites.google.com/site/sqlconnect/sql2005strings）。连接字符串示例：
jdbc:sqlserver://TESTDBMACHINE;instanceName=S2005;databaseName=SampleDB;MarsConn=yes
但它们都没有解决问题。 Microsoft JDBC 驱动程序似乎接受连接字符串中的任何内容 - 如果将 MarsConn=yes 替换为 PleaseBeFast=yes，MS 驱动程序会忽略该参数，甚至不记录事实。我不知道 MARS 是否是一个仅限客户端的功能，仅缓存以前活动结果集中的行，或者它是否是一个服务器功能。我什至不知道如何从服务器端检测给定连接是否正在使用 MARS。如果您能对此发表评论，我们将非常欢迎。
暂停的另一个解决方案是使用可滚动（双向）结果集。这消除了暂停，但使获取时间减慢 80%，并且消耗更多网络。我们现在正在考虑实现一个 JDBC 连接包装器，该包装器保留实际连接池并自动向不同的“ResultSet free”连接发出查询。但这有点麻烦，因为我们需要在每个连接及其活动结果集之间保持链接。此外，它还会消耗更多来自服务器的连接，并可能给 DBA 带来麻烦。如果存在活动事务（所有查询必须在同一连接上发出），则此解决方案无济于事。
您是否知道一些参数、配置、特定 API、链接或技巧可以消除第二次及后续查询执行的暂停？
We are testing JDBC drivers from jTDS and Microsoft, and we are suffering from unwanted pauses in query execution. Our application opens many ResultSets and fetches only a few rows from each. Each query selects about 100k rows, but we fetch only about 50 (which is enough to fill a page). The problem is that every query after the first contains a pause of about 2s, on which the driver loads all rows from the previous ResultSet to a temporary storage (memory or disk), so they can be traversed later. Because we have about 6 queries in worst scenarios, there will be a pause of about 10s, which makes the application unresponsive to the user. MSSQL version is 2005.
To remove such pauses, we've tried to enable MARS (Multiple Active Result Sets) via connection string parameters of Microsoft JDBC driver (due to lack of documentation, we tried everything that is listed on https://sites.google.com/site/sqlconnect/sql2005strings). Example of connection string:
jdbc:sqlserver://TESTDBMACHINE;instanceName=S2005;databaseName=SampleDB;MarsConn=yes
But none of them solves the problem. Microsoft JDBC driver seems to accept anything at connection string - if you replace MarsConn=yes by PleaseBeFast=yes, MS driver ignores the parameter and doesn't even log the fact. I don't know if MARS is a client-only feature that just caches rows from a previously active result set, or if it's a server feature. I don't even know how to detect, from the server side, if a given connection is using MARS. If you can comment on this, it will be welcome.
Another solution for the pause was to use scrollable (bidirectional) result sets. This removes the pause, but makes fetch time 80% slower and more network consuming. We are now considering to implement a JDBC connection wrapper that keeps a pool of actual connections and automatically issue queries to distinct "ResultSet free" connections. But this is somewhat cumbersome because we need to keep a link between each connection and its active ResultSet. Also it would consume more connections from the server and may cause troubles to DBAs. And this solution doesn't help if there is an active transaction, on which all queries must be issued on the same connection.
Do you know some parameter, configuration, specific API, link or trick that can remove the pause from the second and subsequent query executions?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
修复您的 SQL 查询！为什么只使用 100k 行中的前 50 行左右？使用
TOP 100或类似的东西！应用程序没有理由过滤 100k 行，这是数据库的工作。fix your SQL queries! why only use the first 50 or so from 100k rows?? use
TOP 100or something like that! There is no reason that the application should be filtering 100k rows, that is the job of the database.比客户端的问题更重要的是服务器端发生的事情。由于您发出查询然后停止读取结果，服务器将不得不在执行过程中暂停查询，因为网络缓冲区已满并且没有空间可写入结果。在执行过程中暂停的查询会消耗大量资源：内存、锁，以及最重要的工作线程（有 很少这些散布在周围）。
仅对您需要的数据发出查询，消耗所有数据，释放连接，更重要的是释放服务器资源。如果您的查询太复杂，请返回绘图板并重新设计您的数据模型，以正确、有效地回答您所请求的查询。现在你完全是在找错对象，你只是在问如何让糟糕的情况变得更糟。
Far more important that your client woes is what happens on the server side. Since you issue queries and then you stop reading the result the server will have to suspend the query in the middle of the execution because the network buffers are full and has no room to write the result into. A query suspended in the middle of the execution is consuming a lot of resources: memory, locks and, most importantly, a worker thread (there are very few of these lying around).
Issue queries for only the data you need, consume all the data, free the connection and, more importantly, the server resources. If your queries are to complex, go back to the drawing board and redesign your data model to properly answer, efficiently, the queries your requesting from it. Right now you are totally barking up the wrong tree, you're simply asking how to make a bad situation worse.
我们使用 SQL Server 驱动程序（Native Client 10 - sqlncli10.dll）创建了 ODBC 数据源。此 ODBC 数据源配置为启用 MARS（键
HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\ datasource，MARS_Connection的值必须为 <代码>是）。然后我们使用了 Sun 的 JDBC-ODBC 桥，瞧！停顿消失了，令人惊讶的是，获取时间变得比 JTDS 和 MSSQL 纯 Java JDBC 驱动程序更快！根据 http://msdn.microsoft.com/en- us/library/ms345109(SQL.90).aspx，MARS 是一项服务器辅助功能。纯Java驱动程序（来自JTDS和MSSQL）似乎不支持基于服务的MARS（至少在多次配置更改后我们无法启用它）。因为我们使用 MSSQL Server 的大多数用户群都在 Windows 上运行（这并不奇怪），所以我们即将从 JTDS 切换到 JDBC-ODBC。 Native Client ODBC 驱动程序和 JDBC-ODBC 桥似乎都是成熟的、功能齐全且最新的解决方案，所以我想应该没有问题。如果你知道一些，请评论！
基于 Linux 的用户仍将使用 JTDS。既然现在我们知道 MARS 是一项服务器辅助功能，我们将填写 JTDS 和 Microsoft 的功能请求，以在其纯 Java JDBC 驱动程序中支持 MARS。
We've created an ODBC data source using the SQL Server driver (Native Client 10 - sqlncli10.dll). This ODBC data source was configured to enable MARS (key
HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\ datasource, value ofMARS_Connectionmust beYes). Then we used Sun's JDBC-ODBC bridge, and voilá! The pauses were gone, and surprisingly, fetch time became faster than JTDS and MSSQL pure Java JDBC drivers!According to http://msdn.microsoft.com/en-us/library/ms345109(SQL.90).aspx, MARS is a server-aided feature. The pure Java drivers (from JTDS and MSSQL) doesn't seem to support served-based MARS (at least we couldn't enable it after many configuration changes). Because most of our user base that uses MSSQL Server runs on Windows (no surprise), we are about to make the switch from JTDS to JDBC-ODBC. Both Native Client ODBC driver and JDBC-ODBC bridge seems to be mature, full featured and up-to-date solutions, so I guess there should be no problems. If you know some, please comment!
Linux based users will still use JTDS. Since now we know that MARS is a server-aided feature, we'll fill a feature request for JTDS and Microsoft to support MARS in their pure Java JDBC drivers.