Interesting Performance Issue... 
Author Message
 Interesting Performance Issue...

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



Mon, 28 Jun 2004 07:01:19 GMT
 Interesting Performance Issue...

I don't think there are settings like how many recordsets can be opened and
so on. I can't reproduce your behavior on my side either(With ADO2.5
against SQL Server 2000). I would suggest you try to narrow down in the
following ways:

Is this problem database specific? Do you use SQL or Access? Please try
those sample database shipped with SQL Server/VB, will this still happen?

Is this problem machine dependent? Can you reproduce this behavior on other
machines?

Is this problem project dependent? Do you use ADO or DAO?Can you reproduce
it with other simple project?

You can also provide such information and open a incident with MS support
to get efficient support.

Hope it helps.

Regards,
Robin

This posting is provided AS IS, with no warranties, and confers no rights.



Mon, 28 Jun 2004 11:38:08 GMT
 Interesting Performance Issue...
I have never experienced any delay?

Some hints though

If you are opening a recordset using sql then use this syntax and also reuse
the object
if necessary!

    Dim rsTemp as recordset
    Dim strSql1 as string
    Dim strSql2 as string

    strSql1 = "SELECT * FROM tblCompany"
    strSql2 = "SELECT * FROM tblContacts"

    Set rsTemp = new recordset
        With rsTemp
            .open strSql1, DB, adOpenForwardOnly, adOpenReadOnly, adCmdText
            '/- Do what you want here
            .close
            .open strSql2, DB, adOpenForwardOnly, adOpenReadOnly, adCmdText
            '/- Do what you want here
            .close
        End with
    Set rsTemp = nothing

    Of course the parameters depend on what you need to do with it!

    But always use adCmdText if you are passing a sql statement!

Regards

VOrtex


Quote:
> 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



Mon, 28 Jun 2004 11:08:13 GMT
 Interesting Performance Issue...
Here is the actual test program you can run, my resuts follow showing that
loading the SAME recordset takes 13 longer if you do not disconnect or set
the first recordset to nothing:

Option Explicit

Sub Main()

    Dim CloseIT As Boolean
    Dim DisconnectIT As Boolean
    Dim KillIt As Boolean

    Dim C As ADODB.Connection
    Dim R1 As ADODB.Recordset
    Dim R2 As ADODB.Recordset
    Dim T As Double
    Dim M As String

    Dim PassNumber As Long

DoItAgain:

    Set C = New ADODB.Connection
    C.CursorLocation = adUseClient
    Set R1 = New Recordset
    R1.CursorLocation = adUseClient
    Set R2 = New Recordset
    R2.CursorLocation = adUseClient

    M = "Driver={SQL Server};"
    M = M + "Server=myServer;"
    M = M + "Uid=UserName;"
    M = M + "Pwd=Password;"
    M = M + "DataBase=myDB;"

    C.Open M

    CloseIT = MsgBox("Close R1?", vbYesNo) = vbYes
    DisconnectIT = MsgBox("Disconnect R1?", vbYesNo) = vbYes
    KillIt = MsgBox("Set R1 = Nothing?", vbYesNo) = vbYes

    PassNumber = PassNumber + 1

    M = "-------------------------------------------" & vbCrLf
    M = M & "Pass Number: " & PassNumber & vbCrLf & vbCrLf
    M = M & "CloseIT = " & CloseIT & vbCrLf
    M = M & "DisconnectIT = " & DisconnectIT & vbCrLf
    M = M & "KillIT = " & KillIt & vbCrLf & vbCrLf

    T = Timer
    R1.Open "myTable", C, adOpenForwardOnly, adLockReadOnly
    M = M & "Time To Open Recordset R1: " & CLng((Timer - T) * 1000) & "
milisecongs" & vbCrLf

    If CloseIT Then R1.Close
    If DisconnectIT Then Set R1.ActiveConnection = Nothing
    If KillIt Then Set R1 = Nothing

    T = Timer
    R2.Open "myTable", C, adOpenForwardOnly, adLockReadOnly
    M = M & "Time To Open Recordset R2: " & CLng((Timer - T) * 1000) & "
milisecongs" & vbCrLf

    Debug.Print M

    If Not R1 Is Nothing Then
        If R1.State Then R1.Close
    End If
    R2.Close
    C.Close
    Set R1 = Nothing
    Set R2 = Nothing
    Set C = Nothing

    If MsgBox("TestAgain?", vbYesNo) = vbYes Then GoTo DoItAgain

End Sub

========================Results============================
-------------------------------------------
Pass Number: 1

CloseIT = False
DisconnectIT = False
KillIT = False

Time To Open Recordset R1: 10 milisecongs
Time To Open Recordset R2: 130 milisecongs

-------------------------------------------
Pass Number: 2

