Fastest way of looping in recordset

I have a VB6 application. I have 2 recordsets having the same number of records (almost 50k). Now I have to loop 5开发者_高级运维0k*50k *(number of fields). Can any one tell me the fastest way to do so?

Thanks in advance.

As others have pointed out it is not a good idea to do this client side but here are a few pointers for speeding up recordset access.

• Open the recordset as forward only (adOpenForwardOnly) and if you are not writing then read only also.

• Refer to the fields by number so instead of doing rs.Fields(“MyField”) you would use rs.Fields(0)

• If you are writing back to the database then consider wrapping things up in a transaction to speed things up.

• When looping use “Do until rs.EOF” instead of counting records.

Thats all I can think of for now but they should help a bit

There really is no fastest way to do this in vb6. You'd use 2 for loops.

You might want to give us more details as to why you are pulling down 50k records (twice) and comparing each field. This is usually an indication that something needs to be done on the database side but was brought in to the client side to be solved.

If your two databases are the same and the only difference is the data, you could do something like this (pseudo-code):

SELECT t1.A, t2.A, t2.B, t2.B, ...
FROM t1
INNER JOIN t2 on t1.id = t2.id
WHERE (t1.A <> t2.A) OR (t1.B <> t2.B) OR ...

t1 and t2 would be your two tables. This isn't the most efficient but it would allow you to do the comparisons very easily. Also, you could get more complicated with what you show in the SELECT statement. Currently it is just a listing of both columns side by side.

Having clarified that you've got two Access databases you need to compare, the easiest is to link the two together and then compare with a query.

Right-click the white empty space, Link tables.
Locate you second DB, select the table in it.

Now you can write a query to compare the two. The data being compared is always up to date as it's being pulled through the link.

I have written code to compare and synchronize two data tables many, many times, and I've posted about it here on SO. The key is to use SQL to limit the results to the records that don't match, but the chief insight is to write your on-the-fly SQL ONE COLUMN AT A TIME. With a WHERE clause on each of your SQL statements, this will be much more efficient than any other comparison method.

Within Access, I've written this code to update one table from another. It assumes the tables have the same fields and that there's a PK field that actually identifies the same record in both tables. The code has a dependency on my SQLRun() function to actually execute the SQL, but that line could be replaced with CurrentDB.Execute if you don't want that.

  Public Function UpdateTableData(ByVal strSourceTable As String, _
      ByVal strTargetTable As String, ByVal strJoinField As String, _
      ByRef db As DAO.Database, Optional ByVal strExcludeFieldsList As String, _
      Optional ByVal strUpdatedBy As String = "Auto Update", _
      Optional strAdditionalCriteria As String) As Boolean
    Dim strUpdate As String
    Dim rsFields As DAO.Recordset
    Dim fld As DAO.Field
    Dim strFieldName As String
    Dim strNZValue As String
    Dim strSet As String
    Dim strWhere As String

    strUpdate = "UPDATE " & strTargetTable & " INNER JOIN " & strSourceTable _
        & " ON " & strTargetTable & "." & strJoinField & " = " _
        & strSourceTable & "." & strJoinField
    ' if the fields don't have the same names in both tables,
    '   create a query that aliases the fields to have the names of the
    '   target table
    ' if the source table is in a different database and you don't
    '   want to create a linked table, create a query and specify
    '   the external database as the source of the table
    ' alternatively, for strTargetTable, supply a SQL string with
    '   the external connect string
    Set rsFields = db.OpenRecordset(strSourceTable)
    For Each fld In rsFields.Fields
      strFieldName = fld.Name
      If strFieldName <> strJoinField Or (InStr(", " & strExcludeFieldsList _
           & ",", strFieldName & ",") <> 0) Then
         Select Case fld.Type
           Case dbText, dbMemo
             strNZValue = "''"
           Case Else
             strNZValue = "0"
         End Select
         strSet = " SET " & strTargetTable & "." & strFieldName & " = _
             varZLSToNull(" & strSourceTable & "." & strFieldName & ")"
         strSet = strSet & ", " & strTargetTable & ".Updated = #" & Date & "#"
         strSet = strSet & ", " & strTargetTable & ".UpdatedBy = " _
             & STR_QUOTE & strUpdatedBy & STR_QUOTE
         strWhere = " WHERE Nz(" & strTargetTable & "." & strFieldName & ", " _
             & strNZValue & ") <> Nz(" & strSourceTable & "." & strFieldName _
             & ", " & strNZValue & ")"
         If db.TableDefs(strTargetTable).Fields(fld.Name).Required Then
            strWhere = strWhere & " AND " & strSourceTable & "." _
                & strFieldName & " Is Not Null"
         End If
         If Len(strAdditionalCriteria) > 0 Then
            strWhere = strWhere & " AND " & strAdditionalCriteria
         End If
         Debug.Print strUpdate & strSet & strWhere
         Debug.Print SQLRun(strUpdate & strSet & strWhere, dbLocal) & " " _
           & strFieldName & " updated."
      End If
    Next fld
    Debug.Print dbLocal.OpenRecordset("SELECT COUNT(*) FROM " _
       & strTargetTable & " WHERE Updated=#" & Date & "# AND UpdatedBy=" _
       & STR_QUOTE & strUpdatedBy & STR_QUOTE)(0) _
       & " total records updated in " & strTargetTable
    rsFields.Close
    Set rsFields = Nothing
    UpdateTableData = True
  End Function

try to use the algorithm of sql., the left and right join., then apply it in vb..,

i also have the same problem with you but i try that solution and it works.., on first, it takes almost 3 hours the complete the query but when i apply the sql algo, it just takes few minis