How can I speed this up?

[RibTime]

Very new to VB and would like some advice on how to improve performance of this utility.

Basically it reads a text file, which has 120k lines, and then separates out fields within the text line into an Access database for processing later.

I have tried two approaches, one using a StreamReader and the other using ADO.net to create a data set. The format of the file doesn't lead itself to ADO.Net but it still loads.

In both cases it takes about 20minutes to create the database file.

Here is an extract from one section of the code....

Dim strVTVreport As String = "Z:\xxx\xxx.txt"
        Dim sr As IO.StreamReader = New IO.StreamReader(strVTVreport)
        Dim Var1-Var10As String
 
        Dim dbConnection As OleDbConnection
        Dim dbCommand As OleDbCommand = New OleDbCommand
 
        dbConnection = New OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0; Data Source=Z:\xxx\DataBases\xxx.mdb")
        dbCommand.Connection = dbConnection
        dbConnection.Open()
 
        dbCommand.CommandText = "Delete * From [xxx Table]"
        dbCommand.ExecuteNonQuery()
 
        strLineIn = sr.ReadLine
 
        Debug.WriteLine(DateTime.Now)
 
        While Not strLineIn = Nothing
 
            ' Debug.WriteLine(strLineIn.Length & "    " & strLineIn)
 
            If strLineIn.Length = 118 Then
 
                If strLineIn.Substring(51, 1).ToString = ":" Then
 
                    strLineLen = strLineIn.Length
                    strVar1 = strLineIn.Substring(1, 6).ToString
                    .
                    .
                    .
                    .
                    strVar10= strLineIn.Substring(113, (strLineIn.Length - 113)).ToString
 
                    '
                    '   Build a SQL statement
                    '
 
                    Dim strSQL As String = "INSERT INTO [xxx]( var1....var10)" & _
                                        " VALUES(@Var1....@var10)
 
                    '
                    '   Now that we have defined the SQL statement and some positional parms assign some values to them
                    '
 
                    dbCommand.Parameters.Add("@Var1", OleDb.OleDbType.VarChar, 50).Value = strVar1
                    .... more lines
                    
                    dbCommand.Parameters.Add("@Var10, OleDb.OleDbType.VarChar, 50).Value = strVar10
 
                    '
                    ' Define and SQL statement and execute it. Once were done clear all the Parameter.Add values for the next iterations
                    '
                    Try
 
                        dbCommand.CommandText = strSQL
                        dbCommand.ExecuteNonQuery()
                        dbCommand.Parameters.Clear()

Thanks in advance

RibTime

[HWijngaarD]

i should try to add datarows in stead of the insert command.
Try to fill a dataset Like in this example 'DS' in combination with a .
Someting like:

dbConnection = New OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0; Data Source=Z:\xxx\DataBases\xxx.mdb")
        dbDataAdapter = New OleDBDataAdapter("SELECT * FROM [xxx Table], dbConnection)
        ds = New DataSet
        dbCommandBuilder = New OleDBCommandBuilder(dbDataAdapter)
        dbDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
        dbDataAdapter.Fill(ds, [xxx Table])
ds.clear
 
(in the Loop: )
 
Dim Drow as Datarow
Drow = Ds.Tables(0).NewRow()
Drow!anything = blablabla
Drow!AlsoAnyThing = blabla
Ds.Tables(0).Rows.Add(Drow)
 
(After the loop: )
 
dbDataAdapter.Update(Ds, [xxx Table])

This will speed thinks up, I think.

[gigemboy]

Remember that when debugging, any kind of output will affect the speed of processing. The Debug.Writeline is slowing the process down just because it is having to output that to the debug window each iteration. If you run the EXE outside visual studio.net, it will be faster because it shouldnt output the debug messages...