?
Solved

ADO RecordSet Behaviour

Posted on 2003-03-10
15
Medium Priority
?
399 Views
Last Modified: 2013-12-25
I have an application that runs a sizeable query from an old, slow database.  Over the course of the application the recordset is iterated through several times, and when the end is reached each time a MoveFirst() command is performed to go back to the start.  However each time the MoveFirst is executed, the program actually executes the query all over again!  This causes the app to slow down by several orders of magnitude.  How do i get it to work with the detached data and not refresh every time a MoveFirst is called?
0
Comment
Question by:barryfandango
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +3
15 Comments
 
LVL 3

Author Comment

by:barryfandango
ID: 8105796
ps - currently the line to open the recordset looks like this:

oRS.Open mySQLText, myConnection, adOpenStatic, adLockOptimistic
0
 
LVL 3

Author Comment

by:barryfandango
ID: 8105908
Additional Info:

apparently the field RecordSet.CursorLocation plays a role here.  It's set by default to adUseServer but can be changed to adUseClient.  I'm not sure if this is the solution to my problem, but I gave it a try (setting CursorLocation to adUseClient) and it threw the following error:

#-2147467259 : Data provider or other service returned an E_FAIL status.
0
 
LVL 6

Assisted Solution

by:graham_charles
graham_charles earned 100 total points
ID: 8106211
For background, what ADO Provider are you using? If the provider supports it, you could use the recordset in a disconnected state by setting the ActiveConnection property to Nothing after the recordset it opened.

If not, you might try saving the recordset to a disk file, then opening it from there using the MSPERSIST provider.  If you're just stepping through the recordset a record at a time, your performance will be pretty reasonable, and probably better than a clunky old server:

MyRS.Save MyFileName, adPersistADTG
MyRS.Close

Set MyRS = New ADODB.Recordset
MyRS.Open MyFileName, "Provider=MSPERSIST"


Hope that helps,

g.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 3

Expert Comment

by:phildaley
ID: 8106965
Things to try:

1. myConnection should be a Connection object and not a connection string.
2. Try it with a Command object using its Execute method instead. mySQLText will then get optimized, so that first call will still be slow but subsequent calls faster.
3. If you are not updating the recordset, open database and/or recordset readonly.
0
 
LVL 2

Expert Comment

by:cero
ID: 8123176
Hi barryfandango,

I think you have to use a disconnected recordset.

First, configure the connection to use client side engine.

Myconnection.CursorLocation = adUseClient

' Your extra code

Next, open the recordset

oRs.CursorLocation = adUseClient

oRS.Open mySQLText, myConnection, adOpenStatic, adLockOptimistic

And then

oRs.ActiveConnection = Nothing 'disconnect the recordset.

What DB are you using???

cero
 
0
 
LVL 1

Assisted Solution

by:FirstBorn
FirstBorn earned 100 total points
ID: 8123681
Hi barryfandango,

Here, Try This:

-------------------------------------------
' On the Form:

Dim rstSQL as ADODB.Recordset
Dim strSQL as String

Private Sub Form_Load()
OpenDatabase
End Sub


' This goes somewhere that you'll be populating the Recordset, I use Public Function PopulateRecordset
Public Function PopulateRecordset
strSQL = "Select * from TableName"
Set rstSQL = New ADODB.Recordset
With rstSQL
     .CursorLocation = adUseClient
     .CursorType = adOpenKeyset
     .LockType = adLockOptimistic
     .Open strSQL, cnnCnxn, , , adCmdText
End With
End Function

Private Sub cmdMoveFirst_Click()
     rstRules.MoveFirst
     PopulateFields ' This is where you populate the fields on your form
End Sub

--------------------------------------
' Stick this in the Main Module Somewhere:
' Declarations:
Public strCNN As String
Public cnnCnxn As New ADODB.Connection

Public Function OpenDatabase()
strCNN = "PROVIDER=MSDASQL;dsn=Visual FoxPro" & _
        " Tables;uid=;pwd=;SourceDb=" & App.Path
        ' Or Whatever Your Connection String may be
