Strange Performance Issue using ADO and VB

Strange Performance Issue using ADO and VB

Post by John Shelle » Fri, 11 Jan 2002 08:31:11



I have discovered that if I have one recordset in use, opening a second
recordset takes 10x longer than it normally would.  Example:

'----------------------------------
'MyForm
'DB is a global connection object with a running connection to my database
'DB has cursor location set to Client
'----------------------------------
Dim R1 as Recordset

Sub MySub()

    Dim R2 as recordset
    Set R2 = New Recordset

    R2.Open "MyTable",DB

End Sub

'-----------------
'This routine runs in the blink of an eye, since there are only about 300
rows in MyTable.
'But...
'-----------------

Sub MySub2()

    Dim R2 as recordset
    Set R2 = New Recordset

    Set R1 = New Recordset
    R1.Open "MyOtherTable", DB

    R2.Open "MyTable",DB

End Sub

'--------------------
'This now takes about 4 seconds to open R2
'This does NOT fix it:
'--------------------

Sub MySub3()

    Dim R2 as recordset
    Set R2 = New Recordset

    Set R1 = New Recordset
    R1.Open "MyOtherTable", DB
    R1.Close

    R2.Open "MyTable",DB

End Sub

'--------------------
'This now takes about 4 seconds to open R2
'But this DOES:
'--------------------

Sub MySub3()

    Dim R2 as recordset
    Set R2 = New Recordset

    Set R1 = New Recordset
    R1.Open "MyOtherTable", DB
    R1.Close
    set R1 = nothing

    R2.Open "MyTable",DB

End Sub

'--------------------------
'That last example runs as fast as the original.
'--------------------------

Is there some setting for how many recordsets can be open or something?  I'm
really stumped on this one...

Thanks for you ideas and help,

John

 
 
 

Strange Performance Issue using ADO and VB

Post by cb » Fri, 11 Jan 2002 09:55:45


Check out disconnected recordsets.  Make connection, exec
sql, set result to rs object, then close connection.  Your
rs object should still be intact.

Also - when you dim an rs do it like:
Dim rs as ADODB.Recordset

Shouldn't have to set = new adodb.rs

If you are using access, you might see some strange
things 'cuase that's how you dim a dao recordset, like:
Dim rs as Recordset

hth
ChrisB
MCDBA MCSE
www.MyDBA2000.com

Quote:>-----Original Message-----

>I have discovered that if I have one recordset in use,
opening a second
>recordset takes 10x longer than it normally would.  
Example:

>'----------------------------------
>'MyForm
>'DB is a global connection object with a running

connection to my database
Quote:>'DB has cursor location set to Client
>'----------------------------------
>Dim R1 as Recordset

>Sub MySub()

>    Dim R2 as recordset
>    Set R2 = New Recordset

>    R2.Open "MyTable",DB

>End Sub

>'-----------------
>'This routine runs in the blink of an eye, since there
are only about 300
>rows in MyTable.
>'But...
>'-----------------

>Sub MySub2()

>    Dim R2 as recordset
>    Set R2 = New Recordset

>    Set R1 = New Recordset
>    R1.Open "MyOtherTable", DB

>    R2.Open "MyTable",DB

>End Sub

>'--------------------
>'This now takes about 4 seconds to open R2
>'This does NOT fix it:
>'--------------------

>Sub MySub3()

>    Dim R2 as recordset
>    Set R2 = New Recordset

>    Set R1 = New Recordset
>    R1.Open "MyOtherTable", DB
>    R1.Close

>    R2.Open "MyTable",DB

>End Sub

>'--------------------
>'This now takes about 4 seconds to open R2
>'But this DOES:
>'--------------------

>Sub MySub3()

>    Dim R2 as recordset
>    Set R2 = New Recordset

>    Set R1 = New Recordset
>    R1.Open "MyOtherTable", DB
>    R1.Close
>    set R1 = nothing

>    R2.Open "MyTable",DB

>End Sub

>'--------------------------
>'That last example runs as fast as the original.
>'--------------------------

