asked on

Possible Performance Improvements

I am currently using the code below. The performance is very slow and I am looking for suggestions on how to improve it. This application is currently being used to process between 100 meg and 2 gig of data.

Thank you very much for your time and expertise,
Gr8life

Imports System.Data.Common
Imports System.Data.Oledb
Imports System.IO
Imports System.Text

Public Class Form1

Private ds As New DataSet

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Try
Dim inFile As String
Dim outFile As String
Dim openFileDialog1 As New OpenFileDialog
openFileDialog1.InitialDirectory = "c:\resource"
openFileDialog1.Filter = "txt files (*.txt|*.txt|All files(*.*)|*.*"
openFileDialog1.FilterIndex = 2
openFileDialog1.RestoreDirectory = True

If openFileDialog1.ShowDialog() = DialogResult.OK Then
inFile = openFileDialog1.FileName
outFile = Mid(inFile, 1, inFile.LastIndexOf("."))
outFile += "-out.CSV"
End If

If (inFile.Length > 0) Then
If (outFile.Length > 0) Then
CountryDatatable() 'Here is where I am calling function
ConvertFiles(inFile, outFile)
'Wave.Play("C:\resource\sounds\completed.wav")
MsgBox("File Complete!")
Else
MsgBox("No output file specified!")
End If
Else
MsgBox("No input file specified!")
End If
Catch ex As Exception
MsgBox("Error in Button1_Click :" & ex.message)
End Try
End Sub

Public Sub ConvertFiles(ByVal filein As String, ByVal fileout As String)
Dim sb As New StringBuilder 'I will be trying other values later (100) was removed after stringbuilder
Try
If (IO.File.Exists(filein)) Then
Dim sin As New IO.StreamReader(filein)
Dim items() As String
While True
Dim readline As String = sin.ReadLine
If (IsNothing(readline)) Then Exit While

items = readline.Split(vbTab)
items(3) = items(3) & "," & IPLookup(items(3))
items(5) = items(5) & "," & IPLookup(items(5))
sb.Append(items(3).ToString & "," & items(5).ToString & vbCrLf)

End While
sin.Close()

Dim sout As New IO.StreamWriter(fileout, False)
sout.WriteLine(String.Join(",", items)) 'Name 'items' not declared
sout.Flush()
sout.Close()
End If
Catch ex As Exception
MsgBox("Problem Occurred" & ex.Message)
End Try
End Sub

Public Function Dot2LongIP(ByVal DottedIP As String) As Long
Dim arrDec() As String
Dim i As Integer
Dim intResult As Long
If DottedIP = "" Then
Return 0
Else
arrDec = DottedIP.Split(".")
For i = arrDec.Length - 1 To 0 Step -1
intResult = intResult + ((Int(arrDec(i)) Mod 256) * Math.Pow(256, 3 - i))
Next
Return intResult
End If
End Function

Public Sub CountryDatatable()
Dim cmdSelect As New OleDbCommand
Dim dtm As DataTableMapping
Dim da As New OleDbDataAdapter
Dim dt As New DataTable
Dim con As New OleDbConnection

With da
.SelectCommand = New OleDbCommand
With .SelectCommand
.Connection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source = C:\resource\master.mdb")
.CommandText = "SELECT * from Country"
End With

.TableMappings.Add("Table", "Country")
.AcceptChangesDuringFill = True
End With

Try
da.Fill(ds)
Catch ex As Exception
MessageBox.Show("Unable to load Country data: " + ex.Message)
End Try
End Sub

Public Function IPLookup(ByVal DottedIP As String) As String
Dim lngIP As Long
Dim strCountry As String
Dim foundrows() As DataRow
Dim sql As String

lngIP = Dot2LongIP(DottedIP)

sql = lngIP.ToString & ">=ipl1 and " & lngIP.ToString & " <= ipl2"
foundrows = ds.Tables("Country").Select(sql)

If foundrows.Length = 0 Then
strCountry = "unknown"
Else
strCountry = foundrows(0).Item("countryname")
End If
Return strCountry
End Function
End Class