How to make for loop faster in vb.net?

See more:

VB

Hello all, i am facing an issue where my for loop takes long time to execute. (approx 1m 30 s which reach time out).
After doing some modification which are;
1) Simplify SQL Query into view.
2) Instead of looping thru datatable, I convert it to arraylist.
3) using for loop instead of for each.

it takes about 1:10 to successfully execute. I have tried to create new datatable and using bulk copy to insert into table in sql but still no changes in time consuming for the data to be updated.

Where else should I change so that it takes less than 1 min to execute? I am afraid in future, I need to retrieve more data to be updated inside the table that requires more time consuming.

Here is the code:

 getPartcode()

Dim Sct, matcode, calibration, partcode, shopgroup As String
Dim act, groupcode, leader As String
Dim stat As String

Dim sqlMc As String = "SELECT MCNO_N FROM MC_INFO_TRACKING_HEADER"
Dim dtMc As DataTable = DBLayer.Util.Data.MsSql.DBFunction.BuildDataTable(sqlMc, connEPJSetting)
        Dim colBValues = (From row In dtMc Select colB = row(0).ToString).ToArray

   For i As Integer = 0 To colBValues.Length - 1
  Dim mcNo As String = colBValues(i)
 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)

            If Not pc Is Nothing Then
                partcode = "'" & pc & "'"
                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


                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") & "'"

 Else
                    act = "NULL"
                    groupcode = "NULL"
                    leader = "NULL"
                    shopgroup = "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"


            End If


            Dim insStr 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 & ", SHOPGROUP_T = " & shopgroup & " WHERE MCNO_N = '" & mcNo & "'"

            Dim cmdinsrd As SqlCommand = New SqlCommand(insStr, ConnectionA)

            cmdinsrd.CommandTimeout = 0

            cmdinsrd.Connection.Open()

            cmdinsrd.ExecuteNonQuery()

            cmdinsrd.Connection.Close()


        Next

Dim sqlDate As String = "SELECT MAX(DATEMODIFY_D) FROM MC_INFO_TRACKING"
        Dim dte As DateTime = DBLayer.Util.Data.MsSql.DBFunction.executeScalar(sqlDate, connEPJSetting)

        Me.lblDate.Text = "Last updated on : " & dte
        PanelWait_ModalPopupExtender.Hide()

        getLocMC(Nothing, 0)

This function used to retrieve data from oracle and copy into sql. This method saves lot of time.

Private Sub getPartcode()

       Dim DeletePC As Object = DBLayer.Util.Data.MsSql.DBFunction.executeNonQuery("DELETE FROM MC_PARTCODE_MASTER ", connEPJSetting)

       Dim sqlPC As String = "Select DVLPER.PARAMETERMASTER.ADDITIONALNO, DVLPER.PARAMETERMASTER.CHARACTORITEM" & _
       " From(DVLPER.PARAMETERMASTER) Where DVLPER.PARAMETERMASTER.CLASSIFICATION1 = 'PQRIS' And" & _
       "  DVLPER.PARAMETERMASTER.CLASSIFICATION2 = 'MACHINENO'"
       Dim dt As DataTable = OracleDbLib.OracleDbBase.BuildDataTable(sqlPC)

       Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(connEPJSetting)

           bulkCopy.DestinationTableName = "dbo.MC_PARTCODE_MASTER"   ''''bulkcopy can insert ID

           bulkCopy.WriteToServer(dt)

       End Using



   End Sub