help creating a faster meathod for working with remote data

[thechazm]

Hello all,

This is something I seem to battle with a lot is what is the fastest most feasable way to modify remote data (meaning a front end trying to modify backend data).

I have created many projects and have been able to mitigate most of them but this one is eluding me. Any help is appreciated.

I have roughly 97K of data rows that gets updated every day in this one table and it's whole purpose is to combine different server's data into one table. It's really a simple as all it needs to do is find the row of data and update 3 columns to have the value's if their was something there.

Anyway I have done multiple tests regarding this from adding certain indexes to only having the ID field indexed to keeping the recordset open or open it with the specifics for what I want to modify.

It takes almost 2 hours for this computer to update those 97K records and that's really only a small part of the process.

I'm going to try and break this down but here is what I have seen.

1st Meathod:
Using .FindFirst
Processor stays at 50% due to a dual core machine.
Network floats from .27% to 1 %
2 Hours to complete as it slows down as it gets to lower records.

2nd Meathod:
Using Parameters in a query def
Processor floats between 1-4%
Network spikes to 40% when opening the recordset
Slower than meathod one but a more stable rate through the whole process. 2.5-3 hours to complete.

If anyone can take a look at my code and offer any idea's I would greatly appreciate it.

Function FindPROSProjects()
On Error GoTo ErrHandler:
Dim db As Database, rs As DAO.Recordset, rs2 As DAO.Recordset, ii As Long
Set db = CurrentDb
Set rs = db.OpenRecordset("Select * from [(Code) SDSK - PROS Find PROS Project] WHERE((([IBB Date]) Between #" & GetChargesStart & "# AND #" & GetChargesEnd & "#))", dbOpenSnapshot)
Set rs2 = db.OpenRecordset("Select * from [(SDSK) Charges Master] WHERE [IBB Date] Between #" & GetChargesStart & "# AND #" & GetChargesEnd & "#", dbOpenDynaset)
rs.MoveLast
rs.MoveFirst
ii = rs.RecordCount
Do While rs.EOF = False
    StatusLabel "Finding PROS Projects. On Record:" & rs.AbsolutePosition & " - " & ii
    rs2.FindFirst "[ID] = " & rs("ID")
    'Set rs2 = db.OpenRecordset("Select [ID], [PROS Project], [PROS Shop], [PROS TSD] from [(SDSK) Charges Master] WHERE [ID] = " & rs("ID"), dbOpenDynaset)
        If rs2.NoMatch = False Then
        'If rs2.EOF = False Then
            With rs2
                .Edit
                    ![PROS Project] = rs("CProject")
                    ![PROS Shop] = rs("Shop")
                    ![PROS TSD] = rs("CTSD")
                .Update
            End With
        End If
    rs2.Close
    rs.MoveNext
Loop
StatusLabel "Completed!"
rs.Close
rs2.Close
Set rs = Nothing
Set rs2 = Nothing
Set db = Nothing
Exit Function
ErrHandler:
If Err.Number = 3052 Then
    StatusLabel "Clearing Buffer..."
    Err.Clear
    Resume
Else
    MsgBox Err.Number & " " & Err.Description
End If
rs.Close
rs2.Close
Set rs = Nothing
Set rs2 = Nothing
Set db = Nothing
End Function

Thanks,

TheChazm

 

[spikepl]

and the reason for doing VBA loops over records, instead of an SQL query, is ???

 

[thechazm]

Because I was being retarded :/ Holy crap sometime's I'm a big idiot. I get sucked in to programming mode and never think of using a query for this....

Thanks spikepl and please forgive my stupid question

 

[thechazm]

Ow trust me I'm never afraid to ask but it's a huge slap in the face sometimes when looking back because that should have been the first thing I considered. Anyway for time metrics I'm almost embarrased to say but here we go.

1st Meathod:
Using .FindFirst
Processor stays at 50% due to a dual core machine.
Network floats from .27% to 1 %
2 Hours to complete as it slows down as it gets to lower records.

2nd Meathod:
Using Parameters in a query def
Processor floats between 1-4%
Network spikes to 40% when opening the recordset
Slower than meathod one but a more stable rate through the whole process. 2.5-3 hours to complete.

3RD Meathod:
Using an update query
Processor was on average around 15%
Network spikes to 50%
The whole process only took 5 minutes verses 2-3 hours using vba

Now that's embarrasment at it's best lol.