CloseIT = True
DisconnectIT = False
KillIT = False

Time To Open Recordset R1: 10 milisecongs
Time To Open Recordset R2: 131 milisecongs

-------------------------------------------
Pass Number: 3

CloseIT = False
DisconnectIT = True
KillIT = False

Time To Open Recordset R1: 10 milisecongs
Time To Open Recordset R2: 10 milisecongs

-------------------------------------------
Pass Number: 4

CloseIT = False
DisconnectIT = False
KillIT = True

Time To Open Recordset R1: 10 milisecongs
Time To Open Recordset R2: 10 milisecongs

-------------------------------------------
Pass Number: 5

CloseIT = True
DisconnectIT = True
KillIT = False

Time To Open Recordset R1: 10 milisecongs
Time To Open Recordset R2: 10 milisecongs

-------------------------------------------
Pass Number: 6

CloseIT = False
DisconnectIT = True
KillIT = True

Time To Open Recordset R1: 10 milisecongs
Time To Open Recordset R2: 10 milisecongs

-------------------------------------------
Pass Number: 7

CloseIT = True
DisconnectIT = False
KillIT = True

Time To Open Recordset R1: 10 milisecongs
Time To Open Recordset R2: 10 milisecongs

-------------------------------------------
Pass Number: 8

CloseIT = True
DisconnectIT = True
KillIT = True

Time To Open Recordset R1: 10 milisecongs
Time To Open Recordset R2: 10 milisecongs

-------------------------------------------
Pass Number: 1

CloseIT = True
DisconnectIT = True
KillIT = True

Time To Open Recordset R1: 10 milisecongs
Time To Open Recordset R2: 10 milisecongs

-------------------------------------------
Pass Number: 2

CloseIT = False
DisconnectIT = True
KillIT = True

Time To Open Recordset R1: 10 milisecongs
Time To Open Recordset R2: 10 milisecongs

-------------------------------------------
Pass Number: 3

CloseIT = True
DisconnectIT = False
KillIT = True

Time To Open Recordset R1: 11 milisecongs
Time To Open Recordset R2: 10 milisecongs

-------------------------------------------
Pass Number: 4

CloseIT = True
DisconnectIT = True
KillIT = False

Time To Open Recordset R1: 10 milisecongs
Time To Open Recordset R2: 10 milisecongs

-------------------------------------------
Pass Number: 5

CloseIT = False
DisconnectIT = False
KillIT = True

Time To Open Recordset R1: 20 milisecongs
Time To Open Recordset R2: 10 milisecongs

-------------------------------------------
Pass Number: 6

CloseIT = False
DisconnectIT = True
KillIT = False

Time To Open Recordset R1: 10 milisecongs
Time To Open Recordset R2: 10 milisecongs

-------------------------------------------
Pass Number: 7

CloseIT = True
DisconnectIT = False
KillIT = False

Time To Open Recordset R1: 10 milisecongs
Time To Open Recordset R2: 140 milisecongs

-------------------------------------------
Pass Number: 8

CloseIT = False
DisconnectIT = False
KillIT = False

Time To Open Recordset R1: 10 milisecongs
Time To Open Recordset R2: 140 milisecongs



Tue, 29 Jun 2004 00:39:02 GMT
 Interesting Performance Issue...
After test with VB6+SQL2000 with the pubs sample database, I still can't
reproduce your problem. Sample data as follows:

-------------------------------------------
Pass Number: 1

CloseIT = False
DisconnectIT = False
KillIT = False

Time To Open Recordset R1: 10milisecongs
Time To Open Recordset R2: 10milisecongs

-------------------------------------------
Pass Number: 2

CloseIT = True
DisconnectIT = False
KillIT = False

Time To Open Recordset R1: 0milisecongs
Time To Open Recordset R2: 10milisecongs

-------------------------------------------
Pass Number: 3

CloseIT = False
DisconnectIT = True
KillIT = False

Time To Open Recordset R1: 10milisecongs
Time To Open Recordset R2: 0milisecongs

I seems that there maybe other factors other than code cause your behavior,
I would suggest work with MS support if you want someone review your
code/scenario carefully and provide efficient support.

Hope it helps.

Regards,
Robin

This posting is provided AS IS, with no warranties, and confers no rights.



Tue, 29 Jun 2004 14:07:13 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. Interesting performance issue

2. Interesting Query Performance Issue

3. OPENROWSET performance issue or SET FMTONLY OFF issue

4. interesting odbc dsn setup issue - change to wrong default database

5. subselect bug (was Re: [GENERAL] DBLink: interesting issue)

6. Interesting INSERT issue

7. DBLink: interesting issue

8. interesting issue: dso

9. Very Interested in Common Rollback Issue

10. Interesting OEM issue

11. Interesting date issue


 
Powered by phpBB® Forum Software