0

here is the loop of my vb.net code 

Do While (Not stringReader Is Nothing)
                Application.DoEvents()
                percent = Math.Round(curline * 100.0F / lineCount)

                If (String.Compare(lineAbove, "/") = 0) Then 
                    stringReader = fileReader.ReadLine()
                    curline = curline + 1
                    currentCategory = stringReader
                    TestID = TestID + 1
                    totalTests = totalTests + 1

                Else 
                    'INSERT INTO table1
                    mySqlCommand = New SqlCeCommand("
                    INSERT INTO table1(q_text, q_mark, q_category, q_test)
                    VALUES (?, ?, ?, ?)")
                    mySqlCommand.Connection = conn

                    'These VALUES
                    words = stringReader.Split(New Char() {" "c})
                    mySqlCommand.Parameters.AddWithValue("@q_text", words(0))
                    mySqlCommand.Parameters.AddWithValue("@q_mark", words(1))
                    mySqlCommand.Parameters.AddWithValue("@q_category", currentCategory)
                    mySqlCommand.Parameters.AddWithValue("@q_test", TestID)

                    'EXECUTE SQL
                    Try
                        SQLResult = mySqlCommand.ExecuteNonQuery()
                    Catch ex As Exception
                        MsgBox(ex.Message())
                    End Try

                    lblImport.Text = "Please wait ... " & percent & "%" & "."
                    TotalQuestions = TotalQuestions + 1
                End If
                stringReader = fileReader.ReadLine()
                curline = curline + 1
                lineAbove = stringReader
            Loop

which takes 10 mins (alot) to execute when i have over a million of lines in my code. Do you know how can i speed up the process for very large files ? the .txt files i read have this same format 

Titlex
q1 0
q2 1
q3 1
q4 0
/
Titlexx
q1 0
q2 1
q3 1
q4 0
q5 1

without any limit in q1,q2,...,qx and the second word is 0 or 1 always

thanks beforehand

Steve
  • 1
  • 3
  • [Most efficient way to insert Rows into MySQL Database](http://stackoverflow.com/questions/25323560/most-efficient-way-to-insert-rows-into-mysql-database) – Alex K. Apr 17 '17 at 14:17
  • https://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-bulk-data-loading.html – juergen d Apr 17 '17 at 14:17
  • MySQL has a bulk import capability which could read, parse and load the file in 2-3 lines of code (and no DoEvents). Since you are not processing the data at all, it should work fine. Also, please read [ask] and take the [Tour] – Ňɏssa Pøngjǣrdenlarp Apr 17 '17 at 14:19
  • `Application.DoEvents` could be causing a slow down also. You should put the time consuming code in a background task and only update the UI periodically. – Chris Dunaway Apr 18 '17 at 17:38

0 Answers0