Search code examples
vb.netnpgsql

Long-running Process with Responsive Form - Performance Improvements

So, I'm working on a library for my internal applications that interacts with our PostgreSQL database (amongst many other things). One requirement at the moment is that this library can dump data from the database to a file. I have something working, but I've been trying to improve its performance as much as possible. This is what I'm currently looking at:

Using COPYReader As NpgsqlCopyTextReader = CType(CIADB.DBConnection.BeginTextExport(COPYSQL), NpgsqlCopyTextReader)
    With COPYReader
        Dim stopWatch As New Stopwatch
        Dim ts As TimeSpan
        Dim elapsedTime As String

        ' ** FIRST ATTEMPT
        stopWatch.Start()
        Dim BufferText As String = .ReadLine

        Do While Not BufferText Is Nothing
            CurrentPosition += 1
            OutputFile.WriteLine(BufferText)

            If Not UpdateForm Is Nothing Then
                UpdateForm.UpdateProgress(Convert.ToInt32((CurrentPosition / MaxRecords) * 100))
            End If

            BufferText = .ReadLine
        Loop

        OutputFile.Flush()
        OutputFile.Close()

        stopWatch.Stop()
        ts = stopWatch.Elapsed
        elapsedTime = String.Format("{0:00}:{1:00}:{2:00}.{3:00}", ts.Hours, ts.Minutes, ts.Seconds, ts.Milliseconds / 10)

        ' ** FIRST ATTEMPT RESULTS
        ' ** Records Retrieved: 65358
        ' ** Time To Complete: 2:12.07
        ' ** Lines Written: 65358
        ' ** File Size: 8,166 KB

        ' ** SECOND ATTEMPT
        stopWatch.Start()

        Using TestOutputFile As New IO.StreamWriter(DestinationFile.FullName.Replace(".TXT", "_TEST.TXT"), False)
            TestOutputFile.Write(.ReadToEndAsync.Result)
        End Using

        stopWatch.Stop()
        ts = stopWatch.Elapsed
        elapsedTime = String.Format("{0:00}:{1:00}:{2:00}.{3:00}", ts.Hours, ts.Minutes, ts.Seconds, ts.Milliseconds / 10)

        ' ** SECOND ATTEMPT
        ' ** Records Retrieved: 65358
        ' ** Time To Complete: 1:04.01
        ' ** Lines Written: 65358
        ' ** File Size: 8,102 KB
    End With
End Using

I've run multiple tests of each method and come up with pretty much the same results. FIRST ATTEMPT takes about twice as long as SECOND ATTEMPT

Obviously, the UpdateForm.UpdateProgress method used in FIRST ATTEMPT (used to keep the form responsive and show the current progress of the export) is going to cause the process to take longer because of the form updating and such involved, not to mention the line-by-line writing to the file. This is pretty much exactly why I was looking at reducing the number of extra calls by doing a full dump in one line of code. The problem is that if I use the "one-liner", the form is completely unresponsive until the process is complete.

I've tried moving the code for the "all-in-one-shot" dump from the SECOND ATTEMPT into a separate Async method, but I'm extremely unfamiliar with async methods in general, so I'm (obviously) not doing it right:

Private Async Sub OutputToFile(ByVal COPYReader As NpgsqlCopyTextReader, ByVal DestinationFile As IO.FileInfo)
    ' ** METHOD 3
    Using TestOutputFile As New IO.StreamWriter(DestinationFile.FullName.Replace(".TXT", "_TEST.TXT"), False)
        Await TestOutputFile.WriteAsync(COPYReader.ReadToEndAsync.Result)
    End Using

    ' ** METHOD 3 RESULTS
    ' ** Records Retrieved: 65358
    ' ** Time To Complete: 0:15.07
    ' ** Lines Written: 34
    ' ** File Size: 4 KB
End Sub

One other thing to mention: I tried moving all of this to a BackgroundWorker, but I was getting some strange behavior when I tried to call my UpdateForm.UpdateProgress method which caused the application to completely skip the actual dumping process. I've currently given up on trying to get this onto a separate thread, but I'm still open to other suggestions. This is actually one of the smaller tables that I'm dumping, so I'm not looking forward to what one of the bigger ones will do.

Just for the sake of completeness, here's the UpdateForm class that I've implemented in my library for reusability across other applications:

Imports System.Windows.Forms

