Resolving an ADO timeout issue in VB6

Question

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

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

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?

Profiler stats:

• From the client application: 7041720 reads, 59458 ms duration, 3900 row counts
• From SSMS: 30802 reads, 238 ms duration, 3900 row counts

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.

Answer 1

800+ records requires about 5 minutes = query problem.

look at your execution plan:

In SSMS, run:

SET SHOWPLAN_ALL ON

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.

EDIT
From reading your question, I'm not sure if you mean it is always fast from SSMS no matter the rows, but slow from VB as the rows increase. If that is the case check this: http://www.google.com/search?q=sql+server+fast+from+ssms+slow+from+application&hl=en&num=100&lr=&ft=i&cr=&safe=images

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

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

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 ...