[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

VB .Net Code Running Extremely Slow

Posted on 2012-08-15
4
Medium Priority
?
455 Views
Last Modified: 2012-08-15
Hi all,

I have been trying unsuccessfully to figure out why another coders code is running so slow. And now this is becoming critical for my business.

I just can't seem to figure out where the slow down is. Each loop takes about 1.8 seconds which is unacceptable when I will be looping over 1.7 million times.  I believe the database is optimized and that the issue lies within the VB .Net code. If anyone could have a look and point me in the right direction I would be highly appreciative.

So far I have begun to change all string concatenation into StringBuilder appends but am seeing no time reduction. I think there is an overall algorithmic problem here slowing things down.

        While objSQLDataReader.Read()

            If RadioButtonPrintFiles.Checked Then

                If PrevBSP <> objSQLDataReader("BSP") Or (PrevProcessPriority <> objSQLDataReader("ProcessPriority") And (Not CheckBoxFixFile.Checked)) Or PrevStream <> objSQLDataReader("Segment") Or PrevMailIndicator <> objSQLDataReader("MailIndicator") Or FilePackCount >= 10000 Or PrevPostalSort <> objSQLDataReader("HasDPID") Then
                    If PackCount > 0 And PrevMailIndicator <> "PrintReturn" Then
                        AddCols = ""
                        AddVals = ""
                        AddItem(TextBoxPresJobNumber.Text, "JobNumber", "Number")
                        AddItem(OutputFileName, "Stream", "String")
                        AddItem(PrevBSP, "DPID_BSP", "String")
                        AddItem(PrevState, "State", "String")
                        AddItem(PackCount - StartPackCount, "Articles", "Number")
                        AddItem(StartPackCount + 1, "Start_Seq", "Number")
                        AddItem(PackCount, "End_Seq", "Number")
                        AddItem(PrevPostalSort, "PostalSort", "String")
                        SQLCommandPostal = "INSERT INTO Postal_Job_Manifest (" & Mid(AddCols, 1, Len(AddCols) - 1) & ") values (" & Mid(AddVals, 1, Len(AddVals) - 1) & ")"
                        objSQLCommandPostal = New SqlCommand(SQLCommandPostal, objSQLConnPostal)
                        objSQLCommandPostal.ExecuteNonQuery()
                        objSQLCommandPostal.Dispose()
                    End If
                    StartPackCount = PackCount
                    PrevBSP = objSQLDataReader("BSP")
                    PrevState = objSQLDataReader("TheState")
                    PrevPostalSort = objSQLDataReader("HasDPID")
                End If
                'End If

                'If RadioButtonPrintFiles.Checked Then
                If PrevMailIndicator <> objSQLDataReader("MailIndicator") Or (PrevProcessPriority <> objSQLDataReader("ProcessPriority") And (Not CheckBoxFixFile.Checked)) Or PrevStream <> objSQLDataReader("Segment") Or FilePackCount >= 10000 Then
                    If FilePackCount <> 50000 Then
                        RecDetailLine = RecDetailLine & "," & PackCount & "," & FilePackCount & ",," & RecMemberNumber & "," & RecMemberName
                        'RecDetailLine = RecDetailLine & "," & FilePackCount & "," & RecA4MIC & "," & RecA4Switch & "," & RecFollower & "," & DayNumber
                        outWriteRecDetails.WriteLine(RecDetailLine)
                        'RecA4MIC = 0
                        'RecA4Switch = 0
                        'RecFollower = 0
                        'outWriteA3.Close()
                        'If (Not CheckBoxFixFile.Checked) And False Then CreateScheduleItem(DBConnStr, DBConnStrScheduling, OutputFileName, PrevProcessPriority, TextBoxPresJobNumber.Text, TextBoxPreSGrouping.Text)
                        'outWriteA4.Close()
                        'If RadioButtonPrintFiles.Checked Then
                        'If GetFileSize(FilePath & "PresData\" & OutputFileName & "_A4.dta") = 0 Then DeleteFile(FilePath & "PresData\" & OutputFileName & "_A4.dta")
                        'Else
                        'If GetFileSize(FilePath & "CD\" & OutputFileName & "_A4.dta") = 0 Then DeleteFile(FilePath & "CD\" & OutputFileName & "_A4.dta")
                        'End If
                    End If
                    If FilePackCount = 10000 Then
                        FileCount = FileCount + 1
                    Else
                        FileCount = 1
                        PackCount = 0
                        StartPackCount = 0
                    End If
                    FilePackCount = 0
                    OutputFileName = TextBoxPresJobNumber.Text & Mid(objSQLDataReader("MailIndicator"), 1, 2)
                    If Not CheckBoxFixFile.Checked Then
                        OutputFileName = OutputFileName & "_Day" & DisplayNumber(objSQLDataReader("ProcessPriority"), 2)
                    End If
                    OutputFileName = OutputFileName & "_S" & DisplayNumber(objSQLDataReader("Segment"), 2)
                    OutputFileName = OutputFileName & "_File"
                    OutputFileName = OutputFileName & DisplayNumber(FileCount, 2)
                    RecDetailLine = OutputFileName & ",," & FileCount & ","
                    RecMemberNumber = objSQLDataReader("MemberNumber")
                    RecMemberName = objSQLDataReader("B4") & " " & objSQLDataReader("B7")
                    RecDetailLine = RecDetailLine & "," & PackCount + 1
                    If RadioButtonCD.Checked Then
                        outWrite = File.CreateText(FilePath & "CD\" & OutputFileName & "_A3.dta")
                        'outWriteA3 = File.CreateText(FilePath & "CD\" & OutputFileName & "_A3.dta")
                        'outWriteA4 = File.CreateText(FilePath & "CD\" & OutputFileName & "_A4.dta")
                        'CDA4PageNumber = 1
                        'CDA3PageNumber = 1
                    Else
                        outWrite = File.CreateText(FilePath & "PreSData\" & OutputFileName & ".dta")
                        'outWriteA3 = File.CreateText(FilePath & "PreSData\" & OutputFileName & "_A3.dta")
                        'outWriteA4 = File.CreateText(FilePath & "PreSData\" & OutputFileName & "_A4.dta")
                    End If
                    TextBoxFileName.Text = OutputFileName
                    PrevMailIndicator = objSQLDataReader("MailIndicator")
                    PrevStream = objSQLDataReader("Segment")
                    'PrevMICIndicator = objSQLDataReader("MICIndicator")
                    PrevProcessPriority = objSQLDataReader("ProcessPriority")
                End If
            End If

            If RadioButtonCD.Checked Then
                If FilePackCount >= 10000 Then
                    If FilePackCount <> 50000 Then
                        outWrite.Close()
                    End If
                    FileCount = FileCount + 1
                    FilePackCount = 0
                    PackCount = 0
                    OutputFileName = TextBoxPresJobNumber.Text & "CDFile"
                    OutputFileName = OutputFileName & DisplayNumber(FileCount, 3)
                    outWrite = File.CreateText(FilePath & "CD\" & OutputFileName & "_A3.dta")
                    'CDA3PageNumber = 1
                    CDLetterPageNumber = 1
                    TextBoxFileName.Text = OutputFileName
                    CDErrorStarted = False
                End If
            End If

            If RadioButtonViewersMIC.Checked Then
                If (PrevStream <> objSQLDataReader("Segment") Or PrevMICIndicator <> objSQLDataReader("MICIndicator")) Then
                    If FilePackCount <> 50000 Then
                        outWrite.Close()
                    End If
                    OutputFileName = TextBoxPresJobNumber.Text & "View" & TextBoxPreSGrouping.Text & "_S" & objSQLDataReader("Segment")
                    If objSQLDataReader("MICIndicator") = 0 Then
                        OutputFileName = OutputFileName & "_NoMIC"
                    Else
                        OutputFileName = OutputFileName & "_MIC"
                    End If
                    FileCount = 1
                    PackCount = 0
                    FilePackCount = 0
                    outWrite = File.CreateText(FilePath & "PreSData\" & OutputFileName & ".dta")
                    TextBoxFileName.Text = OutputFileName
                    PrevStream = objSQLDataReader("Segment")
                    PrevMICIndicator = objSQLDataReader("MICIndicator")
                End If
            End If

            TempName = ""
            If objSQLDataReader("B4") <> "" Then
                TempName = TempName & objSQLDataReader("B4") & " "
            End If
            If objSQLDataReader("B5") <> "" Then
                TempName = TempName & objSQLDataReader("B5") & " "
            End If
            If objSQLDataReader("B6") <> "" Then
                TempName = TempName & objSQLDataReader("B6") & " "
            End If
            TempName = TempName & objSQLDataReader("B7")

            If RadioButtonCD.Checked Then
                If TextBoxPresJobNumber.Text = "51231" Then
                    outWriteCDPageIndex.WriteLine(TextBoxPresJobNumber.Text & Chr(9) & objSQLDataReader("MemberNumber") & Chr(9) & OutputFileName & "_A3_Lett.pdf" & Chr(9) & CDLetterPageNumber & Chr(9) & "1" & Chr(9) & "21" & Chr(9) & objSQLDataReader("B7") & Chr(9) & TempName)
                    CDLetterPageNumber += 1
                End If
                If objSQLDataReader("Segment") <= 5 Then
                    outWriteCDPageIndex.WriteLine(TextBoxPresJobNumber.Text & Chr(9) & objSQLDataReader("MemberNumber") & Chr(9) & OutputFileName & "_A3.pdf" & Chr(9) & CDA3PageNumber & Chr(9) & "1" & Chr(9) & "1" & Chr(9) & objSQLDataReader("B7") & Chr(9) & TempName)
                    CDA3PageNumber = CDA3PageNumber + 1
                    outWriteCDPageIndex.WriteLine(TextBoxPresJobNumber.Text & Chr(9) & objSQLDataReader("MemberNumber") & Chr(9) & OutputFileName & "_A3.pdf" & Chr(9) & CDA3PageNumber & Chr(9) & "1" & Chr(9) & "2" & Chr(9) & objSQLDataReader("B7") & Chr(9) & TempName)
                    CDA3PageNumber = CDA3PageNumber + 1
                Else
                    outWriteCDPageIndex.WriteLine(TextBoxPresJobNumber.Text & Chr(9) & objSQLDataReader("MemberNumber") & Chr(9) & OutputFileName & "_A3.pdf" & Chr(9) & CDA3PageNumber & Chr(9) & "1" & Chr(9) & "3" & Chr(9) & objSQLDataReader("B7") & Chr(9) & TempName)
                    CDA3PageNumber = CDA3PageNumber + 1
                    outWriteCDPageIndex.WriteLine(TextBoxPresJobNumber.Text & Chr(9) & objSQLDataReader("MemberNumber") & Chr(9) & OutputFileName & "_A3.pdf" & Chr(9) & CDA3PageNumber & Chr(9) & "1" & Chr(9) & "4" & Chr(9) & objSQLDataReader("B7") & Chr(9) & TempName)
                    CDA3PageNumber = CDA3PageNumber + 1
                End If
            End If

            'NumberIntelPages = objSQLDataReader("FollowerPages") + objSQLDataReader("MICIndicator")
            'RecA4MIC += objSQLDataReader("MICIndicator")

            PackCount = PackCount + 1
            FilePackCount = FilePackCount + 1
            '            A4HasRecords = False
            'HoldHD1 = ""

            TextBox2.Text = PackCount
            MemberNumber = objSQLDataReader("MemberNumber")
            TextBoxRemoveMember.Text = MemberNumber
            System.Windows.Forms.Application.DoEvents()

            'GoTo skipsum

            'LineOut = "HD1~" & MemberNumber & "~" & objSQLDataReader("Segment") & "~" & objSQLDataReader("MICIndicator") & "~" & PackCount & "~" & NumberIntelPages & "~" & objSQLDataReader("21") & "~" & objSQLDataReader("B1") & "~" & objSQLDataReader("C1") & "~"
            LineOutSB.Append("HD1~")
            LineOutSB.Append(MemberNumber)
            LineOutSB.Append("~")
            LineOutSB.Append(objSQLDataReader("Segment"))
            LineOutSB.Append("~")
            LineOutSB.Append(objSQLDataReader("MICIndicator"))
            LineOutSB.Append("~")
            LineOutSB.Append(PackCount)
            LineOutSB.Append("~")
            LineOutSB.Append(NumberIntelPages)
            LineOutSB.Append("~")
            LineOutSB.Append(objSQLDataReader("21"))
            LineOutSB.Append("~")
            LineOutSB.Append(objSQLDataReader("B1"))
            LineOutSB.Append("~")
            LineOutSB.Append(objSQLDataReader("C1"))
            LineOutSB.Append("~")
            'LineOut = LineOutSB.ToString
            'LineOutSB.Length = 0



            SQLCommand2 = "SELECT * FROM [Statement_Address] WHERE MemberNumber = '" & MemberNumber & "' AND JobNumber = " & TextBoxPresJobNumber.Text & " AND Grouping = '" & TextBoxPreSGrouping.Text & "'"
            objSQLCommand2 = New SqlCommand(SQLCommand2, objSQLConn2)
            objSQLDataReader2 = objSQLCommand2.ExecuteReader()
            objSQLDataReader2.Read()

            HoldName = TempName
            'LineOut = LineOut & UpperLower(TempName) & "~" & UpperLower(objSQLDataReader2("31").ToString) & "~" & UpperLower(objSQLDataReader2("32").ToString)
            LineOutSB.Append(UpperLower(TempName))
            LineOutSB.Append("~")
            LineOutSB.Append(UpperLower(objSQLDataReader2("31").ToString))
            LineOutSB.Append("~")
            LineOutSB.Append(UpperLower(objSQLDataReader2("32").ToString))

            If objSQLDataReader2("34").ToString = "" Then
                'LineOut = LineOut & "~" & objSQLDataReader2("33")
                LineOutSB.Append("~")
                LineOutSB.Append(objSQLDataReader2("33"))
            Else
                'LineOut = LineOut & "~" & UpperLower(objSQLDataReader2("33"))
                LineOutSB.Append("~")
                LineOutSB.Append(UpperLower(objSQLDataReader2("33")))
            End If
            If objSQLDataReader2("35").ToString = "" Then
                'LineOut = LineOut & "~" & objSQLDataReader2("34")
                LineOutSB.Append("~")
                LineOutSB.Append(objSQLDataReader2("34"))
            Else
                'LineOut = LineOut & "~" & UpperLower(objSQLDataReader2("34"))
                LineOutSB.Append("~")
                LineOutSB.Append(UpperLower(objSQLDataReader2("34")))
            End If
            If objSQLDataReader2("36").ToString = "" Then
                'LineOut = LineOut & "~" & objSQLDataReader2("35")
                LineOutSB.Append("~")
                LineOutSB.Append(objSQLDataReader2("35"))
            Else
                'LineOut = LineOut & "~" & UpperLower(objSQLDataReader2("35"))
                LineOutSB.Append("~")
                LineOutSB.Append(UpperLower(objSQLDataReader2("35")))
            End If
            'LineOut = LineOut & "~" & objSQLDataReader2("36")
            LineOutSB.Append("~")
            LineOutSB.Append(objSQLDataReader2("36"))

            ' Letter Salutation
            If Len(objSQLDataReader("B4")) > 1 Then
                'LineOut = LineOut & "~" & UpperLower(objSQLDataReader("B4") & " " & objSQLDataReader("B7"))
                LineOutSB.Append("~")
                LineOutSB.Append(UpperLower(objSQLDataReader("B4")))
                LineOutSB.Append(" ")
                LineOutSB.Append(objSQLDataReader("B7"))

                'LineOut = LineOut & "~" & UpperLower(objSQLDataReader2("31"))
                LineOutSB.Append("~")
                LineOutSB.Append(UpperLower(objSQLDataReader2("31")))
            Else
                'LineOut = LineOut & "~Member"
                LineOutSB.Append("~Member")

                'LineOut = LineOut & "~" & UpperLower(objSQLDataReader("B5") & " " & objSQLDataReader("B7"))
                LineOutSB.Append("~")
                LineOutSB.Append(UpperLower(objSQLDataReader("B5")))
                LineOutSB.Append(" ")
                LineOutSB.Append(objSQLDataReader("B7"))
            End If

            objSQLDataReader2.Close()
            objSQLCommand2.Dispose()

            'LineOut = LineOut & "~"
            LineOutSB.Append("~")

            'If objSQLDataReader("MICIndicator") > 0 Or objSQLDataReader("Followerpages") > 0 Then
            'LineOut = LineOut & "Demand"
            'End If

            'LineOut = LineOut & "~" & TextBoxPresJobNumber.Text & "~" & TextBoxPreSGrouping.Text
            LineOutSB.Append("~")
            LineOutSB.Append(TextBoxPresJobNumber.Text)
            LineOutSB.Append("~")
            LineOutSB.Append(TextBoxPreSGrouping.Text)
            LineOut = LineOutSB.ToString
            LineOutSB.Length = 0

            If RadioButtonPrintFiles.Checked Or RadioButtonReprints.Checked Or RadioButtonCD.Checked Then
                outWrite.WriteLine(LineOut)
                HoldHD1 = LineOut
            Else
                outWrite.WriteLine(LineOut)
            End If

            ' MIC Detail Values
            If objSQLDataReader("MICIndicator").ToString = "" Then
                'LineOut = "HD5~" & MemberNumber & "~" & objSQLDataReader("Segment") & "~Balanced"
                LineOutSB.Append("HD5~")
                LineOutSB.Append(MemberNumber)
                LineOutSB.Append("~")
                LineOutSB.Append(objSQLDataReader("Segment")
                LineOutSB.Append("~Balanced")

                'LineOut = LineOut & "~" & FormatDollar(objSQLDataReader("G7"))
                LineOutSB.Append("~")
                LineOutSB.Append(FormatDollar(objSQLDataReader("G7")))

                'LineOut = LineOut & "~" & FormatPercentage(100)
                LineOutSB.Append("~")
                LineOutSB.Append(FormatPercentage(100))
                If RadioButtonPrintFiles.Checked Or RadioButtonReprints.Checked Or RadioButtonCD.Checked Then
                    outWrite.WriteLine(LineOut)
                    'outWriteA3.WriteLine(LineOut)
                    'outWriteA4.WriteLine(LineOut)
                    'A4HasRecords = True
                Else
                    outWrite.WriteLine(LineOut)
                End If
            Else
                SQLCommand2 = "SELECT * FROM [Statement_MIC] INNER JOIN [MIC_Reference] ON MIC_ShortDesc = MIC_Code WHERE (MIC_Value10 <> 0 OR MIC_Value11 <> 0) AND MemberNumber = '" & MemberNumber & "' AND JobNumber = " & TextBoxPresJobNumber.Text & " AND Grouping = '" & TextBoxPreSGrouping.Text & "' ORDER BY Print_Order"
                objSQLCommand2 = New SqlCommand(SQLCommand2, objSQLConn2)
                objSQLDataReader2 = objSQLCommand2.ExecuteReader()
                While objSQLDataReader2.Read()
                    LineOut = "HD5~" & MemberNumber & "~" & objSQLDataReader("Segment") & "~" & objSQLDataReader2("Print_Description1")
                    NoValues = True
                    For Idx1 = 1 To 11
                        If objSQLDataReader2("MIC_Value" & Idx1) <> 0 Then
                            NoValues = False
                        End If
                    Next
                    If Not NoValues Then
                        LineOut = LineOut & "~" & FormatDollar(objSQLDataReader2("MIC_Value10"))
                        LineOut = LineOut & "~" & FormatPercentage(objSQLDataReader2("MIC_Value11"))
                        If RadioButtonPrintFiles.Checked Or RadioButtonReprints.Checked Or RadioButtonCD.Checked Then
                            outWrite.WriteLine(LineOut)
                            'outWriteA4.WriteLine(LineOut)
                            'A4HasRecords = True
                        Else
                            outWrite.WriteLine(LineOut)
                        End If
                    End If
                End While
                objSQLDataReader2.Close()
                objSQLCommand2.Dispose()
            End If

            LineOut = "HD5~" & MemberNumber & "~" & objSQLDataReader("Segment") & "~Total"
            LineOut = LineOut & "~" & FormatDollar(objSQLDataReader("G7"))
            LineOut = LineOut & "~" & FormatPercentage(100)
            If RadioButtonPrintFiles.Checked Or RadioButtonReprints.Checked Or RadioButtonCD.Checked Then
                outWrite.WriteLine(LineOut)
                'outWriteA3.WriteLine(LineOut)
                'outWriteA4.WriteLine(LineOut)
                'A4HasRecords = True
            Else
                outWrite.WriteLine(LineOut)
            End If

            LineOut = "HD2~" & MemberNumber & "~" & objSQLDataReader("Segment")

            ' Opening balance
            SQLCommand2 = "SELECT ROC_Value5 FROM [Statement_ROC] WHERE MemberNumber = '" & MemberNumber & "' AND ROC_Description = 'OPENING BALANCE' AND JobNumber = " & TextBoxPresJobNumber.Text & " AND Grouping = '" & TextBoxPreSGrouping.Text & "'"
            objSQLCommand2 = New SqlCommand(SQLCommand2, objSQLConn2)
            objSQLDataReader2 = objSQLCommand2.ExecuteReader()
            If objSQLDataReader2.Read() Then
                If objSQLDataReader2("ROC_Value5").ToString <> "" Then
                    LineOut = LineOut & "~" & FormatDollar(objSQLDataReader2("ROC_Value5"))
                Else
                    LineOut = LineOut & "~$0.00"
                End If
            Else
                LineOut = LineOut & "~$0.00"
            End If
            objSQLDataReader2.Close()
            objSQLCommand2.Dispose()

            ' Contributions & Transfers out
            LineOut = LineOut & "~" & FormatDollar(objSQLDataReader("G8")) + "~" + FormatDollar(objSQLDataReader("G9"))

            ' Withdrawals/Transfers Out
            LineOut = LineOut & "~" & FormatDollar(objSQLDataReader("G10"))

            ' Fees/Charges
            ' Change request 4, alteration to calculation of fees and charges
            SQLCommand2 = "SELECT sum(ROC_Value5) as TotFee FROM [Statement_ROC] WHERE MemberNumber = '" & MemberNumber & "' AND ControlCode = '9' AND JobNumber = " & TextBoxPresJobNumber.Text & " AND Grouping = '" & TextBoxPreSGrouping.Text & "' AND " & _
                         "(ROC_Description = 'Account Keeping Fee' OR " & _
                         "ROC_Description = 'Withdrawal Fee' OR " & _
                         "ROC_Description = 'Investment Switching Fee' OR " & _
                         "ROC_Description = 'Location Fee' OR " & _
                         "ROC_Description = 'Family Law Splitting Fee' OR " & _
                         "ROC_Description = 'Super Advice Fee' OR " & _
                         "ROC_Description = 'Benefit Fee' OR " & _
                         "ROC_Description = 'Member Benefit Protection Rebate' OR " & _
                         "ROC_Description = 'No TFN Tax Refund ' OR " & _
                         "ROC_Description = 'Lost Member Protection Rebate' OR " & _
                         "ROC_Description = 'Antidetriment cont tax refund' OR " & _
                         "ROC_Description = 'Surcharge Tax' OR " & _
                         "ROC_Description = 'No TFN Tax' OR " & _
                         "ROC_Description = 'Surcharge tax Interest')"
            objSQLCommand2 = New SqlCommand(SQLCommand2, objSQLConn2)
            objSQLDataReader2 = objSQLCommand2.ExecuteReader()
            If objSQLDataReader2.Read() Then
                If objSQLDataReader2("TotFee").ToString <> "" Then
                    LineOut = LineOut & "~" & FormatDollar(objSQLDataReader2("TotFee"))
                Else
                    LineOut = LineOut & "~$0.00"
                End If
            Else
                LineOut = LineOut & "~$0.00"
            End If
            objSQLDataReader2.Close()
            objSQLCommand2.Dispose()

            ' Insurance Fees
            SQLCommand2 = "SELECT ROC_Value5 FROM [Statement_ROC] WHERE MemberNumber = '" & MemberNumber & "' AND ROC_Description = 'Insurance Premiums' AND JobNumber = " & TextBoxPresJobNumber.Text & " AND Grouping = '" & TextBoxPreSGrouping.Text & "'"
            objSQLCommand2 = New SqlCommand(SQLCommand2, objSQLConn2)
            objSQLDataReader2 = objSQLCommand2.ExecuteReader()
            If objSQLDataReader2.Read() Then
                If objSQLDataReader2("ROC_Value5").ToString <> "" Then
                    LineOut = LineOut & "~" & FormatDollar(objSQLDataReader2("ROC_Value5"))
                Else
                    LineOut = LineOut & "~$0.00"
                End If
            Else
                LineOut = LineOut & "~$0.00"
            End If
            objSQLDataReader2.Close()
            objSQLCommand2.Dispose()

            ' Federal Govt Taxes
            ' Possible change below for Contributions tax on form
            'SQLCommand2 = "SELECT sum(ROC_Value5) as TotFee FROM [Statement_ROC] WHERE MemberNumber = '" & MemberNumber & "' AND ControlCode = '9' AND JobNumber = " & TextBoxPresJobNumber.Text & " AND Grouping = '" & TextBoxPreSGrouping.Text & "' AND " & _
            '             "(ROC_Description = 'Contributions Tax' OR " & _
            '            "ROC_Description = 'No TFN Tax' OR " & _
            '           "ROC_Description = 'Transfer In Tax' OR " & _
            '          "ROC_Description = 'Surcharge Payments')"

            SQLCommand2 = "SELECT sum(ROC_Value5) as TotFee FROM [Statement_ROC] WHERE MemberNumber = '" & MemberNumber & "' AND ControlCode = '9' AND JobNumber = " & TextBoxPresJobNumber.Text & " AND Grouping = '" & TextBoxPreSGrouping.Text & "' AND " & _
                          "(ROC_Description = 'Contributions Tax' OR " & _
                          "ROC_Description = 'Transfer In Tax' OR " & _
                          "ROC_Description = 'Surcharge Payments')"

            objSQLCommand2 = New SqlCommand(SQLCommand2, objSQLConn2)
            objSQLDataReader2 = objSQLCommand2.ExecuteReader()
            If objSQLDataReader2.Read() Then
                If objSQLDataReader2("TotFee").ToString <> "" Then
                    LineOut = LineOut & "~" & FormatDollar(objSQLDataReader2("TotFee"))
                Else
                    LineOut = LineOut & "~$0.00"
                End If
            Else
                LineOut = LineOut & "~$0.00"
            End If
            objSQLDataReader2.Close()
            objSQLCommand2.Dispose()

            ' Earnings
            SQLCommand2 = "SELECT * FROM [Statement_ROC] WHERE MemberNumber = '" & MemberNumber & "' AND ControlCode = 'A' AND ROC_Description = 'Investment Earnings' AND JobNumber = " & TextBoxPresJobNumber.Text & " AND Grouping = '" & TextBoxPreSGrouping.Text & "'"
            objSQLCommand2 = New SqlCommand(SQLCommand2, objSQLConn2)
            objSQLDataReader2 = objSQLCommand2.ExecuteReader()
            If objSQLDataReader2.Read() Then
                If objSQLDataReader2("ROC_Value5").ToString <> "" Then
                    LineOut = LineOut & "~" & FormatDollar(objSQLDataReader2("ROC_Value5"))
                Else
                    LineOut = LineOut & "~$0.00"
                End If
            Else
                LineOut = LineOut & "~$0.00"
            End If
            objSQLDataReader2.Close()
            objSQLCommand2.Dispose()

            'Closing Balance
            LineOut = LineOut & "~" & FormatDollar(objSQLDataReader("G7"))

            LineOut = LineOut & "~" & FormatDollar(objSQLDataReader("02")) & "~" & FormatDollar(objSQLDataReader("03")) & "~" & FormatDollar(objSQLDataReader("04"))
            LineOut = LineOut & "~" & objSQLDataReader("Filename") & "~" & objSQLDataReader("C3") & "~" & objSQLDataReader("C2") & "~"
            LineOut += Format(CDate(objSQLDataReader("C4")), "dd MMMM yyyy") & "~" & objSQLDataReader("B2")

            If RadioButtonPrintFiles.Checked Or RadioButtonReprints.Checked Or RadioButtonCD.Checked Then
                outWrite.WriteLine(LineOut)
            Else
                outWrite.WriteLine(LineOut)
            End If

            LineOut = "HD6~" & MemberNumber & "~" & objSQLDataReader("Segment")

            MaxValue = 0

            For Idx1 = 4 To 7
                LineOut = LineOut & "~$" & FormatNumber(objSQLDataReader("G" & Idx1), 0)
                If objSQLDataReader("G" & Idx1) > MaxValue Then
                    MaxValue = objSQLDataReader("G" & Idx1)
                End If
            Next
            LineOut = LineOut & "~" & FormatNumber(MaxValue, 2)

            If RadioButtonPrintFiles.Checked Or RadioButtonReprints.Checked Or RadioButtonCD.Checked Then
                outWrite.WriteLine(LineOut)
            Else
                outWrite.WriteLine(LineOut)
            End If

            ' Insurance Benefits
            LineOut = "HD4~" & MemberNumber & "~" & objSQLDataReader("Segment") & "~"

            If objSQLDataReader("09") <> "N/A" Then
                LineOut = LineOut & "Fixed~"
                LineOut = LineOut & FormatDollar(objSQLDataReader("09")) & "~"
            Else
                LineOut = LineOut & Mid(objSQLDataReader("08"), InStr(1, objSQLDataReader("08"), "/") + 1)
                LineOut = LineOut & "~"
                If FormatDollar(objSQLDataReader("07")) = "NIL" Then
                    LineOut = LineOut & "NIL~"
                Else
                    LineOut = LineOut & FormatDollar(objSQLDataReader("07")) & "~"
                End If
            End If

            If objSQLDataReader("012") <> "N/A" Then
                LineOut = LineOut & "Fixed~"
                LineOut = LineOut & FormatDollar(objSQLDataReader("012")) & "~"
            Else
                LineOut = LineOut & Mid(objSQLDataReader("011"), InStr(1, objSQLDataReader("011"), "/") + 1)
                LineOut = LineOut & "~"
                If FormatDollar(objSQLDataReader("010")) = "NIL" Then
                    LineOut = LineOut & "NIL~"
                Else
                    LineOut = LineOut & FormatDollar(objSQLDataReader("010")) & "~"
                End If
            End If

            If objSQLDataReader("P1") <> 0 Then
                LineOut = LineOut & FormatDollar(objSQLDataReader("P1")) & " per annum"
            Else
                LineOut = LineOut & "NIL"
            End If

            'Check Insurance Status
            If objSQLDataReader("09") <> "N/A" Or objSQLDataReader("012") <> "N/A" Then
                LineOut &= "~Fixed"
            Else
                If objSQLDataReader("07") <> "NIL" Or objSQLDataReader("010") <> "NIL" Or objSQLDataReader("P1") <> "0.00" Then
                    LineOut &= "~Provided"
                Else
                    LineOut &= "~NotProvided"
                End If
            End If

            If RadioButtonPrintFiles.Checked Or RadioButtonReprints.Checked Or RadioButtonCD.Checked Then
                outWrite.WriteLine(LineOut)
            Else
                outWrite.WriteLine(LineOut)
            End If

            ' Beneficiaries
            SQLCommand2 = "SELECT count(*) as BenefCount FROM [Statement_Header_Variable] WHERE MemberNumber = '" & MemberNumber & "' AND substring(ControlCode,1,1) = '1' AND JobNumber = " & TextBoxPresJobNumber.Text & " AND Grouping = '" & TextBoxPreSGrouping.Text & "'"
            objSQLCommand2 = New SqlCommand(SQLCommand2, objSQLConn2)
            objSQLDataReader2 = objSQLCommand2.ExecuteReader()
            If objSQLDataReader2.Read() Then
                If objSQLDataReader2("BenefCount").ToString <> "" Then
                    BenefCount = objSQLDataReader2("BenefCount")
                Else
                    BenefCount = 0
                End If
            Else
                BenefCount = 0
            End If
            objSQLDataReader2.Close()
            objSQLCommand2.Dispose()

            If BenefCount = 0 Then
                LineOut = "DT1~" & MemberNumber & "~" & objSQLDataReader("Segment") & "~No nomination received"
                If RadioButtonPrintFiles.Checked Or RadioButtonReprints.Checked Or RadioButtonCD.Checked Then
                    outWrite.WriteLine(LineOut)
                Else
                    outWrite.WriteLine(LineOut)
                End If
            Else
                If BenefCount > 100 Then
                    LineOut = "DT1~" & MemberNumber & "~" & objSQLDataReader("Segment") & "~Too many"
                    If RadioButtonPrintFiles.Checked Or RadioButtonReprints.Checked Or RadioButtonCD.Checked Then
                        outWrite.WriteLine(LineOut)
                    Else
                        outWrite.WriteLine(LineOut)
                    End If
                Else
                    SQLCommand2 = "SELECT * FROM [Statement_Header_Variable] WHERE MemberNumber = '" & MemberNumber & "' AND substring(ControlCode,1,1) = '1' AND JobNumber = " & TextBoxPresJobNumber.Text & " AND Grouping = '" & TextBoxPreSGrouping.Text & "' ORDER BY ControlCode"
                    objSQLCommand2 = New SqlCommand(SQLCommand2, objSQLConn2)
                    objSQLDataReader2 = objSQLCommand2.ExecuteReader()
                    While objSQLDataReader2.Read()
                        LineOut = "DT1~" & MemberNumber & "~" & objSQLDataReader("Segment") & "~"
                        If InStr(1, objSQLDataReader2("VariableContent"), "(") > 0 Then
                            LineOut &= Mid(objSQLDataReader2("VariableContent"), 1, InStr(1, objSQLDataReader2("VariableContent"), "(") - 1) & "~"
                            LineOut &= Mid(objSQLDataReader2("VariableContent"), InStr(1, objSQLDataReader2("VariableContent"), "(") + 1)
                            If Mid(LineOut, Len(LineOut), 1) = ")" Then
                                LineOut = Mid(LineOut, 1, Len(LineOut) - 1)
                            End If
                        Else
                            LineOut &= objSQLDataReader2("VariableContent") & "~"
                        End If
                        LineOut &= "~" & objSQLDataReader("BindingDeath")
                        If RadioButtonPrintFiles.Checked Or RadioButtonReprints.Checked Or RadioButtonCD.Checked Then
                            outWrite.WriteLine(LineOut)
                        Else
                            outWrite.WriteLine(LineOut)
                        End If
                    End While
                    objSQLDataReader2.Close()
                    objSQLCommand2.Dispose()
                End If
            End If

            ' Transactions
            SQLCommand2 = "SELECT * FROM [Statement_ROC] WHERE ControlCode = '8' AND MemberNumber = '" & MemberNumber & "' AND JobNumber = " & TextBoxPresJobNumber.Text & " AND Grouping = '" & TextBoxPreSGrouping.Text & "' ORDER BY PageNumber, PageLineNumber"
            objSQLCommand2 = New SqlCommand(SQLCommand2, objSQLConn2)
            objSQLDataReader2 = objSQLCommand2.ExecuteReader()
            NoValues = True
            MemberProtectionRebate = "No"
            LastPageNumber = 0
            SubtotalAmount = 0
            While objSQLDataReader2.Read()
                SubtotalAmount += objSQLDataReader2("ROC_Value5")
                LineOut = "DT2~" & MemberNumber & "~" & objSQLDataReader("Segment") & "~" & objSQLDataReader2("ControlCode") & "~" & _
                          objSQLDataReader2("PageNumber") & "~" & objSQLDataReader2("PageLineNumber") & "~"
                LineOut = LineOut & objSQLDataReader2("ROC_Date") & "~"
                LineOut = LineOut & objSQLDataReader2("ROC_Description") & "~"
                LineOut = LineOut & objSQLDataReader2("ROC_Date_Range") & "~"
                If objSQLDataReader2("ROC_Num_Weeks") <> 0 Then
                    LineOut = LineOut & objSQLDataReader2("ROC_Num_Weeks")
                End If
                If Mid(objSQLDataReader2("ROC_Description").ToString, 1, 6) = "MERGED" Then
                    LineOut = LineOut & "~~~~~"
                Else
                    For Idx1 = 1 To 4
                        LineOut = LineOut & "~" & FormatDollar(objSQLDataReader2("ROC_Value" & Idx1))
                    Next
                    LineOut = LineOut & "~" & FormatDollar(objSQLDataReader2("ROC_Value5"))
                End If
                LineOut = LineOut & "~" & objSQLDataReader("ReduceTranFont")
                If RadioButtonPrintFiles.Checked Or RadioButtonReprints.Checked Or RadioButtonCD.Checked Then
                    If objSQLDataReader2("PageNumber") = 1 Then
                        outWrite.WriteLine(LineOut)
                        'PageSize = "A3"
                    Else
                        If HoldHD1 <> "" Then
                            outWrite.WriteLine(HoldHD1)
                            HoldHD1 = ""
                        End If
                        outWrite.WriteLine(LineOut)
                        'PageSize = "A4"
                        'A4HasRecords = True
                        If LastPageNumber <> objSQLDataReader2("PageNumber") Then
                            If RadioButtonCD.Checked Then
                                outWriteCDPageIndex.WriteLine(TextBoxPresJobNumber.Text & Chr(9) & objSQLDataReader("MemberNumber") & Chr(9) & OutputFileName & "_A4.pdf" & Chr(9) & CDA4PageNumber & Chr(9) & "1" & Chr(9) & "17" & Chr(9) & objSQLDataReader("B7") & Chr(9) & TempName)
                                CDA4PageNumber += 1
                            End If
                            RecFollower += 1
                        End If
                    End If
                    LastPageNumber = objSQLDataReader2("PageNumber")
                Else
                    outWrite.WriteLine(LineOut)
                End If
                'If objSQLDataReader2("ControlCode") = "F" And NoValues Then
                'LineOut = "DT2~" & MemberNumber & "~" & objSQLDataReader("Segment") & "~" & objSQLDataReader2("ControlCode") & "~" & _
                '          objSQLDataReader2("PageNumber") & "~" & objSQLDataReader2("PageLineNumber") & "~"
                'If RadioButtonPrintFiles.Checked Or RadioButtonReprints.Checked Or RadioButtonCD.Checked Then
                'If objSQLDataReader2("PageNumber") = 1 Then
                'outWriteA3.WriteLine(LineOut)
                'PageSize = "A3"
                'Else
                'If HoldHD1 <> "" Then
                'outWriteA4.WriteLine(HoldHD1)
                'HoldHD1 = ""
                'End If
                'outWriteA4.WriteLine(LineOut)
                'PageSize = "A4"
                'A4HasRecords = True
                'End If
                'Else
                'outWrite.WriteLine(LineOut)
                'End If
                'End If
                If objSQLDataReader2("ROC_Description").ToString = "Member protection rebate^" Then
                    MemberProtectionRebate = "Yes"
                End If
                If objSQLDataReader2("ControlCode") = "8" And objSQLDataReader2("ControlLineNumber") <> 1 Then
                    NoValues = False
                End If
            End While

            objSQLDataReader2.Close()
            objSQLCommand2.Dispose()

            'Investment Earnings Line
            SQLCommand2 = "SELECT * FROM [Statement_ROC] WHERE ROC_Description = 'Investment Earnings' AND MemberNumber = '" & MemberNumber & "' AND JobNumber = " & TextBoxPresJobNumber.Text & " AND Grouping = '" & TextBoxPreSGrouping.Text & "' ORDER BY PageNumber, PageLineNumber"
            objSQLCommand2 = New SqlCommand(SQLCommand2, objSQLConn2)
            objSQLDataReader2 = objSQLCommand2.ExecuteReader()
            If objSQLDataReader2.Read() Then
                SubtotalAmount += objSQLDataReader2("ROC_Value5")
                LineOut = "DT2~" & MemberNumber & "~" & objSQLDataReader("Segment") & "~" & objSQLDataReader2("ControlCode") & "~" & _
                          objSQLDataReader2("PageNumber") & "~" & objSQLDataReader2("PageLineNumber") & "~"
                LineOut = LineOut & objSQLDataReader2("ROC_Date") & "~"
                LineOut = LineOut & objSQLDataReader2("ROC_Description") & "~"
                LineOut = LineOut & objSQLDataReader2("ROC_Date_Range") & "~"
                For Idx1 = 1 To 4
                    LineOut = LineOut & "~" & FormatDollar(objSQLDataReader2("ROC_Value" & Idx1))
                Next
                LineOut = LineOut & "~" & FormatDollar(objSQLDataReader2("ROC_Value5"))
                LineOut = LineOut & "~" & objSQLDataReader("ReduceTranFont")
                If RadioButtonPrintFiles.Checked Or RadioButtonReprints.Checked Or RadioButtonCD.Checked Then
                    If objSQLDataReader2("PageNumber") = 1 Then
                        outWrite.WriteLine(LineOut)
                        'PageSize = "A3"
                    Else
                        If HoldHD1 <> "" Then
                            outWrite.WriteLine(HoldHD1)
                            HoldHD1 = ""
                        End If
                        outWrite.WriteLine(LineOut)
                        'PageSize = "A4"
                        'A4HasRecords = True
                        If LastPageNumber <> objSQLDataReader2("PageNumber") Then
                            If RadioButtonCD.Checked Then
                                outWriteCDPageIndex.WriteLine(TextBoxPresJobNumber.Text & Chr(9) & objSQLDataReader("MemberNumber") & Chr(9) & OutputFileName & "_A4.pdf" & Chr(9) & CDA4PageNumber & Chr(9) & "1" & Chr(9) & "17" & Chr(9) & objSQLDataReader("B7") & Chr(9) & TempName)
                                CDA4PageNumber += 1
                            End If
                            RecFollower += 1
                        End If
                    End If
                    LastPageNumber = objSQLDataReader2("PageNumber")
                Else
                    outWrite.WriteLine(LineOut)
                End If
            End If

            objSQLDataReader2.Close()
            objSQLCommand2.Dispose()

            'Other amounts added
            SQLCommand2 = "SELECT * FROM [Statement_ROC] WHERE ROC_Description <> 'Investment Earnings' AND ControlCode = '9' AND ROC_Value5 > 0 AND MemberNumber = '" & MemberNumber & "' AND JobNumber = " & TextBoxPresJobNumber.Text & " AND Grouping = '" & TextBoxPreSGrouping.Text & "' ORDER BY PageNumber, PageLineNumber"
            objSQLCommand2 = New SqlCommand(SQLCommand2, objSQLConn2)
            objSQLDataReader2 = objSQLCommand2.ExecuteReader()
            While objSQLDataReader2.Read()
                SubtotalAmount += objSQLDataReader2("ROC_Value5")
                LineOut = "DT2~" & MemberNumber & "~" & objSQLDataReader("Segment") & "~" & objSQLDataReader2("ControlCode") & "+~" & _
                          objSQLDataReader2("PageNumber") & "~" & objSQLDataReader2("PageLineNumber") & "~"
                LineOut = LineOut & objSQLDataReader2("ROC_Date") & "~"
                LineOut = LineOut & objSQLDataReader2("ROC_Description") & "~"
                LineOut = LineOut & objSQLDataReader2("ROC_Date_Range") & "~"
                For Idx1 = 1 To 4
                    LineOut = LineOut & "~" & FormatDollar(objSQLDataReader2("ROC_Value" & Idx1))
                Next
                LineOut = LineOut & "~" & FormatDollar(objSQLDataReader2("ROC_Value5"))
                LineOut = LineOut & "~" & objSQLDataReader("ReduceTranFont")
                If RadioButtonPrintFiles.Checked Or RadioButtonReprints.Checked Or RadioButtonCD.Checked Then
                    If objSQLDataReader2("PageNumber") = 1 Then
                        outWrite.WriteLine(LineOut)
                        'PageSize = "A3"
                    Else
                        If HoldHD1 <> "" Then
                            outWrite.WriteLine(HoldHD1)
                            HoldHD1 = ""
                        End If
                        outWrite.WriteLine(LineOut)
                        'PageSize = "A4"
                        'A4HasRecords = True
                        If LastPageNumber <> objSQLDataReader2("PageNumber") Then
                            If RadioButtonCD.Checked Then
                                outWriteCDPageIndex.WriteLine(TextBoxPresJobNumber.Text & Chr(9) & objSQLDataReader("MemberNumber") & Chr(9) & OutputFileName & "_A4.pdf" & Chr(9) & CDA4PageNumber & Chr(9) & "1" & Chr(9) & "17" & Chr(9) & objSQLDataReader("B7") & Chr(9) & TempName)
                                CDA4PageNumber += 1
                            End If
                            RecFollower += 1
                        End If
                    End If
                    LastPageNumber = objSQLDataReader2("PageNumber")
                Else
                    outWrite.WriteLine(LineOut)
                End If
            End While

            objSQLDataReader2.Close()
            objSQLCommand2.Dispose()


            'Subtotal Line
            LineOut = "DT2~" & MemberNumber & "~" & objSQLDataReader("Segment") & "~S~1~1~~Subtotal~~~~~~"
            LineOut = LineOut & "~" & FormatDollar(SubtotalAmount)
            LineOut = LineOut & "~" & objSQLDataReader("ReduceTranFont")
            'If RadioButtonPrintFiles.Checked Or RadioButtonReprints.Checked Or RadioButtonCD.Checked Then
            'If objSQLDataReader2("PageNumber") = 1 Then
            'outWriteA3.WriteLine(LineOut)
            'PageSize = "A3"
            'Else
            'If HoldHD1 <> "" Then
            'outWriteA4.WriteLine(HoldHD1)
            'HoldHD1 = ""
            'End If
            'outWriteA4.WriteLine(LineOut)
            'PageSize = "A4"
            'A4HasRecords = True
            'If LastPageNumber <> objSQLDataReader2("PageNumber") Then
            'If RadioButtonCD.Checked Then
            'outWriteCDPageIndex.WriteLine(TextBoxPresJobNumber.Text & Chr(9) & objSQLDataReader("MemberNumber") & Chr(9) & OutputFileName & "_A4.pdf" & Chr(9) & CDA4PageNumber & Chr(9) & "1" & Chr(9) & "17" & Chr(9) & objSQLDataReader("B7") & Chr(9) & TempName)
            'CDA4PageNumber += 1
            'End If
            'RecFollower += 1
            'End If
            'End If
            'LastPageNumber = objSQLDataReader2("PageNumber")
            'Else
            'outWrite.WriteLine(LineOut)
            'End If


            'Other amounts deducted
            SQLCommand2 = "SELECT * FROM [Statement_ROC] WHERE ROC_Description <> 'Investment Earnings' AND ControlCode = '9' AND ROC_Value5 < 0 AND MemberNumber = '" & MemberNumber & "' AND JobNumber = " & TextBoxPresJobNumber.Text & " AND Grouping = '" & TextBoxPreSGrouping.Text & "' ORDER BY PageNumber, PageLineNumber"
            objSQLCommand2 = New SqlCommand(SQLCommand2, objSQLConn2)
            objSQLDataReader2 = objSQLCommand2.ExecuteReader()
            While objSQLDataReader2.Read()
                LineOut = "DT2~" & MemberNumber & "~" & objSQLDataReader("Segment") & "~" & objSQLDataReader2("ControlCode") & "-~" & _
                          objSQLDataReader2("PageNumber") & "~" & objSQLDataReader2("PageLineNumber") & "~"
                LineOut = LineOut & objSQLDataReader2("ROC_Date") & "~"
                LineOut = LineOut & objSQLDataReader2("ROC_Description") & "~"
                LineOut = LineOut & objSQLDataReader2("ROC_Date_Range") & "~"
                For Idx1 = 1 To 4
                    LineOut = LineOut & "~" & FormatDollar(objSQLDataReader2("ROC_Value" & Idx1))
                Next
                LineOut = LineOut & "~" & FormatDollar(objSQLDataReader2("ROC_Value5"))
                LineOut = LineOut & "~" & objSQLDataReader("ReduceTranFont")
                If RadioButtonPrintFiles.Checked Or RadioButtonReprints.Checked Or RadioButtonCD.Checked Then
                    If objSQLDataReader2("PageNumber") = 1 Then
                        outWrite.WriteLine(LineOut)
                        'PageSize = "A3"
                    Else
                        If HoldHD1 <> "" Then
                            outWrite.WriteLine(HoldHD1)
                            HoldHD1 = ""
                        End If
                        outWrite.WriteLine(LineOut)
                        'PageSize = "A4"
                        'A4HasRecords = True
                        If LastPageNumber <> objSQLDataReader2("PageNumber") Then
                            If RadioButtonCD.Checked Then
                                outWriteCDPageIndex.WriteLine(TextBoxPresJobNumber.Text & Chr(9) & objSQLDataReader("MemberNumber") & Chr(9) & OutputFileName & "_A4.pdf" & Chr(9) & CDA4PageNumber & Chr(9) & "1" & Chr(9) & "17" & Chr(9) & objSQLDataReader("B7") & Chr(9) & TempName)
                                CDA4PageNumber += 1
                            End If
                            RecFollower += 1
                        End If
                    End If
                    LastPageNumber = objSQLDataReader2("PageNumber")
                Else
                    outWrite.WriteLine(LineOut)
                End If
            End While

            objSQLDataReader2.Close()
            objSQLCommand2.Dispose()

            'Closing Balance
            SQLCommand2 = "SELECT * FROM [Statement_ROC] WHERE ROC_Description = 'CLOSING BALANCE*' AND MemberNumber = '" & MemberNumber & "' AND JobNumber = " & TextBoxPresJobNumber.Text & " AND Grouping = '" & TextBoxPreSGrouping.Text & "' ORDER BY PageNumber, PageLineNumber"
            objSQLCommand2 = New SqlCommand(SQLCommand2, objSQLConn2)
            objSQLDataReader2 = objSQLCommand2.ExecuteReader()
            If objSQLDataReader2.Read() Then
                LineOut = "DT2~" & MemberNumber & "~" & objSQLDataReader("Segment") & "~" & objSQLDataReader2("ControlCode") & "-~" & _
                          objSQLDataReader2("PageNumber") & "~" & objSQLDataReader2("PageLineNumber") & "~"
                LineOut = LineOut & objSQLDataReader2("ROC_Date") & "~"
                LineOut = LineOut & objSQLDataReader2("ROC_Description") & "~"
                LineOut = LineOut & objSQLDataReader2("ROC_Date_Range") & "~"
                For Idx1 = 1 To 4
                    LineOut = LineOut & "~" & FormatDollar(objSQLDataReader2("ROC_Value" & Idx1))
                Next
                LineOut = LineOut & "~" & FormatDollar(objSQLDataReader2("ROC_Value5"))
                LineOut = LineOut & "~" & objSQLDataReader("ReduceTranFont")
                If RadioButtonPrintFiles.Checked Or RadioButtonReprints.Checked Or RadioButtonCD.Checked Then
                    If objSQLDataReader2("PageNumber") = 1 Then
                        outWrite.WriteLine(LineOut)
                        'PageSize = "A3"
                    Else
                        If HoldHD1 <> "" Then
                            outWrite.WriteLine(HoldHD1)
                            HoldHD1 = ""
                        End If
                        outWrite.WriteLine(LineOut)
                        'PageSize = "A4"
                        'A4HasRecords = True
                        If LastPageNumber <> objSQLDataReader2("PageNumber") Then
                            If RadioButtonCD.Checked Then
                                outWriteCDPageIndex.WriteLine(TextBoxPresJobNumber.Text & Chr(9) & objSQLDataReader("MemberNumber") & Chr(9) & OutputFileName & "_A4.pdf" & Chr(9) & CDA4PageNumber & Chr(9) & "1" & Chr(9) & "17" & Chr(9) & objSQLDataReader("B7") & Chr(9) & TempName)
                                CDA4PageNumber += 1
                            End If
                            RecFollower += 1
                        End If
                    End If
                    LastPageNumber = objSQLDataReader2("PageNumber")
                Else
                    outWrite.WriteLine(LineOut)
                End If
            End If

            objSQLDataReader2.Close()
            objSQLCommand2.Dispose()

            LineOut = "FT2~" & MemberNumber & "~" & objSQLDataReader("Segment") & "~" & FormatNumber(objSQLDataReader("G11"), 2) & "~" & MemberProtectionRebate & "~" & FormatNumber(objSQLDataReader("K1"), 2) & "~" & FormatNumber(objSQLDataReader("R1"), 2)
            If RadioButtonPrintFiles.Checked Or RadioButtonReprints.Checked Or RadioButtonCD.Checked Then
                'If PageSize = "A3" Then
                outWrite.WriteLine(LineOut)
                'Else
                'outWriteA4.WriteLine(LineOut)
                'A4HasRecords = True
                'End If
            Else
                outWrite.WriteLine(LineOut)
            End If

            If objSQLDataReader("Segment") = 1 Or objSQLDataReader("Segment") = 2 Then

                SQLCommand2 = "SELECT * FROM [Statement_Address] WHERE MemberNumber = '" & MemberNumber & "' AND JobNumber = " & TextBoxPresJobNumber.Text & " AND Grouping = '" & TextBoxPreSGrouping.Text & "'"
                objSQLCommand2 = New SqlCommand(SQLCommand2, objSQLConn2)
                objSQLDataReader2 = objSQLCommand2.ExecuteReader()
                objSQLDataReader2.Read()

                LineOut = "FT3~" & MemberNumber & "~" & objSQLDataReader("Segment") & "~" & objSQLDataReader("B4") & " " & objSQLDataReader("B5")
                If objSQLDataReader("B6").ToString <> "" Then
                    LineOut &= " " & objSQLDataReader("B6")
                End If
                LineOut &= " " & objSQLDataReader("B7")
                LineOut &= "~" & objSQLDataReader2("32") & "~" & objSQLDataReader2("33") & "~" & objSQLDataReader2("34") & "~" & objSQLDataReader2("35") & "~" & objSQLDataReader2("36")

                objSQLDataReader2.Close()
                objSQLCommand2.Dispose()

                If RadioButtonPrintFiles.Checked Or RadioButtonReprints.Checked Or RadioButtonCD.Checked Then
                    'If PageSize = "A3" Then
                    outWrite.WriteLine(LineOut)
                    'Else
                    'outWriteA4.WriteLine(LineOut)
                    'A4HasRecords = True
                    'End If
                Else
                    outWrite.WriteLine(LineOut)
                End If
            End If

skipsum:

            LineOut = "END~" & MemberNumber & "~" & objSQLDataReader("Segment")
            If RadioButtonPrintFiles.Checked Or RadioButtonReprints.Checked Or RadioButtonCD.Checked Then
                outWrite.WriteLine(LineOut)
                'If A4HasRecords Then outWriteA4.WriteLine(LineOut)
            Else
                outWrite.WriteLine(LineOut)
            End If

        End While

Open in new window

0
Comment
Question by:berniefitz
  • 3
4 Comments
 
LVL 5

Accepted Solution

by:
mvdeveloper earned 2000 total points
ID: 38296363
Where to start ..

How many INSERT statements are you actually issuing through this? That's very slow performance, make it a parameterized procedure and don't destroy it each time round.

Are you sure you can't combine some of these selects into a single select returning multiple rows for the different charges? This is all just calling out for a stored procedure.

All those ROC_Descriptions OR'ed - use an IN clause.

Basically you're hitting the database far more than you need and in the wrong ways.
0
 
LVL 1

Author Comment

by:berniefitz
ID: 38296447
Hi,

Thanks for the response.

Only one INSERT statement per thousand or so records. So not often on the INSERT statements.

Not sure about combining the select statements. I'm looking into this now.

I've fixed the ROC_Descriptions to use an IN clause.

If you see any other areas of concern please let me know.

Thanks, Bernie.
0
 
LVL 1

Author Comment

by:berniefitz
ID: 38298291
Hi again,

After reducing the number of queries sent to the DB I had a more detailed look into the database itself. Basically the issue came back to poorly set indexes. I should have not trusted someone's word that the database was built well.

Anyway, thank you for your help.

Thanks, Bernie.
0
 
LVL 1

Author Closing Comment

by:berniefitz
ID: 38298299
This wasn't the solution to the problem however the responder correctly identified the areas of concern based on the information given.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question