Set cnnCnxn = New ADODB.Connection

cnnCnxn.Open strCNN

End Function

0
 
LVL 3

Author Comment

by:barryfandango
ID: 8127826
Thanks for all the helpful suggestions.

cero,
The suggestion to set the CursorLocation property on both the RecordSet and Connection objects was something I didn't know about, and it allowed my program to get a little farther in execution.  Unfortunately when the time comes to perform a RecordSet.MoveFirst, I get another "#-2147467259 : Data provider or other service returned an E_FAIL status" error.

phildaley,
AFAIK creating a recordset through Connection.Execute can only return a cursortype of adForwardOnly.  I need to be able to iterate through the data several times without requerying the database.
0
 
LVL 3

Author Comment

by:barryfandango
ID: 8127838
graham_charles,

I'm using MS ADO 2.5 in visual studio 6, and the query is to an ODBC layer sitting over a legacy C-ISAM database (the hulking, sloooow data store for an ancient ERP.)
0
 
LVL 3

Author Comment

by:barryfandango
ID: 8127886
A little bit more info:

based on this page

http://www.xcent.com/faq/XcIBViewItem.asp?ID=334

I changed the project reference from ADO 2.5 to ADO 2.7 but there was no change in the problem.
0
 
LVL 2

Accepted Solution

by:
cero earned 100 total points
ID: 8129621
barryfandango:

mmmm, I'm almost sure your data-provider doesn't support client-side cursors feature.

But, try with a few records, it's the same effect?
Look for a options in your driver (Control Panel - ODBC Connections ), there is an option about cache, buffer, or something???

Well, If you want fast access, use ServerSide cursors, and use a array (a variant variable) and GetRows Method.
But you have to do some changes on your application.

Something Like this:


Dim arrayRows() As Variant


//Open connection server side
//Open Recordset server side

arrayRows = adoRs.GetRows()

//to refer Row(0), Column(10)

   varRows(10, 0)

//to refer Row(15), Column(2)
   varRows(2, 15)


// to iterate through
Dim lngCount as Long

For lngCount = LBound(varRows) To UBound(varRows, 2)
   debug.print varRows(0, lngCount) 'Row lngCount Column 0
   debug.print varRows(1, lngCount) 'Row lngCount Column 1
Next lngCount



cero


 
0
 
LVL 2

Expert Comment

by:cero
ID: 8129629
mistake.... arrayRows and varRows are tha same
0
 
LVL 6

Expert Comment

by:graham_charles
ID: 8130085
Did you try my suggestion of persisting the recordset to disk? If that won't work, I like GetRows() the best...
0
 
LVL 3

Assisted Solution

by:phildaley
phildaley earned 100 total points
ID: 8132214
I suggested using the COMMAND.EXECUTE method and not the CONNECTION.EXECUTE method. If your provider does not allow MoveFirst functionality (some do not) the Command may be faster on subsequent executions because it has compiled the SQL.

Taken from Help:
Improving Queries with the Command Object
With the ADO Command object you can execute queries in the same way as queries executed with the Connection and Recordset object, except that with the Command object you can prepare, or compile, your query on the database source and then repeatedly reissue the query with a different set of values. The benefit of compiling queries in this manner is that you can vastly reduce the time required to reissue modifications to an existing query. In addition, you can leave your SQL queries partially undefined, with the option of altering portions of your queries just prior to execution
0
 
LVL 2

Expert Comment

by:cerebralpc
ID: 9041691
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

DELETE

Please leave any comments here within the next seven days.
 
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
 
cerebralPC
EE Cleanup Volunteer
0
 
LVL 1

Expert Comment

by:FirstBorn
ID: 9072890
Hi cerebralpc,

I would say split points... I offered a suggestion that may help, but was not responded to, but it seems that phildaley, graham_charles, and cero answered the Q, as well...

:)

FirstBorn
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month10 days, 10 hours left to enroll

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question