Namespace Common
    Public Class FormHandler
        Implements IDisposable

        Public Property ApplicationForm As Form
        Public Property ApplicationStatusLabel As Label
        Public Property ApplicationToolStripLabel As ToolStripStatusLabel
        Public Property ApplicationProgressBar As ProgressBar

        Private LabelVisibleState As Boolean = True
        Private ProgressBarVisibleState As Boolean = True
        Private CurrentStatusText As String
        Private CurrentProgress As Integer

        Public Sub New(ByVal AppForm As Form)
            ApplicationForm = AppForm
        End Sub

        Public Sub New(ByVal StatusLabel As Label, ByVal Progress As ProgressBar)
            ApplicationStatusLabel = StatusLabel
            ApplicationToolStripLabel = Nothing
            ApplicationProgressBar = Progress
            ApplicationForm = ApplicationProgressBar.Parent.FindForm

            LabelVisibleState = StatusLabel.Visible
            ProgressBarVisibleState = Progress.Visible

            With ApplicationProgressBar
                .Minimum = 0
                .Maximum = 100
                .Value = 0
                .Visible = True
            End With

            With ApplicationStatusLabel
                .Visible = True
                .Text = ""
            End With
        End Sub

        Public Sub New(ByVal StatusLabel As ToolStripStatusLabel, ByVal Progress As ProgressBar)
            ApplicationToolStripLabel = StatusLabel
            ApplicationStatusLabel = Nothing
            ApplicationProgressBar = Progress
            ApplicationForm = ApplicationProgressBar.Parent.FindForm

            LabelVisibleState = StatusLabel.Visible
            ProgressBarVisibleState = Progress.Visible

            With ApplicationProgressBar
                .Minimum = 0
                .Maximum = 100
                .Value = 0
                .Visible = True
            End With

            With ApplicationToolStripLabel
                .Visible = True
                .Text = ""
            End With
        End Sub

        Public Sub New(ByVal AppForm As Form, ByVal StatusLabel As Label, ByVal Progress As ProgressBar)
            ApplicationForm = AppForm
            ApplicationStatusLabel = StatusLabel
            ApplicationToolStripLabel = Nothing
            ApplicationProgressBar = Progress

            LabelVisibleState = StatusLabel.Visible
            ProgressBarVisibleState = Progress.Visible

            With ApplicationProgressBar
                .Minimum = 0
                .Maximum = 100
                .Value = 0
                .Visible = True
            End With

            With ApplicationStatusLabel
                .Visible = True
                .Text = ""
            End With
        End Sub

        Public Sub New(ByVal AppForm As Form, ByVal StatusLabel As ToolStripStatusLabel, ByVal Progress As ProgressBar)
            ApplicationForm = AppForm
            ApplicationToolStripLabel = StatusLabel
            ApplicationStatusLabel = Nothing
            ApplicationProgressBar = Progress

            LabelVisibleState = StatusLabel.Visible
            ProgressBarVisibleState = Progress.Visible

            With ApplicationProgressBar
                .Minimum = 0
                .Maximum = 100
                .Value = 0
                .Visible = True
            End With

            With ApplicationToolStripLabel
                .Visible = True
                .Text = ""
            End With
        End Sub

        Friend Sub UpdateProgress(ByVal StatusText As String, ByVal CurrentPosition As Integer, ByVal MaxValue As Integer)
            CurrentStatusText = StatusText
            CurrentProgress = Convert.ToInt32((CurrentPosition / MaxValue) * 100)
            UpdateStatus()
        End Sub

        Friend Sub UpdateProgress(ByVal StatusText As String, ByVal PercentComplete As Decimal)
            CurrentStatusText = StatusText
            CurrentProgress = Convert.ToInt32(PercentComplete)
            UpdateStatus()
        End Sub

        Friend Sub UpdateProgress(ByVal StatusText As String)
            CurrentStatusText = StatusText
            CurrentProgress = 0
            UpdateStatus()
        End Sub

        Friend Sub UpdateProgress(ByVal PercentComplete As Decimal)
            CurrentProgress = Convert.ToInt32(PercentComplete)
            UpdateStatus()
        End Sub

        Friend Sub UpdateProgress(ByVal CurrentPosition As Integer, ByVal MaxValue As Integer)
            CurrentProgress = Convert.ToInt32((CurrentPosition / MaxValue) * 100)
            UpdateStatus()
        End Sub

        Friend Sub ResetProgressUpdate()
            CurrentStatusText = ""
            CurrentProgress = 0
            UpdateStatus()
        End Sub

        Private Sub UpdateStatus()
            If Not ApplicationForm Is Nothing Then
                If ApplicationForm.InvokeRequired Then
                    Dim UpdateInvoker As New MethodInvoker(AddressOf UpdateStatus)

                    Try
                        ApplicationForm.Invoke(UpdateInvoker)
                    Catch ex As Exception
                        Dim InvokeError As New ErrorHandler(ex)

                        InvokeError.LogException()
                    End Try
                Else
                    UpdateApplicationProgress(CurrentStatusText)
                End If
            End If
        End Sub

        Friend Sub UpdateApplicationProgress(ByVal ProgressText As String)
            If Not ApplicationForm Is Nothing Then
                With ApplicationForm
                    If Not ProgressText Is Nothing Then
                        If Not ApplicationStatusLabel Is Nothing Then
                            ApplicationStatusLabel.Text = ProgressText
                        End If

                        If Not ApplicationToolStripLabel Is Nothing Then
                            ApplicationToolStripLabel.Text = ProgressText
                        End If
                    End If

                    If Not ApplicationProgressBar Is Nothing Then
                        ApplicationProgressBar.Value = CurrentProgress
                    End If
                End With

                ApplicationForm.Refresh()
                Application.DoEvents()
            End If
        End Sub

        Public Sub Dispose() Implements IDisposable.Dispose
            If Not ApplicationForm Is Nothing Then
                ApplicationForm.Dispose()
            End If

            If Not ApplicationStatusLabel Is Nothing Then
                ApplicationStatusLabel.Visible = LabelVisibleState
                ApplicationStatusLabel.Dispose()
            End If

            If Not ApplicationToolStripLabel Is Nothing Then
                ApplicationToolStripLabel.Visible = LabelVisibleState
                ApplicationToolStripLabel.Dispose()
            End If

            If Not ApplicationProgressBar Is Nothing Then
                ApplicationProgressBar.Visible = ProgressBarVisibleState
                ApplicationProgressBar.Dispose()
            End If
        End Sub
    End Class
