Faster way to make txt file ?

Hi!

I am imorting a large txt file, using LOAD DATA INFILE to import the data to MYSQL
QUERY use about 1 sek to complete.
But making the txt file using about 60 sek.

Is there some other way to make the txt file, faster ?

Include the source i am using:


Dim datoen As String = Date.Now

        Dim ornr(500) As Int32
        Dim nesteordrenr As Int32
        Dim ordrelinjen As String
        Kill("C:\\singel\\test24.txt")
        Dim TextFile As New StreamWriter("C:\\singel\\test24.txt")
        MsgBox("START" & datoen, MsgBoxStyle.Information)

        For i = 1 To 500
            nesteordrenr = hentnestenummeriserie(2)
            ornr(i) = nesteordrenr
            ordrelinjen = ornr(i)
            ordrelinjen = ordrelinjen & "," & "1"
            ordrelinjen = ordrelinjen & "," & "TEB"
            ordrelinjen = ordrelinjen & "," & "28.12.2010"
            ordrelinjen = ordrelinjen & "," & ""
            ordrelinjen = ordrelinjen & "," & ""
            ordrelinjen = ordrelinjen & "," & ""
            ordrelinjen = ordrelinjen & "," & ""
            ordrelinjen = ordrelinjen & "," & ""
            ordrelinjen = ordrelinjen & "," & ""
            ordrelinjen = ordrelinjen & "," & ""
            ordrelinjen = ordrelinjen & "," & ""
            ordrelinjen = ordrelinjen & "," & "0"
            ordrelinjen = ordrelinjen & "," & "2"
            ordrelinjen = ordrelinjen & "," & ""
            ordrelinjen = ordrelinjen & "," & ""
            ordrelinjen = ordrelinjen & "," & "0"
            ordrelinjen = ordrelinjen & "," & "0"
            ordrelinjen = ordrelinjen & "," & "0"
            TextFile.WriteLine(ordrelinjen)
            ordrelinjen = ""
            oppdaternummerserie(2)
        Next
        datoen = Date.Now
        MsgBox("END1" & datoen, MsgBoxStyle.Information)

        TextFile.Close()
        datoen = Date.Now
        MsgBox("START SQL" & datoen, MsgBoxStyle.Information)

        Dim sSql As String = "LOAD DATA INFILE 'C:\\singel\\test24.txt' into table ordrer FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';"
        Call db_allaccess_nettlis(sSql)

        datoen = Date.Now
        MsgBox("SQL END" & datoen, MsgBoxStyle.Information)

Open in new window

LVL 2
team2005Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Den_HBRCommented:
What is 'hentnestenummeriserie' and 'oppdaternummerserie'?
I am using your code without these and the textfile is written in a few milliseconds
team2005Author Commented:
Hi!

Aha, so the problem is where it gets hentneste...

Here is the source for this to functions



Public Function GETQUERYRES(ByRef strsql As String) As Object
        Dim cn As System.Data.Odbc.OdbcConnection
        Dim cmd As System.Data.Odbc.OdbcCommand

        Try
            cn = New System.Data.Odbc.OdbcConnection(hmsTILPconnstr)
            cn.Open()
            cmd = New System.Data.Odbc.OdbcCommand(strsql, cn)
            GETQUERYRES = cmd.ExecuteScalar
            If GETQUERYRES Is System.DBNull.Value Then
                GETQUERYRES = 0
            End If
            cn.Close()
        Catch ex As System.Exception
            MsgBox("Error " & ex.Message)

            GETQUERYRES = 0
        End Try
    End Function

 Public Function hentnestenummeriserie(ByVal nr As Integer) As Int32

        If nr = 1 Then
            hentnestenummeriserie = GETQUERYRES("SELECT fakturanr_neste FROM informasjonomfirmaet;")
        End If

        If nr = 2 Then
            hentnestenummeriserie = GETQUERYRES("SELECT salgsordre_neste FROM informasjonomfirmaet;")
        End If

        If nr = 3 Then
            hentnestenummeriserie = GETQUERYRES("SELECT arbordre_neste FROM informasjonomfirmaet;")
        End If

        If nr = 4 Then
            hentnestenummeriserie = GETQUERYRES("SELECT kreditnota_neste FROM informasjonomfirmaet;")
        End If

    End Function

