Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: VB.NET
Before this, I encouter a problem when my for loop takes a very long time to execute and cause the time out. I have successfully solved the problem by using parallel loop which has been introduced by our friend here[^]
 
The problem now is, some data retrieved are wrong when compared to original data where the data has been taken. Is that anything to do with the loop?
Here is the code.
 
Parallel.For(0, dtMc.Rows.Count, Sub(i)
                                                                                       Dim mcno As String = dtMc.Rows(i)("MCNO_N")
 
                                                                                       Dim sqlPC As String = "Select PARTCODE_T FROM MC_PARTCODE_MASTER WHERE MCNO_N = '" & mcno & "'"
                                            Dim pc As String = DBLayer.Util.Data.MsSql.DBFunction.executeScalar(sqlPC, connEPJSetting)
 
    Dim sqlMcType As String = "SELECT MCTYPE_T FROM MC_MASTER WHERE MCNO_N = '" & mcno & "' AND DEPTID_T = 'PD1'  "
                                            Dim mctype As String = DBLayer.Util.Data.MsSql.DBFunction.executeScalar(sqlMcType, connEPJSetting)
 
                                            If Not mctype Is Nothing Or Not IsDBNull(mctype) Then
                                                type = "'" & mctype & "'"
                                            Else
                                                type = "NULL"
                                            End If
                                            If Not pc Is Nothing Then
                                                partcode = "'" & pc & "'"
 

                                                Dim sqlMpInspect As String = "SELECT HEADER.GROUPCODE_T, HEADER.MONTH_T,HEADER.SHOPGROUP_T, HEADER.MCNO_N, HEADER.PARTC" & _
                                                                                "ODE_T, HEADER.ACTCT_T, dbo.MCINFO_DETAILS.ROLEID_N, EPJ_Employee.dbo.E" & _
                                                                                "MPLOYEE_DATA.NAME_T FROM (SELECT GROUPCODE_T, MONTH_T, MCNO_N, PARTCOD" & _
                                                                                "E_T, ACTCT_T,  SHOPGROUP_T FROM dbo.MCINFO_HEADER WHERE (MONTH_T = (SELECT MAX(MONTH" & _
                                                                                "_T) AS MAXMTH FROM dbo.MCINFO_HEADER AS MCINFO_HEADER_1 WHERE (MCNO_N " & _
                                        "= '" & mcno & "'))) AND (MCNO_N = '" & mcno & "') AND (PARTCODE_T = '" & pc & "')) AS" & _
                                        " HEADER INNER JOIN dbo.MCINFO_DETAILS ON HEADER.MONTH_T = dbo.MCINFO_D" & _
                                        "ETAILS.MONTH_T AND HEADER.MCNO_N = dbo.MCINFO_DETAILS.MCNO_N AND HEADE" & _
                                        "R.PARTCODE_T = dbo.MCINFO_DETAILS.PARTCODE_T INNER JOIN EPJ_Employee.d" & _
                                        "bo.EMPLOYEE_DATA ON dbo.MCINFO_DETAILS.EMPID_T = EPJ_Employee.dbo.EMPL" & _
                                        "OYEE_DATA.EMPID_T WHERE (dbo.MCINFO_DETAILS.ROLEID_N = 2) "
                                                Dim dtInspect As DataRow = DBLayer.Util.Data.MsSql.DBFunction.executeRowScalar(sqlMpInspect, connEPJSetting)
 
                                                If Not dtInspect Is Nothing Then
                                                    act = "'" & dtInspect("ACTCT_T") & "'"
                                                    groupcode = "'" & dtInspect("GROUPCODE_T") & "'"
                                                    leader = "'" & dtInspect("NAME_T") & "'"
                                                    shopgroup = "'" & dtInspect("SHOPGROUP_T") & "'"
 
                                                    Dim sqlMpPlanning As String = "SELECT * FROM Q_MPPLANNING_MCLOC WHERE ITEM_CODE_T = '" & pc & "' "
                                                    Dim dtPlanning As DataRow = DBLayer.Util.Data.MsSql.DBFunction.executeRowScalar(sqlMpPlanning, connEPJSetting)
 

                                                    If Not dtPlanning Is Nothing Then
                                                        Sct = "'" & dtPlanning("CT_N") & "'"
                                                        matcode = "'" & dtPlanning("DIMENSION_T") & "'"
                                                        calibration = "'" & dtPlanning("CALIBRATION_T") & "'"
 
                                                    Else
                                                        Sct = "NULL"
                                                        matcode = "NULL"
                                                        calibration = "NULL"
                                                    End If
 
                                                Else
                                                    act = "NULL"
                                                    groupcode = "NULL"
                                                    leader = "NULL"
                                                    shopgroup = "NULL"
                                                    Sct = "NULL"
                                                    matcode = "NULL"
                                                    calibration = "NULL"
                                                End If
 
                                                Dim sqlMCStat As String = "SELECT MCSTATID_N FROM MCPLAN_DATA PLANDATA " & _
                                                                                             " WHERE PLANDATA.MCNO_N = '" & mcno & "' AND CONVERT(DATE, DATE_D, 102) = CONVERT(DATE, GETDATE(), 102) "
 
                                                Dim drMcStatus As DataRow = DBLayer.Util.Data.MsSql.DBFunction.executeRowScalar(sqlMCStat, connEPJSetting)
 

                                                If drMcStatus Is Nothing Then
                                                    stat = "NULL"
                                                Else
                                                    stat = "'" & drMcStatus("MCSTATID_N") & "'"
                                                End If
 

                                            Else
                                                Sct = "NULL"
                                                matcode = "NULL"
                                                calibration = "NULL"
                                                act = "NULL"
                                                groupcode = "NULL"
                                                leader = "NULL"
                                                stat = "NULL"
                                                partcode = "NULL"
                                                shopgroup = "NULL"
                                                'type = "NULL"

                                            End If
 
                                           Dim str As String = "UPDATE MC_INFO_TRACKING SET GROUP_T = " & groupcode & ", LEADER_T = " & leader & ", PARTCODE_T= " & partcode & " , ACTCT_T = " & act & ", MATCODE_T = " & matcode & ", CALIBER_T = " & calibration & ", MCSTAT_N = " & stat & ", DATEMODIFY_D = GETDATE(), STD_CT_T = " & Sct & " WHERE MCNO_N = '" & mcno & "'"
                                            Dim a As Object = DBLayer.Util.Data.MsSql.DBFunction.executeNonQuery(str, connEPJSetting)
 
                                            Dim strB As String = "UPDATE MC_INFO_TRACKING_HEADER SET MC_TYPE_CODE_T = " & type & " WHERE MCNO_N = '" & mcno & "'"
                                            Dim b As Object = DBLayer.Util.Data.MsSql.DBFunction.executeNonQuery(strB, connEPJSetting)
 
                                        End Sub)
 