End Namespace

EDIT

Per the suggestion in the comments from @the_lotus, I modified my FIRST ATTEMPT slightly to check the value of the current progress (I declared a CurrentProgress variable as an Integer), and it dramatically improved the time taken:

' ** FOURTH ATTEMPT
Using COPYReader As NpgsqlCopyTextReader = CType(CIADB.DBConnection.BeginTextExport(COPYSQL), NpgsqlCopyTextReader)
    With COPYReader
        Dim stopWatch As New Stopwatch
        Dim ts As TimeSpan
        Dim elapsedTime As String
        Dim CurrentProgress As Integer = 0

        stopWatch.Start()
        Dim BufferText As String = .ReadLine

        Do While Not BufferText Is Nothing
            CurrentPosition += 1
            OutputFile.WriteLine(BufferText)

            ' ** Checks to see if the value of the ProgressBar will actually
            ' ** be changed by the CurrentPosition before making a call to
            ' ** UpdateProgress.  If the value doesn't change, don't waste
            ' ** the call
            If Convert.ToInt32((CurrentPosition / MaxRecords) * 100) <> CurrentProgress Then
                CurrentProgress = Convert.ToInt32((CurrentPosition / MaxRecords) * 100)

                If Not UpdateForm Is Nothing Then
                    UpdateForm.UpdateProgress(CurrentProgress)
                End If
            End If

            BufferText = .ReadLine
        Loop

        OutputFile.Flush()
        OutputFile.Close()

        stopWatch.Stop()
        ts = stopWatch.Elapsed
        elapsedTime = String.Format("{0:00}:{1:00}:{2:00}.{3:00}", ts.Hours, ts.Minutes, ts.Seconds, ts.Milliseconds / 10)
    End With
End Using
' ** FOURTH ATTEMPT RESULTS
' ** Records Retrieved: 65358
' ** Time To Complete: 0:47.45
' ** Lines Written: 65358
' ** File Size: 8,166 KB

Of course, the form is a "little" less responsive than if I were making the call on every record, but I'm thinking it's worth the trade-off.

EDIT #2

So that I could minimize the amount of code I'd have to retype (read: "copy/paste") each time I use the UpdateProgress method, I've moved the test for changed values over there and it seems to be operating with the same performance improvements. Again, for the sake of completeness, here's the code for the two private methods involved in doing the actual progress/status update:

Private Sub UpdateStatus()
    If Not ApplicationForm Is Nothing Then
        If ApplicationForm.InvokeRequired Then
            Dim UpdateInvoker As New MethodInvoker(AddressOf UpdateStatus)

            Try
                ApplicationForm.Invoke(UpdateInvoker)
            Catch ex As Exception
                Dim InvokeError As New ErrorHandler(ex)

                InvokeError.LogException()
            End Try
        Else
            UpdateApplicationProgress()
        End If
    End If
End Sub

Private Sub UpdateApplicationProgress()
    Dim Changed As Boolean = False

    If Not ApplicationForm Is Nothing Then
        With ApplicationForm
            If Not CurrentStatusText Is Nothing Then
                If Not ApplicationStatusLabel Is Nothing Then
                    If ApplicationStatusLabel.Text <> CurrentStatusText Then
                        Changed = True
                        ApplicationStatusLabel.Text = CurrentStatusText
                    End If
                End If

                If Not ApplicationToolStripLabel Is Nothing Then
                    If ApplicationToolStripLabel.Text <> CurrentStatusText Then
                        Changed = True
                        ApplicationToolStripLabel.Text = CurrentStatusText
                    End If
                End If
            End If

            If Not ApplicationProgressBar Is Nothing Then
                If ApplicationProgressBar.Value <> CurrentProgress Then
                    Changed = True
                    ApplicationProgressBar.Value = CurrentProgress
                End If
            End If
        End With

        If Changed Then
            ApplicationForm.Refresh()
        End If

        Application.DoEvents()
    End If
End Sub

Doing it this way also came with the added benefit of returning some of the responsiveness to the form that was previously lost. I hope at least some of this code and information is helpful to someone out there.

Solution

  • You don't need to call UpdateProgress on each call. It's not necessary when the percentage didn't even move. Try to do a small check and only update the percentage when needed.

    It's also possible that the second attempt is faster because it doesn't go to the database. The data could be cached.