如何在vb.net中更快地进行循环？ [英] How to make for loop faster in vb.net?

问题描述

大家好，我正面临一个问题，我的for循环需要很长时间才能执行。 （约1分30秒，达到超时）。

做了一些修改之后;

1）将SQL查询简化为视图。

2）我转换它而不是通过数据表循环到arraylist。

3）使用for循环而不是每个。



成功执行大约需要1:10。我试图创建新的数据表，并使用批量复制插入到sql的表中，但仍然没有时间更新数据更新。



还应该在哪里我改变，执行时间不到1分钟？我担心将来，我需要在表格内检索更多需要更多时间的数据。



这是代码：

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)

使用此函数从oracle中检索数据并复制到sql中。这种方法可以节省大量时间。

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

推荐答案

我的唯一建议是，循环查询结果时的最佳做法是使用DataTable使用DataReader ..因为它在这种情况下快速设计



问候，

Hi,

My only suggestion was, the best practice when looping a query result was instead using DataTable use the DataReader.. because its design to be fast on this kind of scenario

Regards,

你可以看到这些链接。这是关于Parallel.For Loop。



http://msdn.microsoft.com/en-us/library/dd460713.aspx [ ^ ]



http://blogs.msdn.com/b/csharpfaq/archive/2010/06 /01/parallel-programming-in-net-framework-4-getting-started.aspx [ ^ ]

这篇关于如何在vb.net中更快地进行循环？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！