Updated:
 
After doing some study on my code, I tried to retrieve just one data and debug it one by one. Look example below.
 
No	Item
701	00273991L-T7N
702	00361541L-T7N
703	00273991L-T7N
704	00361372L-T7S
705	0027268KL-T7N
 

see no 701 and 703. For no 703, the loop reverse back and filter Item for no 701. the item no for 703 should be 02725100L-T7N. Why is that happening?
Posted 17-Dec-12 23:21pm
snamyna1.2K
Edited 18-Dec-12 0:34am
v2
Comments
Sergey Alexandrovich Kryukov at 18-Dec-12 20:10pm
   
So, "successfully solved" was a big exaggeration... This is called "race condition", check it out.
—SA
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Was the question really "why is that happening"? If so, my respect. You don't just want to kick the problem until the results satisfy you, but want to understand the problem.
 
Very basically, here is why: http://en.wikipedia.org/wiki/Race_condition[^].
 
The solution? Well, this is not so easy. Perhaps you would need to go to sequential execution again. Basically, you need to identify dependencies and ensure proper sequence of operations according to them. Easier to say then to solve it. By the way, if and when you synchronize depending parts of elaboration, you gain in throughput may go down and even become negative.
 
—SA
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Hi Again,
 
It is impossible to see each line of code but it seems you are using any variable in the loop which is changing the by another thread of loop, while it is in use by previous one.
 
Here one thing I need to say, while using “Multithreading” you will have to very careful about variables. It can bring disaster to you program. Parallel Loop is new thing using multi threading inside. So you need to be careful.
 
Solution is: Intelligent use of variable.
 
RKS
  Permalink  
Comments
snamyna at 19-Dec-12 4:23am
   
This is my first time using parallel loop. I need some time to study more about it. I am now studying this article about parallel loop. http://msdn.microsoft.com/en-us/library/ff963552.aspx
 
Hope it can increase my understandings. In a meanwhie, hope somebody can really explain about this.
snamyna at 19-Dec-12 22:39pm
   
Hye Rahul.. can u take a look at my code and see which variables actually cause the value to mix up? Just take one example in my code.Tq
snamyna at 19-Dec-12 22:48pm
   
I have looked at Interlocked function when using Parallel Loop but i have no idea how to use it. So, i googled around and found a method which Interlocked is not necessary.
 
Private Sub b()
 
Dim sqlMc As String = "SELECT MCNO_N FROM MC_LAYOUTTEST"
Dim dtMc As DataTable = DBLayer.Util.Data.MsSql.DBFunction.BuildDataTable(sqlMc, connEPJSetting)
Dim mc = (From row In dtMc Select colB = row(0).ToString).ToArray
 
Parallel.For(0, mc.Length, Sub(x) mc(x) = getItem(mc))
 

End Sub
 
Function getItem(ByVal mc As String()) As String
 

 
Dim pc As String = ""
 
For Each d As Integer In mc
 
Dim sqlPC As String = "Select PARTCODE_T FROM MC_PARTCODE_MASTER WHERE MCNO_N = '" & d & "'"
pc = DBLayer.Util.Data.MsSql.DBFunction.executeScalar(sqlPC, connEPJSetting)
 
Dim str As String = "UPDATE MC_LAYOUTTEST SET PARTCODE_T= '" & pc & "' WHERE MCNO_N = '" & d & "'"
Dim a As Object = DBLayer.Util.Data.MsSql.DBFunction.executeNonQuery(str, connEPJSetting)
 

Next
 
Return pc
 

 

End Function
 
mc is declared as INT in database design. After executing the code, I successfully get the right item for each no. After finish executing all no, the loop passes items values to d variable to get pc value at this statement -> Dim sqlPC As String = "Select PARTCODE_T FROM MC_PARTCODE_MASTER WHERE MCNO_N = '" & d & "'".
Why is the loop didnt stop although all array items has been processed?
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

I moved all variable declaration inside the Parallel Loop function and it goes well. I dont know whether that's the best solution but I think that solves race condition occured in the function. Thanks all for the feedback. Smile | :)
  Permalink  
v2
Comments
Rahul K Singh at 23-Dec-12 23:52pm
   
Test it as much as possible. Good Luck :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web04 | 2.8.141022.1 | Last Updated 24 Dec 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100