writing a text file of 1 million (10 lakhs) records

Question

1.00/5 (1 vote)

See more:

Dear All

I am working on a banking domain and writing a text file of 1 million (10 lakhs) records
and it will take near by 14 hours to write ,
i have use five foreach loop with in it ,data write one by one basis by using
stringbuilder .

can any one tell fast way to write a text file

thanks.

VB

For Each dRow As DataRow In dsetSegmentSelect.Tables("ConsumerBranches").Rows
                intRecCnt = 0 'Uncomment For Previous
                strBrCode = dRow.Item("BranchCode")

                If dsetSegmentSelect.Tables.Contains("ConsumerBorrowerSegment") Then
                    For Each dCBSBrRow As DataRow In dsetSegmentSelect.Tables("ConsumerBorrowerSegment").Select("BranchCode='" &amp; strBrCode &amp; "'")
                        strBrCode = dCBSBrRow.Item("BranchCode")
                        intCustomerID = dCBSBrRow.Item("CustomerID")
                        intcustData = 0
                        intRecCnt = intRecCnt + 1
                        If dsetSegmentSelect.Tables.Contains("ConsumerAccountSegment") Then
                            For Each dCBABrRow As DataRow In dsetSegmentSelect.Tables("ConsumerAccountSegment").Select("BranchCode='" &amp; strBrCode &amp; "' AND CustomerID='" &amp; intCustomerID &amp; "'")
                                intAcID = dCBABrRow.Item("Acid")
                                intcustData = intcustData + 1
                                blnJoint = False
                                For Each dBPNBrRow As DataRow In dsetSegmentSelect.Tables("BPNBranches").Select("BranchCode='" &amp; strBrCode &amp; "' AND CustomerID='" &amp; intCustomerID &amp; "'")

                                    dsetSegmentSelect.Tables("BPNBranches").DefaultView.RowFilter = "BranchCode='" &amp; strBrCode &amp; "' AND CustomerID='" &amp; intCustomerID &amp; "'"
                                    intCIBILPGID = dBPNBrRow.Item("CIBILPGID")

                                    If dsetSegmentSelect.Tables("BPNBranches").DefaultView.ToTable().Rows.Count &gt; 1 Then
                                        blnJoint = True
                                    Else
                                        blnJoint = False
                                    End If
                                    If dsetSegmentSelect.Tables.Contains("BorrowerNameSegment") Then
                                        For Each dBNSRow As DataRow In dsetSegmentSelect.Tables("BorrowerNameSegment").Select("BranchCode='" &amp; strBrCode &amp; "' AND CustomerID='" &amp; intCustomerID &amp; "' AND CIBILPGID='" &amp; intCIBILPGID &amp; "'")
                                            If blnDSFromNEXP = True Then
                                                'cnnVision.Execute(" Update CustomerID Set CustomerMark ='Y' Where Branchcode='" &amp; rstCustomer!BranchCode &amp; "' and CustomerId = " &amp; rstCustomer!CustomerID)

                                            End If
                                            Call ConsumerTableToString(dBNSRow, FileFullPathName)
                                        Next
                                    End If

                                    If dsetSegmentSelect.Tables.Contains("BorrowerIDSegment") Then
                                        For Each dBASRow As DataRow In dsetSegmentSelect.Tables("BorrowerIDSegment").Select("BranchCode='" &amp; strBrCode &amp; "' AND CustomerID='" &amp; intCustomerID &amp; "' AND CIBILPGID='" &amp; intCIBILPGID &amp; "'", "IDSegmentTag Asc")
                                            Call ConsumerTableToString(dBASRow, FileFullPathName)
                                        Next
                                    End If
                                    If
NEXT

VB

Private Sub ConsumerTableToString(ByRef DR As DataRow, ByVal FileFullPathName As String, Optional ByVal NoOfRows As Integer = 1)
       Dim strTables As New StringBuilder
       Dim intlen As Integer
       Dim I As Integer = 0
       If DR.Table.TableName.ToString = "HeaderSegment" Then
           intlen = 0
       Else
           intlen = 4
       End If
       Try

           For I = 4 To DR.ItemArray.Length - 1

               If Len(DR.Item(I).ToString) &gt; intlen Then
                   If DR.Table.TableName.ToString = "AccountSegment" Then
                       If blnJoint = True Then
                           If DR.Table.Columns(I).ColumnName.ToString = "OwnerShip" Then
                               strField = "05014"
                           Else
                               strField = DR.Item(I).ToString
                           End If
                       End If
                       If blnGuarantor = True Then
                           If DR.Table.Columns(I).ColumnName.ToString = "OwnerShip" Then
                               strField = "05013"
                           Else
                               strField = DR.Item(I).ToString
                           End If
                       End If
                       If blnJoint = False And blnGuarantor = False Then strField = DR.Item(I).ToString
                   Else
                       strField = DR.Item(I).ToString
                   End If
               Else
                   strField = ""
               End If
               strRowConsumer.Append(strField)

           Next
       Catch ex As Exception
           MessageBox.Show(ex.Message, objCommonPar.strpackagename)
       End Try
   End Sub

Posted 14-Oct-14 2:24am

Naim_86

Updated 14-Oct-14 5:08am

BillWoodruff

v8

Add a Solution

Comments

ZurdoDev 14-Oct-14 8:26am

You need to post the relevant code so we can see what might be causing it to be slow.

Naim_86 14-Oct-14 8:43am

deleted

Naim_86 14-Oct-14 8:44am

deleted

Naim_86 14-Oct-14 8:44am

deleted

Naim_86 14-Oct-14 8:46am

deleted

ZurdoDev 14-Oct-14 8:51am

Please delete these comments. They are very hard to read. Instead, click on Improve question and add the relevant code and format it.

Naim_86 14-Oct-14 8:58am

pls find the code

Sinisa Hajnal 14-Oct-14 9:21am

Please copy these comments into your question : use Improve question link

No one will read through the code in comments, its unreadable.

Your slowness comes from too deeply nested for each, rather then writing into the file. Improve the question and I'll check the code.

ZurdoDev 14-Oct-14 10:18am

No. Click on improve question and add it to the question so it is readable.

Mehdi Gholam 14-Oct-14 8:54am

1 million records to text file should at most take a minute and that is stretching it!

Naim_86 14-Oct-14 10:19am

deleted

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Stephen Hewison · Answer 1 · 2014-10-14T04:56:00

The way you're applying your loops is what's going to take the time. Visual Studio Premium comes with Profiling tools and you can also download demo profiling tools from Red Gate. This will show you where your code is spending all of its time.

Ideally you want to write a single select statement or stored procedure which outputs, as a flat list, the data which needs to be written to the file. Make the database do the hard work.

Entity relational databases maintain indexes which means it will implement your logic for which rows to write out much faster than any .Net code will.

Then have a single loop to output each of the rows to the file.

You also create a new string builder for each item which in inefficient. When you're writing to a file the most efficient thing to do is to write to the file. Use the Stream Writer and forget about builders.

You want to pass the stream writer around which outputs to the text file so all output is written directly to the file. Otherwise you're going to spend a lot of time just moving strings around in memory.

writing a text file of 1 million (10 lakhs) records

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us