Comparing two tables(using time dif)

[mfaqueiroz]

Hei

I've one function that is really slow :/ , maybe you have some tips to improve that.
I have one table with all the events(arround 915755 registers) and other with the Maintenance Events(9000).
If one off my "on" or "off" events occured 15 seconds before or after the Maintenance Events , they are flagged as "Maintenance".




Sub Maintenance()


Dim TempoEvenLog As Date
Dim TempoMaintenance
Dim Dif As Long
Dim MachineCode as String
Dim ID as integer
Dim Status as String
Dim Dif as Long
Dim TagMan as string
Dim MachineCodeMan as strinG
dIM Machine AS String



Set dbs = DBEngine(0)(0)
Set Maintenance = dbs.OpenRecordset("TabMaintenance", DB_OPEN_TABLE) 'Maintenance
Set Events = dbs.OpenRecordset("DesLig", DB_OPEN_DYNASET)
Set Results = dbs.OpenRecordset("Results", DB_OPEN_DYNASET)


Events.MoveFirst

Do While Not Events.EOF
If IsNull(Events.Fields(15)) Then
DateTime = Events.Fields(2) 'RTU date
Machine = Events.Fields(4)
MachineCode = Events.Fields(5)
ID = Events.Fields(0)
Tag = Left(Events.Fields(8), 10)
Status = Events.Fields(7)

Maintenance.MoveFirst
Do While Not ELog.EOF
MachineMan = ELog.Fields(5)
TimeMAN = ELog.Fields(1) 'RTU date
MachineCodeMan = ELog.Fields(6)
IDman = ELog.Fields(0)
Dif = DateDiff("s", TimeMAN, DateTime)
TagMAN = Left(ELog.Fields(4), 10)

If Status = "Off" And Tag = TagMAN And Dif >= -15 And Dif <= 15 Then

Results.AddNew
Results.Fields(1) = ID
Results.Fields(2) = "Maintenance"
Results.Fields(4) = MachineCode
Results.Fields(5) = Machine
Results.Fields(6) = DateTime
Results.Fields(16) = "OFF"
Results.Update

Exit Do


ElseIf Status = "On" And Tag = TagMAN And Dif >= -15 And Dif <= 15 Then
Results.AddNew
Results.Fields(1) = ID
Results.Fields(2) = "Maintenance"
Results.Fields(4) = MachineCode
Results.Fields(5) = Machine
Results.Fields(6) = DateTime
Results.Fields(16) = "ON"
Results.Update


Exit Do


Else

End If
Maintenance.MoveNext

Loop
End If
Events.MoveNext
Loop

End Sub

 

[Minty]

Looking at the numbers involved - And I've formatted this so that you can see the loop structure - PLEASE use code tags to make things readable!

Private Sub try_out()

    Dim TempoEvenLog As Date
    Dim TempoMaintenance
    Dim Dif As Long
    Dim MachineCode As String
    Dim ID As Integer
    Dim Status As String
    Dim Dif As Long
    Dim TagMan As String
    Dim MachineCodeMan As String
    Dim Machine As String



    Set dbs = DBEngine(0)(0)
    Set Maintenance = dbs.OpenRecordset("TabMaintenance", DB_OPEN_TABLE)        'Maintenance
    Set Events = dbs.OpenRecordset("DesLig", DB_OPEN_DYNASET)
    Set Results = dbs.OpenRecordset("Results", DB_OPEN_DYNASET)


    Events.MoveFirst

    Do While Not Events.EOF
        If IsNull(Events.Fields(15)) Then
            DateTime = Events.Fields(2)        'RTU date
            Machine = Events.Fields(4)
            MachineCode = Events.Fields(5)
            ID = Events.Fields(0)
            Tag = Left(Events.Fields(8), 10)
            Status = Events.Fields(7)

            Maintenance.MoveFirst
            Do While Not ELog.EOF
                MachineMan = ELog.Fields(5)
                TimeMAN = ELog.Fields(1)        'RTU date
                MachineCodeMan = ELog.Fields(6)
                IDman = ELog.Fields(0)
                Dif = DateDiff("s", TimeMAN, DateTime)
                TagMan = Left(ELog.Fields(4), 10)

                If Status = "Off" And Tag = TagMan And Dif >= -15 And Dif <= 15 Then

                    Results.AddNew
                    Results.Fields(1) = ID
                    Results.Fields(2) = "Maintenance"
                    Results.Fields(4) = MachineCode
                    Results.Fields(5) = Machine
                    Results.Fields(6) = DateTime
                    Results.Fields(16) = "OFF"
                    Results.Update

                    Exit Do


                ElseIf Status = "On" And Tag = TagMan And Dif >= -15 And Dif <= 15 Then
                    Results.AddNew
                    Results.Fields(1) = ID
                    Results.Fields(2) = "Maintenance"
                    Results.Fields(4) = MachineCode
                    Results.Fields(5) = Machine
                    Results.Fields(6) = DateTime
                    Results.Fields(16) = "ON"
                    Results.Update


                    Exit Do


                Else

                End If
                Maintenance.MoveNext

            Loop
        End If
        Events.MoveNext
    Loop

End Sub

You are effectively looping through 915755 events individually checking them against 9000 Maintenence events which equals 8,241,795,000 comparisons - I'm not surprised it take a while?

I think you need to streamline the initial data or possibly flag an event record that you have already checked it so not to to check it again?

 

[Cronk]

Use of some initial queries might reduce the size of the recordset being looped through.

But I wish the procedure had been posted in whole. No Dim for recordsets and Elog is a recordset opened somewhere or other.