# Performance improvement on vb.net code

I need to write 5 crores records with 72 columns into text file, the file size is growing as 9.7gb .

I need to check each and every columm length need to format as according to the length as defined in XML file.

Reading records from oracle one by one and checking the format and writing into text file.

To write 5 crores records it is taking more than 24 hours. How can I increase the performance in the below code?

Dim valString As String = Nothing
Dim valName As String = Nothing
Dim valLength As String = Nothing
Dim valDataType As String = Nothing
Dim validationsArray As ArrayList = GetValidations(Directory.GetCurrentDirectory() + "\ReportFormat.xml")
Console.WriteLine("passed xml")
Dim k As Integer = 1

Try
Console.WriteLine(System.DateTime.Now())
Dim selectSql As String = "select * from table where
" record_date >= To_Date('01-01-2014','DD-MM-YYYY') and record_date <= To_Date('31-12-2014','DD-MM-YYYY')"
Dim dataTable As New DataTable
Dim oracleAccess As New OracleConnection(System.Configuration.ConfigurationManager.AppSettings("OracleConnection"))
Dim cmd As New OracleCommand()
cmd.Connection = oracleAccess
cmd.CommandType = CommandType.Text
cmd.CommandText = selectSql
oracleAccess.Open()
Dim Tablecolumns As New DataTable()
Using oracleAccess
Using writer = New StreamWriter(Directory.GetCurrentDirectory() + "\FileName.txt")
For i As Integer = 0 To odr.FieldCount - 1
Next
Dim sbColumnData As New StringBuilder
Dim values(odr.FieldCount - 1) As Object
Dim fieldCount As Integer = odr.GetValues(values)
For i As Integer = 0 To fieldCount - 1
Dim vals As Array = validationsArray(i).ToString.ToUpper.Split("|")
valName = vals(0).trim
valDataType = vals(1).trim
valLength = vals(2).trim

Select Case valDataType
Case "VARCHAR2"
If values(i).ToString().Length = valLength Then
sbColumnData.Append(values(i).ToString())
'sbColumnData.Append("|")
ElseIf values(i).ToString().Length > valLength Then
sbColumnData.Append(values(i).ToString().Substring(0, valLength))
'sbColumnData.Append("|")
Else
'sbColumnData.Append("|")
End If
Case "NUMERIC"
valLength = valLength.Substring(0, valLength.IndexOf(","))
If values(i).ToString().Length = valLength Then
sbColumnData.Append(values(i).ToString())
'sbColumnData.Append("|")
Else
'sbColumnData.Append("|")
End If
'sbColumnData.Append((values(i).ToString()))
End Select
Next
writer.WriteLine(sbColumnData)

k = k + 1
Console.WriteLine(k)
End While
End Using
writer.WriteLine(System.DateTime.Now())
End Using
End Using

Console.WriteLine(System.DateTime.Now())
Return Tablecolumns
Catch ex As Exception
Console.WriteLine(System.DateTime.Now())
Console.WriteLine("Error: " & ex.Message)
Return Nothing
End Try

• Writing to the console is very slow. If odr contains a lot of rows, you might see improvement by removing the console.writeline – the_lotus Sep 21 '15 at 15:34

So, what is happening here

Select Case valDataType
Case "VARCHAR2"
If values(i).ToString().Length = valLength Then
sbColumnData.Append(values(i).ToString())
'sbColumnData.Append("|")
ElseIf values(i).ToString().Length > valLength Then
sbColumnData.Append(values(i).ToString().Substring(0, valLength))
'sbColumnData.Append("|")
Else
'sbColumnData.Append("|")
End If
Case "NUMERIC"
valLength = valLength.Substring(0, valLength.IndexOf(","))
If values(i).ToString().Length = valLength Then
sbColumnData.Append(values(i).ToString())
'sbColumnData.Append("|")
Else
'sbColumnData.Append("|")
End If
'sbColumnData.Append((values(i).ToString()))
End Select


if values(i).ToString().Length is < valLength ? You are calling 3 times .ToString() on the object. A much faster and better way would be to just do it once like so

Dim currentValue As String = values(i).ToString()
Select Case valDataType
Case "VARCHAR2"
If vcurrentValue.Length = valLength Then
sbColumnData.Append(currentValue)
ElseIf currentValue.Length > valLength Then
sbColumnData.Append(currentValue.Substring(0, valLength))
Else
End If

Case "NUMERIC"
valLength = valLength.Substring(0, valLength.IndexOf(","))
If currentValue.Length = valLength Then
sbColumnData.Append(currentValue)
Else
End If

End Select


I don't like this

Dim vals As Array = validationsArray(i).ToString.ToUpper.Split("|")
valName = vals(0).trim
valDataType = vals(1).trim
valLength = vals(2).trim


for a couple of reasons. First you are creating a string array out of this ArrayList (however this is created) and then store it in an Array so casting it to an object to cast it again to a String on the next lines.

But then you use the valLength String's to compare with Length which is an Integer.

The valName variable is never used, so you can just remove it along with the commented code. Code which isn't used or is commented is just dead code which should be removed to increase readability.

Another thing bothering me is the use of abbreviations for variables names. You won't gain any performancy increase by doing so, but you loose a lot of readability.

Dim validationValues As String() = validationsArray(i).ToString.ToUpper.Split("|")
Dim valueDataType As String = validationValues(1).Trim()
Dim valueLength As String = validationValues(1).Trim()
Dim currentValue As String = values(i).ToString()

Select Case valDataType
Case "VARCHAR2"

Dim length As Integer = Convert.ToInt32(valueLength)

If vcurrentValue.Length = length Then
sbColumnData.Append(currentValue)
ElseIf currentValue.Length > length Then
sbColumnData.Append(currentValue.Substring(0, length))
Else
End If

Case "NUMERIC"

Dim length As Integer = Convert.ToInt32(valueLength.Substring(0, valueLength.IndexOf(",")))

If currentValue.Length = length Then
sbColumnData.Append(currentValue)
Else
End If

End Select


Another thing which is slowing down the performance is that you do this

Dim vals As Array = validationsArray(i).ToString.ToUpper.Split("|")
valName = vals(0).trim
valDataType = vals(1).trim
valLength = vals(2).trim


for each datarow and each column. You should store these values once an reuse the values for each other datarow.

k = k + 1
Console.WriteLine(k)


This doesn't serve any real purpose but will slow down your code. Get rid of it.

You are using using statements to enclose objects which implements IDisposable which is good, but why don't you be consistent ? The OracleCommand also implements IDisposable but there is no using.

Dim Tablecolumns As New DataTable()


this isn't used anywhere except for the return value of the method. You can simply change it to a Sub and remove this variable.

Doing a select * from table where... won't help any in the meaning of increasing performance. You only need columns which are either VARCHAR2 or NUMERIC so you should only add these columns to your select query which meets this requirement. There is for instance no use for any Date field like record_date. You don't need to retrieve this column to restrict the query to the value of it.

• Heslacher the code is running very fast and it came to very less time i.e, 2days to 2 hrs . Thank you very much for quick help – user96888 Sep 15 '15 at 13:00