StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POVB.NET Slow Insert of Datagrid rows to MySQL
text
Body
copied!<p>I have a routine imports a CSV into the datagrid then simply a for-next loop to insert each row, but its running slow 18 Minutes to insert 7100 lines.</p> <p>Now I have already taken the connection call out of the loop so it only does it once, performs the insertions then closes when finished.</p> <p>I have seen mentioned to bulk insert 100 rows at a time might help, but that would probably require some conveluted routine to calculate the rows its currently upto and then the odd number after so many lots of 100.</p> <p>Is there a simple way of just saying insert the whole datagrid instead of 100 rows without a for/next loop?</p> <p>As you will see below, for fowmatting and ease to read I have broken up my query string, would joining it together into one line make a vast difference? I may have already tried this by the time you reply:</p> <pre><code> con = New MySqlConnection("Data Source=" & vardbpath & ";Database=" & vardbname & ";User ID=" & txtUsername.Text & ";Password=" & txtPassword.Text & ";") con.Open() For i = 0 To rows - 1 Query = "INSERT into GENERAL (a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z,aa,ab,ac,ad)" Query &= "values ('" Query &= (DataGrid1.Rows(i).Cells(0).Value) Query &= "','" + (DataGrid1.Rows(i).Cells(1).Value) Query &= "','" + (DataGrid1.Rows(i).Cells(2).Value) Query &= "','" + (DataGrid1.Rows(i).Cells(3).Value) Query &= "','" + (DataGrid1.Rows(i).Cells(4).Value) Query &= "','" + (DataGrid1.Rows(i).Cells(5).Value) Query &= "','" + (DataGrid1.Rows(i).Cells(6).Value) Query &= "','" + (DataGrid1.Rows(i).Cells(7).Value) Query &= "','" + (DataGrid1.Rows(i).Cells(8).Value) Query &= "','" + (DataGrid1.Rows(i).Cells(9).Value) Query &= "','" + (DataGrid1.Rows(i).Cells(10).Value) Query &= "','" + (DataGrid1.Rows(i).Cells(11).Value) Query &= "','" + (DataGrid1.Rows(i).Cells(12).Value) Query &= "','" + (DataGrid1.Rows(i).Cells(13).Value) Query &= "','" + (DataGrid1.Rows(i).Cells(14).Value) Query &= "','" + (DataGrid1.Rows(i).Cells(15).Value) Query &= "','" + (DataGrid1.Rows(i).Cells(16).Value) Query &= "','" + (DataGrid1.Rows(i).Cells(17).Value) Query &= "','" + (DataGrid1.Rows(i).Cells(18).Value) Query &= "','" + (DataGrid1.Rows(i).Cells(19).Value) Query &= "','" + (DataGrid1.Rows(i).Cells(20).Value) Query &= "','" + (DataGrid1.Rows(i).Cells(21).Value) Query &= "','" + (DataGrid1.Rows(i).Cells(22).Value) Query &= "','" + (DataGrid1.Rows(i).Cells(23).Value) Query &= "','" + (DataGrid1.Rows(i).Cells(24).Value) Query &= "','" + (DataGrid1.Rows(i).Cells(25).Value) Query &= "','" + (DataGrid1.Rows(i).Cells(26).Value) Query &= "','" + (DataGrid1.Rows(i).Cells(27).Value) Query &= "','" + (DataGrid1.Rows(i).Cells(28).Value) Query &= "','" + (DataGrid1.Rows(i).Cells(29).Value) Query &= "')" Dim cmd As MySqlCommand = New MySqlCommand(Query, con) Try Dim j As Integer = cmd.ExecuteNonQuery() If (j > 0) Then txt_folderactivity.Text &= "Row Successfully Inserted" & vbCrLf Catch myerror As MySqlException txt_folderactivity.Text &= "Error Executing SQL command: " & myerror.Message & vbCrLf End Try </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload