解决VB6中的ADO超时问题 [英] Resolving an ADO timeout issue in VB6

问题描述

在VB6中填充ADO记录集时遇到问题。当我使用SSMS运行查询时，查询（命中SQLServer 2008）只需要大约1秒钟即可运行。当结果集较小时，它可以正常工作，但是当结果集为几百条时，则需要花费很长时间。 800条以上的记录大约需要5分钟才能返回（在SSMS中查询仍然只需要1秒的时间），而6000条以上的记录则需要20分钟以上的时间。我已经通过增加命令超时来修复异常，但是我想知道是否有一种方法可以使它更快地工作，因为它似乎并不是真正需要大量时间的查询。诸如压缩结果之类的事情就不需要那么长时间。记录集如下打开：

I am running into an issue when populating an ADO recordset in VB6. The query (hitting SQLServer 2008) only takes about 1 second to run when I run it using SSMS. It works fine when the result set is small, but when it gets to be a few hundred records it takes a long time. 800+ records requires about 5 minutes to return (query still only takes 1 second in SSMS), and 6000+ takes well over 20 minutes. I have "fixed" the exception by increasing the command timeout, but I was wondering if there was a way to get it to work faster since it does not seem to be the actual query that requires so much time. Something such as compressing the results so it doesn't take as long. The recordset is opened as follows:

myConnection.CommandTimeout = 2000
myConnection.ConnectionString = "Provider=SQLOLEDB;" & _
        "Initial Catalog=DB_NAME;" & _
        "Data Source=SERVER_NAME" & _
        "Network Library=DBMSSOCN;" & _
        "User ID=USER_NAME;" & _
        "Password=PASSWORD;" & _
        "Use Encryption for Data=True;"
myConnection.Open

myRecordSet.Open STORED_PROC_QUERY_STRING, myConnection, adOpenStatic, adLockReadOnly
Set myRecordSet.ActiveConnection = Nothing

myConnection.Close

数据返回3列用于填充组合框。

The data returns 3 columns used to fill a combo box.

更新：
我运行了SQL事件探查器，并且与SSMS中的两个指标相比，来自客户端计算机的实例进行的读取次数更多，花费的时间也增加了100倍。根据分析器，对于SSMS和客户端计算机，查询的文本都是相同的，因此我认为它不应使用其他执行计划。网络库或提供商可能对此有任何影响吗？

UPDATE: I ran SQL Profiler, and the instances from the client machine make more reads and takes more time by a factor of 100 than both metrics for the queries in SSMS. The text of the query is the same for both SSMS and the client machine according to the profiler, so I don't think it should be using a different execution plan. Could the network library or the Provider have any impact on this?

分析器统计信息：

• 从客户端应用程序：7041720
  读取，持续时间59458毫秒，3900行
  计数


• 从SSMS：30802读取，238ms
  持续时间，3900行计数

似乎正在使用不同的执行计划，但查询完全相同，我不确定

It seems like it is using a different execution plan, but the query is exactly the same and I am not sure how to check the execution plan the client might be using if it is different from what is shown in SSMS.

推荐答案

800条以上的记录大约需要5分钟 =查询问题。

查看执行计划：

在SSMS中，运行：

SET SHOWPLAN_ALL ON

然后运行查询，它将不会产生预期的结果集，但有关数据库如何检索数据的执行计划。大多数不良查询通常都是表扫描（查看表中的每一行，速度很慢），因此请在 StmtText 列中查找单词 SCAN。尝试弄清楚为什么未在该表上使用索引（名称将由单词 SCAN出现在该表中）。如果您加入多个表，并且有多个SCAN首先集中在最大的表上。

then run your query, it will not produce the expected result set, but an exceution plan on how the database is retrieving your data. Most bad queries usually table scan (look at every row in the table, which is slow), so look for the word "SCAN" in the StmtText column. Try to figure out why the index is not being used on that table (name will be in there by the word "SCAN"). If you join in multiple tables and have multiple SCANs concentrate on the largest tables first.

没有更多信息，这是您可以获得的最佳通用帮助。

Without more info this is the best "generic" help you can get.

编辑

通过阅读您的问题，我不确定您是说从SSMS来看它总是快的，无论行如何，但随着行数的增加，从VB开始变慢。如果是这种情况，请检查以下内容： http://www.google.com/search?q=sql+server+fast+from+ssms+slow+from + application& hl = zh-CN& num = 100& lr =& ft = i& cr =& safe = images

可能类似于：参数嗅探或连接参数不一致（ANSI空值，arithabort等）

could be something like: parameter sniffing or inconsistent connection parameters (ANSI nulls, arithabort, etc)

对于连接设置，请尝试从SSMS和VB6运行（将它们添加到结果集中）并查看是否存在任何差异：

for the connection settings, try running these from SSMS and from VB6 (add them to the result set) and see if there are any differences:

SELECT SESSIONPROPERTY ('ANSI_NULLS') --Specifies whether the SQL-92 compliant behavior of equals (=) and not equal to (<>) against null values is applied.
                                      --1 = ON 
                                      --0 = OFF

SELECT SESSIONPROPERTY ('ANSI_PADDING') --Controls the way the column stores values shorter than the defined size of the column, and the way the column stores values that have trailing blanks in character and binary data.
                                        --1 = ON 
                                        --0 = OFF

SELECT SESSIONPROPERTY ('ANSI_WARNINGS') --Specifies whether the SQL-92 standard behavior of raising error messages or warnings for certain conditions, including divide-by-zero and arithmetic overflow, is applied.
                                         --1 = ON 
                                         --0 = OFF

SELECT SESSIONPROPERTY ('ARITHABORT') -- Determines whether a query is ended when an overflow or a divide-by-zero error occurs during query execution.
                                      --1 = ON 
                                      --0 = OFF

SELECT SESSIONPROPERTY ('CONCAT_NULL_YIELDS_NULL') --Controls whether concatenation results are treated as null or empty string values.
                                                    --1 = ON 
                                                    --0 = OFF

SELECT SESSIONPROPERTY ('NUMERIC_ROUNDABORT') --Specifies whether error messages and warnings are generated when rounding in an expression causes a loss of precision.
                                              --1 = ON 
                                              --0 = OFF

SELECT SESSIONPROPERTY ('QUOTED_IDENTIFIER') --Specifies whether SQL-92 rules about how to use quotation marks to delimit identifiers and literal strings are to be followed.
                                             --1 = ON 
                                             --0 = OFF

进行查询例如（这样您就可以在VB6中看到连接设置）：

make your query like (so you can see the connection settings in VB6):

SELECT
    col1, col2
        ,SESSIONPROPERTY ('ARITHABORT') AS ARITHABORT
        ,SESSIONPROPERTY ('ANSI_WARNINGS') AS ANSI_WARNINGS
    FROM ...

这篇关于解决VB6中的ADO超时问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！