sql - Resolving an ADO timeout issue in VB6 -

i running issue when populating ado recordset in vb6. query (hitting sqlserver 2008) takes 1 second run when run using ssms. works fine when result set small, when gets few hundred records takes long time. 800+ records requires 5 minutes return (query still takes 1 second in ssms), , 6000+ takes on 20 minutes. have "fixed" exception increasing command timeout, wondering if there way work faster since not seem actual query requires time. such compressing results doesn't take long. recordset opened 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 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 fill combo box.

update: ran sql profiler, , instances client machine make more reads , takes more time factor of 100 both metrics queries in ssms. text of query same both ssms , client machine according profiler, don't think should using different execution plan. network library or provider have impact on this?

profiler stats:

• from client application: 7041720 reads, 59458 ms duration, 3900 row counts
• from ssms: 30802 reads, 238 ms duration, 3900 row counts

it seems using different execution plan, query same , not sure how check execution plan client might using if different shown in ssms.

800+ records requires 5 minutes = query problem.

look @ execution plan:

in ssms, run:

set showplan_all on

then run query, not produce expected result set, exceution plan on how database retrieving data. bad queries table scan (look @ every row in table, slow), word "scan" in stmttext column. try figure out why index not being used on table (name in there word "scan"). if join in multiple tables , have multiple scans concentrate on largest tables first.

without more info best "generic" can get.

edit
reading question, i'm not sure if mean fast ssms no matter rows, slow vb rows increase. if 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 like: parameter sniffing or inconsistent connection parameters (ansi nulls, arithabort, etc)

for connection settings, try running these ssms , vb6 (add them result set) , see if there differences:

select sessionproperty ('ansi_nulls') --specifies whether sql-92 compliant behavior of equals (=) , not equal (<>) against null values applied.                                       --1 = on                                        --0 = off  select sessionproperty ('ansi_padding') --controls way column stores values shorter defined size of column, , way column stores values have trailing blanks in character , binary data.                                         --1 = on                                          --0 = off  select sessionproperty ('ansi_warnings') --specifies whether sql-92 standard behavior of raising error messages or warnings conditions, including divide-by-zero , arithmetic overflow, applied.                                          --1 = on                                           --0 = off  select sessionproperty ('arithabort') -- determines whether query ended when overflow or divide-by-zero error occurs during query execution.                                       --1 = on                                        --0 = off  select sessionproperty ('concat_null_yields_null') --controls whether concatenation results treated null or empty string values.                                                     --1 = on                                                      --0 = off  select sessionproperty ('numeric_roundabort') --specifies whether error messages , warnings generated when rounding in expression causes loss of precision.                                               --1 = on                                                --0 = off  select sessionproperty ('quoted_identifier') --specifies whether sql-92 rules how use quotation marks delimit identifiers , literal strings followed.                                              --1 = on                                               --0 = off 

make query (so can see connection settings in vb6):

select     col1, col2         ,sessionproperty ('arithabort') arithabort         ,sessionproperty ('ansi_warnings') ansi_warnings     ...