解决 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 Profiler，与 SSMS 中的查询的两个指标相比，来自客户端计算机的实例进行了更多的读取并花费了 100 倍的时间.根据分析器，SSMS 和客户端机器的查询文本是相同的，所以我认为它不应该使用不同的执行计划.网络库或Provider会不会对此有影响?

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 次读取，238 毫秒持续时间，3900 行计数

似乎它使用了不同的执行计划，但查询完全相同，如果客户端可能使用的执行计划与 SSMS 中显示的不同，我不确定如何检查它.

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 中，运行:

In SSMS, run:

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=en&num=100&lr=&ft=i&cr=&safe=images

可能类似于:参数嗅探或不一致的连接参数(ANSI null、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屋！