>Is there some setting for how many recordsets can be open
or something?  I'm
>really stumped on this one...

>Thanks for you ideas and help,

>John

>.


 
 
 

Strange Performance Issue using ADO and VB

Post by Robert Ware [M » Sun, 13 Jan 2002 01:09:50


John,

How long does it take to open the tables in Query Analyzer.  This would be
the bench mark that should be used,  to measure against your application
performance.  Without knowing the schema and data set for these tables it
is hard to comment on this behavior further.  Adding the 2 executions in
the same procedure is going to result in the same execution time regardless
of the ordered the record sets are returned.

Hope this helps,
Robert Ware
=================================================
*  This posting is provided "AS IS" with no warranties, and confers no
rights.
*  Please do not send email directly to this alias.Respond to newsgroup

Are you secure?  For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit,
please visit http://www.microsoft.com/security.

--------------------


>Newsgroups: microsoft.public.sqlserver.programming
>Subject: Strange Performance Issue using ADO and VB
>Date: Wed, 09 Jan 2002 23:31:11 GMT

>I have discovered that if I have one recordset in use, opening a second
>recordset takes 10x longer than it normally would.  Example:

>'----------------------------------
>'MyForm
>'DB is a global connection object with a running connection to my database
>'DB has cursor location set to Client
>'----------------------------------
>Dim R1 as Recordset

>Sub MySub()

>    Dim R2 as recordset
>    Set R2 = New Recordset

>    R2.Open "MyTable",DB

>End Sub

>'-----------------
>'This routine runs in the blink of an eye, since there are only about 300
>rows in MyTable.
>'But...
>'-----------------

>Sub MySub2()

>    Dim R2 as recordset
>    Set R2 = New Recordset

>    Set R1 = New Recordset
>    R1.Open "MyOtherTable", DB

>    R2.Open "MyTable",DB

>End Sub

>'--------------------
>'This now takes about 4 seconds to open R2
>'This does NOT fix it:
>'--------------------

>Sub MySub3()

>    Dim R2 as recordset
>    Set R2 = New Recordset

>    Set R1 = New Recordset
>    R1.Open "MyOtherTable", DB
>    R1.Close

>    R2.Open "MyTable",DB

>End Sub

>'--------------------
>'This now takes about 4 seconds to open R2
>'But this DOES:
>'--------------------

>Sub MySub3()

>    Dim R2 as recordset
>    Set R2 = New Recordset

>    Set R1 = New Recordset
>    R1.Open "MyOtherTable", DB
>    R1.Close
>    set R1 = nothing

>    R2.Open "MyTable",DB

>End Sub

>'--------------------------
>'That last example runs as fast as the original.
>'--------------------------

>Is there some setting for how many recordsets can be open or something?  
I'm
>really stumped on this one...

>Thanks for you ideas and help,

>John

 
 
 

1. (Performance Issue) Inserting records in Access Database using VB

What is the faster way of inserting records into an Access database (MS
Access 2000) using VB.
Right now I am using a disconnected recordset and inserting into another
access database, by firing INSERT SQL statement.
So in short I populate a disconneted recordset from a table from Master.mdb
and want to insert the whole table into Slave.mdb. Right now for doing this
I am using INSERT SQL to do this using ADO connection object and ADO
recordset.
Is there a faster way of doing this task.
If anyone knows please do let me know, it will be highly appreciated.

Abhi

2. IT JOBS - FULL TIME AND CONSULTING

3. Performance issue - Access 2000 using VB6 and ADO 2.1 with SQL

4. Please help: UNBEARABLE slowness with Paradox/NetWare

5. Performance Issue when Browsing a large Table using ADO and DataGrid

6. Foxfont.FON in FoxPro 2.6a

7. strange performance-problems after upgrading from ADO 2.5 to ADO 2.6

8. Q:some Ingres_at_HP questions

9. Strange Performance Issue

10. Strange performance issue - need ideas

11. Strange performance issue

12. Error when using AddNew method in ADO (using ADO 2.7 SP1 from VB 6 SP4)

13. Strange ADO / Word mail merge issue