How to make LOOP faster??

[knockyo]

hi,

currently i have a function that involve DO WHILE Loop.........

Within the loop, i have 600 hundred data need to trigger and update the data.

Since it contains hundred data need to scan and trigger, how i can make the loop more faster.

Once i need to update a records behind, it take about 7-8min, is too slow.....

any idea?

 

[jmcilhinney]

That is far to vague a description to be useful. Why so secretive? Just show us the code.

 

[knockyo]

sorry here u go:

Do While Not stopFlag
            'pmCon.QueryMe("Select * from DELL_OPC_ADDRESS where processing <> 1 and Active=1", DTOPCAddress)
            pmCon.QueryMe("Select * from DELL_OPC_ADDRESS where processing <> 1 and Active=1 and OPCServer=" & OPCServerNo2Scan, DTOPCAddress)

            If DTOPCAddress.Rows.Count <> 0 Then
                For Each DR In DTOPCAddress.Rows
                    If Not OPCFunc.ReadOPCTag(DR("Address"), tmpValue) Then
                        'Log(DR("LifterId"), "*OPCtriggerSMS - Error reading data type (" & DR("triggerDataType") & ") address (" & DR("triggerAddress") & ")!")
                        Log(DR("LifterId"), "*OPCtriggerSMS - Error reading address (" & DR("Address") & ")!")
                        Application.DoEvents()
                        Continue For
                    End If

                    'Need to update RealTimeData so that this value can be reset. It is important for Address where OPC ResetValue='NO ACTION'
                    If tmpValue <> cNULL(DR("RealTimeData")) Then
                        pmCon.UpdateMe("Update DELL_OPC_ADDRESS set RealTimeData='" & tmpValue & "' where Address='" & DR("Address") & "'")
                    End If


                    If Not tmpValue Is Nothing Then
                        Dim NeedTrigger As Boolean = False
                        Select Case DR("triggerValue")
                            Case "EMPTY"
                                If CStr(tmpValue).Trim = "" Then NeedTrigger = True
                            Case "NONEMPTY"
                                If CStr(tmpValue).Trim <> "" Then NeedTrigger = True
                            Case "TRUE", "FALSE"
                                Try
                                    If CBool(tmpValue) = CBool(DR("triggerValue")) Then
                                        NeedTrigger = True
                                    End If
                                Catch ex As Exception

                                End Try
                            Case "NONZERO"
                                If IsNumeric(tmpValue) Then
                                    If CDbl(tmpValue) <> 0 Then NeedTrigger = True
                                End If
                            Case "0", "1", "2"
                                If tmpValue = DR("triggerValue") Then NeedTrigger = True
                        End Select

                        'Check if the TriggerValue stays there forever (due to ResetValue='NO ACTION').
                        'If yes, then make sure TriggerValue <> RealTimeData
                        If NeedTrigger And DR("ResetValue") = "NO ACTION" Then
                            If tmpValue = DR("RealTimeData") Then NeedTrigger = False
                        End If

                        If NeedTrigger Then 'If tmpValue.ToUpper = CType(DR("triggerValue"), String).ToUpper Then
                            Dim NewThread As New clsThread
                            NewThread.AssignValue(DR, tmpValue)
                            Dim T As New System.Threading.Thread(AddressOf NewThread.InvokeMethod)
                            T.Start()
                        End If
                    End If

                    'If Counter > 200 Then
                    ' If InStr(MainForm.lblTnDTriggerSMS.BackColor.ToString.ToUpper, "BLUE") > 0 Then
                    ' MainForm.lblTnDTriggerSMS.BackColor = Color.Gray
                    ' Else
                    ' MainForm.lblTnDTriggerSMS.BackColor = Color.Blue
                    ' End If
                    ' Counter = 0
                    ' Application.DoEvents()

                    ' '10-Nov-2004 Reset the processing=F when the permitted time is over.
                    pmCon.UpdateMe("Update DELL_OPC_ADDRESS set processing=0 where processing=1 and LastUpdateDT <= '" & _
                        Format(DateAdd(DateInterval.Minute, -1 * CDbl(MinB4ResetToFalse), Now), "dd-MMM-yyyy hh:mm:ss tt") & "'", True)


                    'Else
                    ' Counter += 1
                    'End If

                    Application.DoEvents()
                    FlushMemory()
                    'ContinueNext:
                    'DTOPCAddress.Dispose()
                Next
            End If

 

[jmcilhinney]

Well, those calls to Application.DoEvents won't be helping. That looks like a job for a background thread if you feel the need to call that.

Also, I'm not quite sure why you're executing the query each iteration of the loop. Does the value of OPCServerNo2Scan change somewhere else because it doesn't in that code.

It would also likely be more efficient to retrieve all the data as a batch, process it all, then save it all as a batch. All the data access is going to slow things down a bit. I have to say, I'm really not a fan of creating literal SQL statements on demand like that either.

Other than that it's hard to say. You should try profiling your code or at least timing certain sections of it to see where the time-consuming bits are. Then you can hone in on those areas and see if you can make them more efficient.

 

[cjard]

Since it contains hundred data need to scan and trigger, how i can make the loop more faster.

You can ask IBM if you can borrow BlueGene/L for a second?
http://en.wikipedia.org/wiki/Blue_Gene

But seriously, there is huge scope for improvement here. Repeatedly selecting a subset of records at a time via string concat is one of the lamest, slow ways to do SQL you can get.. Access via string ordinal on a datareader is slower than numerical. Running one update per iteration, again via string concat is very very slow (bulk transfer methods exist for most databases). Drowning your machine in potentially hundreds of foreground worker threads is a bad idea; it may be possible to have the threadpool use a background thread for the task.. Oh, and then you run more update statements with large amounts of client side data conversion..

Sorting out the data access methods and ensuring the database design is optimal would be where I would start, but theres potentially a lot of googling to be done before you'll reasonably be in a position to hit those issues

Hmm, sorry.. It would be better to pay some nerd who loves performance optimizing things to do this.. While I dont mean to assert that youre not up to the job, youre here and asking because youre absolutely stuck and cannot see in an instant why that code is so horrendous. The few people here that could advise you to do it, would probably spend longer advising than just rewriting it for you.. It's a job I'd pass to someone else, tbh

 

[knockyo]

hi,

Good advice, im okay. No problems to that. Have comment only have improvement.

Well, i will have my own reference and improve next time.

By this current status, is that i need to modify and redo again? Since i not enough time to do so.