Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: VB.NET
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
Posted 3-Dec-12 15:44pm
snamyna1.3K
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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,
  Permalink  
Comments
snamyna at 5-Dec-12 4:38am
   
Do u mean that I need to change all method in the codes using datatable to datareader?
how about data retrieved using datarow? should it be changed to datareader too?
snamyna at 5-Dec-12 20:05pm
   
I have changed all datatable and datarow into data reader. Still time taken is 1:10. :(
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

  Permalink  
Comments
snamyna at 5-Dec-12 20:59pm
   
Hye Rahul..

Your suggestion really works!!! It only takes 13 seconds to update the data. It is really over my expectation! Thanks so much! I've gained new knowledge. :)
snamyna at 5-Dec-12 21:35pm
   
Ohh. forgot to mention.. I succeed decrease time consuming from 1:10 to only 17 seconds. :-)
Rahul K Singh at 5-Dec-12 23:07pm
   
:)

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

  Print Answers RSS
0 Suvendu Shekhar Giri 265
1 Sergey Alexandrovich Kryukov 235
2 Andy Lanng 185
3 PIEBALDconsult 180
4 Black_Rose 130
0 Sergey Alexandrovich Kryukov 6,360
1 OriginalGriff 5,878
2 Peter Leow 2,514
3 Maciej Los 2,263
4 Abhinav S 2,249


Advertise | Privacy | Mobile
Web03 | 2.8.150414.1 | Last Updated 4 Dec 2012
Copyright © CodeProject, 1999-2015
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