none
Do Loop in lieu of For Next RRS feed

  • Question

  • Is there a way to use a do loop for this expression to speed it up?  The table ranges from 600 to 800 rows and is purged daily to maintain that range (so it is somewhat definite).

    For

    index1 = 0 To myDataTable1.Rows.Count - 1

                        myDataTable1.Rows(index1).SetModified()

                  

    Nextindex1


    • Edited by LHendren Thursday, October 1, 2015 3:16 PM
    Thursday, October 1, 2015 3:16 PM

Answers

All replies

  • Why should a Do-Loop be faster than a For-Next?

    Dim Index As Long = 0
    Do While (Index < myDataTable1.Rows.Count)
      myDataTable1.Rows(Index).SetModified()
      Index = Index + 1
    Loop
    

    What can be faster, cause it does not rely on the Count resolution is:

    Do While (Not myDataTable1.Eof)
      myDataTable1.Current.SetModified()
      myDataTable1.MoveNext
    Loop

    But this depends on what data type (class) myDataTable is an instance of.

    Thursday, October 1, 2015 3:38 PM
  • I don't know if a For Each is faster, but you could benchmark the two methodologies and find the faster one:

    For Each row As DataRow In myDataTable1.Rows
        row.SetModified()
    Next
    


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Thursday, October 8, 2015 8:18 PM
  • Bonnie, the For/Next runs about 19 seconds for around 750 rows in the datatable while the For Each/Next runs about 17 seconds.  Still a long time using today's SQL on a local server.  This takes me back to my days using Do While/End Do methods in dBase2.0!

    Would a count of the rows ahead of time be of any value?

    Friday, October 9, 2015 2:11 PM
  • Are you sure that's not milliseconds rather than seconds? I just did a similar test with a lot more rows (almost 3000) and on average it took about 15 milliseconds. If you are indeed seeing this take this many seconds, then you are obviously doing something more in that loop.
     
    Just calling the DataRow.SetModified() method isn't hitting the database at all, so there's really no comparison between SQL Server and dBase 2!!  BTW, I used to do dBase III Plus, back in the day (late 80's, early 90's). I switched to FoxPro when 2.0 came out ... much better than dBase. ;0)  But, that was a long time ago!!!
     
    Also, I'm wondering why you are calling the .SetModified() method? What does your process look like? What are you trying to do?


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Friday, October 9, 2015 3:30 PM
  • Bonnie, I wish it were milliseconds but it is seconds.  You are right about the SQL Server comment - the datatables are in memory.  I have not needed to use a lot of Do/Loop or For/Next statements in ADO.net, so it is new for me.  Anyway, here is my code.  It works fine, and the For/Next is workable but the process time is hardly acceptable by today's standards.

    If tssUpdate = True Then

                Dim resultsDataTable1 As New DataTable()
                Dim worksheet1 As Worksheet = SpreadsheetControl1.Document.Worksheets.ActiveWorksheet

                Dim range1 As Range = worksheet1.Range("TSS!A17:N56")

                Dim index1 As Integer
                Dim dtTSSExportDataTable As DataTable = worksheet1.CreateDataTable(range1, False)

                worksheet1.Columns("I").CopyFrom(worksheet1.Columns("V"), PasteSpecial.Values)   ' copies the concentration result to the "Result" TableColumn to UPDATE

                Call FindRowsThatHaveErrors()

                Dim cnSQL1 As SqlConnection = dbLIMS.GetLIMSConnection
                Dim adapter1 As New SqlDataAdapter("SELECT SampleNo, PAprojId, PAprojName, STMTNAME, DateAndTime, TestId1a, TestType1a, Facility1a, " _
                                                 & "Results, AUnits1, SampleDate1a, Complete_Date, Comments1a, Dex_Row_Id " _
                                                 & "From LIMS.dbo.Analytical_Sample_Log_ResultsInfo", cnSQL1)

                Dim builder1 As New SqlCommandBuilder(adapter1)
                adapter1.UpdateCommand = builder1.GetUpdateCommand()
                Using New SqlCommandBuilder(adapter1)
                    adapter1.Fill(resultsDataTable1)

                    resultsDataTable1.PrimaryKey = New DataColumn() {resultsDataTable1.Columns("Dex_Row_Id")}
                    dtTSSExportDataTable = resultsDataTable1.Clone()

                    Dim exporter1 As DataTableExporter = worksheet1.CreateDataTableExporter(range1, dtTSSExportDataTable, False)
                    AddHandler exporter1.CellValueConversionError, AddressOf exporter_CellValueConversionError

                    exporter1.Options.ConvertEmptyCells = True
                    exporter1.Options.DefaultCellValueToColumnTypeConverter.EmptyCellValue = 0
                    exporter1.Options.SkipEmptyRows = True
                    exporter1.Export()

                    For index1 = 0 To resultsDataTable1.Rows.Count - 1
                        resultsDataTable1.Rows(index1).SetModified()
                    Next index1

                    resultsDataTable1.Merge(dtTSSExportDataTable)

                    Dim updateStatement1 As String = "UPDATE LIMS.dbo.Analytical_Sample_Log_ResultsInfo SET [SampleNo] = @SampleNo, [Results] = @Results " _
                                                   & "WHERE [Dex_Row_Id] = @Dex_Row_Id And Dex_Row_Id > 0"
                    Dim updateCommand1 As New SqlCommand(updateStatement1, cnSQL1)

                    updateCommand1.Parameters.Add("@SampleNo", SqlDbType.Int, 0, "SampleNo")
                    updateCommand1.Parameters.Add("@Results", SqlDbType.NChar, 10, "Results")
                    updateCommand1.Parameters.Add("@Dex_Row_Id", SqlDbType.Int, 0, "Dex_Row_Id")
                    adapter1.UpdateCommand = updateCommand1
                    adapter1.Update(resultsDataTable1)

                End Using

    End If 'tssUpdate = True

    Friday, October 9, 2015 3:50 PM
  • OK, then ... should I assume that you timed the entire process (from getting the data from both the spreadsheet and the database and then the update to the database) and not just the For/Next loop?

    If so, your slow-down may be the spreadsheet stuff (which, unfortunately, I know nothing about ... I've not used ADO.NET to access spreadsheets), but it's most likely the SQL Server update. Unfortunately, the "built-in" method for updating a database (with the Adapter.Update() method) is a lot slower than doing it directly yourself. I believe it makes two trips to the database to do that update.

    I think this would be a lot quicker if you "rolled your own" update, like this (plus, you can get rid of the For/Next that does the row.SetModified() ... it won't be necessary here):

    Dim updateStatement1 As String = "UPDATE LIMS.dbo.Analytical_Sample_Log_ResultsInfo SET [SampleNo] = @SampleNo, [Results] = @Results " _
                                    & "WHERE [Dex_Row_Id] = @Dex_Row_Id And Dex_Row_Id > 0"
    Dim updateCommand1 As New SqlCommand(updateStatement1, cnSQL1)
    adapter1.UpdateCommand = updateCommand1
    
    For Each row As DataRow In resultsDataTable1.Rows
        updateCommand1.Parameters.Clear()
        ' Not sure about the row column syntax, I think it is different for VB than C#
        ' This might be row.Items("SampleNo") instead
        updateCommand1.Parameters.AddWithValue("@SampleNo", row("SampleNo"))
        updateCommand1.Parameters.AddWithValue("@Results", row("Results"))
        updateCommand1.Parameters.AddWithValue("@Dex_Row_Id", row("Dex_Row_Id"))
        adapter1.ExecuteNonQuery()
    Next
    
    
    


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Friday, October 9, 2015 4:33 PM
  • Bonnie, I apologize for my mistake.  The timing issue is the UPDATE not the For/Next issue, making this thread off-topic.  I modified your suggested code slightly, and I am still about 18 seconds:

     Dim updateStatement1 As String = "UPDATE LIMS.dbo.Analytical_Sample_Log_ResultsInfo SET [SampleNo] = @SampleNo, [Results] = @Results " _
                                    & "WHERE [Dex_Row_Id] = @Dex_Row_Id And Dex_Row_Id > 0"
                    Dim updateCommand1 As New SqlCommand(updateStatement1, cnSQL1)
                    adapter1.UpdateCommand = updateCommand1
    
                    MsgBox("start timer")
    
                    For Each row As DataRow In resultsDataTable1.Rows
                        updateCommand1.Parameters.Clear()
                        updateCommand1.Parameters.AddWithValue("@SampleNo", row("SampleNo"))
                        updateCommand1.Parameters.AddWithValue("@Results", row("Results"))
                        updateCommand1.Parameters.AddWithValue("@Dex_Row_Id", row("Dex_Row_Id"))
                        adapter1.UpdateCommand = updateCommand1
                        cnSQL1.Open()
                        updateCommand1.ExecuteNonQuery()
                        cnSQL1.Close()
    
                    Next
    
                    MsgBox("quit timer")

    Friday, October 9, 2015 7:11 PM
  • Sorry about my code being a little off (using adapter1 when I should have been using updateCommand1). I knew that, it's just that with a copy/paste/change, I missed changing some of the stuff I should have. You don't need the adapter at all in this scenario.

    Don't open/close constantly ... try this instead:

    cnSQL1.Open()
    For Each row As DataRow In resultsDataTable1.Rows
        updateCommand1.Parameters.Clear()
        updateCommand1.Parameters.AddWithValue("@SampleNo", row("SampleNo"))
        updateCommand1.Parameters.AddWithValue("@Results", row("Results"))
        updateCommand1.Parameters.AddWithValue("@Dex_Row_Id", row("Dex_Row_Id"))
        ' adapter1.UpdateCommand = updateCommand1 --- comment out, do not need adapter
        updateCommand1.ExecuteNonQuery()
    Next
    cnSQL1.Close()
    


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Friday, October 9, 2015 7:47 PM
  • Bonnie, I saw the Open/Close code right after I posted.  I know better.  Anyway, here is the code but it still takes about 18 seconds. 

     MsgBox("start timer here")
    
                    Dim updateStatement1 As String = "UPDATE LIMS.dbo.Analytical_Sample_Log_ResultsInfo SET [SampleNo] = @SampleNo, [Results] = @Results " _
                                                   & "WHERE [Dex_Row_Id] = @Dex_Row_Id And Dex_Row_Id > 0"
                    Dim updateCommand1 As New SqlCommand(updateStatement1, cnSQL1)
                    adapter1.UpdateCommand = updateCommand1
                    cnSQL1.Open()
                    For Each row As DataRow In resultsDataTable1.Rows
                        updateCommand1.Parameters.Clear()
                        updateCommand1.Parameters.AddWithValue("@SampleNo", row("SampleNo"))
                        updateCommand1.Parameters.AddWithValue("@Results", row("Results"))
                        updateCommand1.Parameters.AddWithValue("@Dex_Row_Id", row("Dex_Row_Id"))
                        'adapter1.UpdateCommand = updateCommand1
                        updateCommand1.ExecuteNonQuery()
                    Next
                    cnSQL1.Close()
    
                    MsgBox("end timer here")

    Friday, October 9, 2015 8:06 PM
  • OK, let's change tactics ... because you have so many rows to update, you should probably do a Batch update. I didn't suggest this previously, because initially I wasn't sure where your bottle-neck was. I've never needed to use this functionality, because I've never had a large number of rows to update at one time, but I think it should be considerable faster! Anyway, take a look here:

    https://msdn.microsoft.com/en-us/library/aadf8fk2%28v=vs.110%29.aspx?f=255&MSPPError=-2147217396


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Friday, October 9, 2015 9:53 PM
  • I am still struggling and am not that familiar with Batch UPDATE, although it looks simple.  My datatable just won't UPDATE the database table - no errors or thrown exceptions.
     Dim batchSize As Int32 = 0
                    Dim adapter1a As New SqlDataAdapter
    
                    ShowResult(resultsDataTable1)        'correct results are shown in datatable
    
                    adapter1a.UpdateCommand = New SqlCommand("UPDATE LIMS.dbo.Analytical_Sample_Log_ResultsInfo SET [SampleNo] = @SampleNo, [Results] = @Results " _
                                                   & "WHERE [Dex_Row_Id] = @Dex_Row_Id And Dex_Row_Id > 0")
                    adapter1a.UpdateCommand.Parameters.Add("@SampleNo", SqlDbType.Int, 0, "SampleNo")
                    adapter1a.UpdateCommand.Parameters.Add("@Results", SqlDbType.NChar, 20, "Results")
                    adapter1a.UpdateCommand.Parameters.Add("@Dex_Row_Id", SqlDbType.Int, 0, "Dex_Row_Id")
                    adapter1a.UpdateCommand.UpdatedRowSource = UpdateRowSource.None
    
                    adapter1a.InsertCommand = New SqlCommand("INSERT INTO LIMS.dbo.Analytical_Sample_Log_ResultsInfo (SampleNo) VALUES (@SampleNo)", cnSQL1)
                    adapter1a.InsertCommand.Parameters.Add("@Results", SqlDbType.NChar, 20, "Results")
                    adapter1a.InsertCommand.UpdatedRowSource = UpdateRowSource.None
    
                    adapter1a.DeleteCommand = New SqlCommand("DELETE FROM LIMS.dbo.Analytical_Sample_Log_ResultsInfo", cnSQL1)
                    adapter1a.DeleteCommand.Parameters.Add("@Dex_Row_Id", SqlDbType.Int, 0, "Dex_Row_Id")
                    adapter1a.DeleteCommand.UpdatedRowSource = UpdateRowSource.None
    
                    Try
    
                        adapter1a.UpdateBatchSize = batchSize
    
                        ShowResult(resultsDataTable1)   'correct results are shown in datatable
    
                        adapter1a.Update(resultsDataTable1)
    
                    Catch ex As Exception
    
                    End Try

    Saturday, October 10, 2015 6:47 PM
  • Remember when I said that if you "roll your own", you won't need to do the .SetModified() loop? Well, since you're back to using adapter.Update(), you'll need that .SetModified() loop again, since the .Update() will only update Modified DataRows. I bet you forgot that part ....

    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Saturday, October 10, 2015 8:07 PM
  • Bonnie, yes I did forget to put the SetModified loop back into the code. It does work; however, no faster than the other methods. I'll mark this as answered because ALL of my questions have been answered.

    I am going to pursue MERGE inside a User Defined Type/Stored Procedure with the DataTable passed as a parameter to see if I can speed things up a bit.

    Thank you for your help!

    Monday, October 12, 2015 11:43 AM
  • In the FWIW department, this updates correctly in under one second for 2,500 rows.  I have passed the datatable as a parameter in a stored procedure

    Step 1 is Create User-defined Table Type

    -- ================================
    -- Create User-defined Table Type
    -- ================================
    USE LIMS
    GO
    
    -- Create the data type
    CREATE TYPE [dbo].[resultTable] AS TABLE 
    (
    [SampleNo]		[int]			NOT NULL,
    [PAprojid]		[nchar] 	(10)	NULL,
    [PAprojName]		[nchar] 	(100)	NULL,
    [STMTNAME]		[nchar] 	(85)	NULL,
    [DateAndTime]		[date]			NULL,
    [TestId1a]		[nchar] 	(3)	NULL,
    [TestType1a]		[nvarchar] 	(30) 	NULL,
    [Facility1a]		[nchar] 	(80)	NULL,
    [Results]		[nchar] 	(10)	NULL,
    [AUnits1]		[nchar] 	(15)	NULL,
    [SampleDate1a]	[date]			NULL,
    [Complete_Date] 	[date]			NULL,
    [Comments1a]		[nchar] 	(100)	NULL,
    [Dex_Row_Id]		[int]			NOT NULL
    
    PRIMARY KEY (Dex_Row_Id)
    )
    GO
    

    Step 2 is add the StoredProdure (I only need to UPDATE; however, you could modify the sp to UPDATE and INSERT)

    USE [LIMS]
    GO
    
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    ALTER PROCEDURE [dbo].[usp_Update_TestResults]
    	@tblResults resultTable READONLY
    AS
    BEGIN
    	SET NOCOUNT ON;
    
    	MERGE INTO LIMS.dbo.Analytical_Sample_Log_ResultsInfo P
    	USING @tblResults S
    	ON P.Dex_Row_Id = S.Dex_Row_Id
    	WHEN MATCHED THEN
    	UPDATE SET P.Results = S.Results;
    END
    
    Step 3 Add the VB code
                    For Each row As DataRow In resultsDataTable1.Rows
                        row.SetModified()
                    Next
    
                    resultsDataTable1.Merge(dtTSSExportDataTable)
    
                    Using updateCommand As New SqlCommand("usp_Update_TestResults")
                        updateCommand.Connection = cnSQL1
                        updateCommand.CommandType = CommandType.StoredProcedure
                        updateCommand.Parameters.AddWithValue("@tblResults", resultsDataTable1)
                        cnSQL1.Open()
                        updateCommand.ExecuteNonQuery()
                        cnSQL1.Close()
                    End Using
    

    Monday, October 12, 2015 3:31 PM
  • I'm so glad you found that and it's working for you!! Believe it or not, passing the DataTable as a parameter was the next thing I was going to suggest if the batch-updates didn't work well enough for you. I've never used either methodology, so I wasn't sure which would be best and didn't have an easy way to test it before posting my earlier suggestion.

    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Monday, October 12, 2015 5:17 PM