Public Sub oppdaternummerserie(ByVal nr As Integer)
        Dim sSQL As String
        Dim nestenr As Int32
        ' nr = 1 >> Fakturanummer
        ' nr = 2 >> Salgsordrenr
        ' nr = 3 >> Arbeidsordre
        ' nr = 4 >> Kreditnota

        If nr = 1 Then
            nestenr = GETQUERYRES("SELECT fakturanr_neste FROM informasjonomfirmaet;") + 1

            sSQL = "UPDATE " & tblinfofirma & " SET fakturanr_neste=" & nestenr & ";"

            Call db_allaccess_nettlis(sSQL)
        End If

        If nr = 2 Then
            nestenr = GETQUERYRES("SELECT salgsordre_neste FROM informasjonomfirmaet;") + 1

            sSQL = "UPDATE " & tblinfofirma & " SET salgsordre_neste=" & nestenr & ";"

            Call db_allaccess_nettlis(sSQL)
        End If

        If nr = 3 Then
            nestenr = GETQUERYRES("SELECT arbordre_neste FROM informasjonomfirmaet;") + 1

            sSQL = "UPDATE " & tblinfofirma & " SET arbordre_neste=" & nestenr & ";"

            Call db_allaccess_nettlis(sSQL)
        End If

        If nr = 4 Then
            nestenr = GETQUERYRES("SELECT kreditnota_neste FROM informasjonomfirmaet;") + 1

            sSQL = "UPDATE " & tblinfofirma & " SET kreditnota_neste=" & nestenr & ";"

            Call db_allaccess_nettlis(sSQL)
        End If

    End Sub

Open in new window

Den_HBRCommented:
'hentnestenummeriserie' doesn't have a where-clause
It looks like it is ALWAYS returning the same values?
("SELECT fakturanr_neste FROM informasjonomfirmaet;")

If this is the case, you better put it outside the for-loop.
Then you just get it once, instead of 500 times


...
MsgBox("START" & datoen, MsgBoxStyle.Information)
nesteordrenr = hentnestenummeriserie(2)
    For i = 1 To 500
        ornr(i) = nesteordrenr
        ordrelinjen = ornr(i)
        ...

Open in new window

Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

Den_HBRCommented:
Nevermind...
I see now that you change(+1) the value  at the end of the loop with 'oppdaternummerserie'
Den_HBRCommented:
What is the 'tblinfofirma ' in "UPDATE " & tblinfofirma & " SET salgsordre_neste=" & nestenr & ";"?

Is this always informasjonomfirmaet?
If so, you can speed up your 'oppdaternummerserie'
 
Public Sub oppdaternummerserie(ByVal nr As Integer)
        Dim sSQL As String = ""
        Dim nestenr As Int32
        ' nr = 1 >> Fakturanummer
        ' nr = 2 >> Salgsordrenr
        ' nr = 3 >> Arbeidsordre
        ' nr = 4 >> Kreditnota
        Select Case nr
            Case 1
                sSQL = "UPDATE informasjonomfirmaet SET fakturanr_neste=fakturanr_neste+1;"
            Case 2
                sSQL = "UPDATE informasjonomfirmaet SET salgsordre_neste=salgsordre_neste+1;"
            Case 3
                sSQL = "UPDATE informasjonomfirmaet SET arbordre_neste=arbordre_neste+1;"
            Case 4
                sSQL = "UPDATE informasjonomfirmaet SET kreditnota_neste=kreditnota_neste+1;"
        End Select
        If sSQL <> "" Then
            Call db_allaccess_nettlis(sSQL)
        End If
    End Sub

Open in new window

This way you don't have to retrieve the value first to increase it with 1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bansidharCommented:
some more places you can optimize

1. you are running too many updates. wrapping up the updates in a transaction is recommended
2. instead of writing to the text file 500 times use a string builder and write the entire string in one go.

HDD access (especially write) is the slowest part you are going to face so the less access the best.

also i don't know if you are going to use the "For i = 1 To 500" loop as it is now, if so you can reduce a lot of lines and improve on speed with the following

omitting parts already mentioned by Den
Dim txtout As New StringBuilder
 For i As Integer = 0 To 500
   ordrelinjen = hentnestenummeriserie(2) & ",1,TEB,28.12.2010,,,,,,,,,0,2,,,0,0,0"
   txtout.AppendLine(ordrelinjen)
 Next
 Dim TextFile As New StreamWriter("C:\\singel\\test24.txt")
 TextFile.Write(txtout.ToString)
 TextFile.Close()

Open in new window

team2005Author Commented:
Thanks for big help here :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.