DataAdapter problem (VB.Net,MySQL)
HI guys..
Need some help here, plz.. I can't solve this.. :(
My batch insert is extremely slow.. :( So ther must be something wrong with my code
It says that it dosen't suport updatebatchsize.
If i remove updatebatchsize it works ,but it takes over 6,5 secounds to insert 8000 rows,
If I create 8000 querys with a loop it take about 3,5 secounds to insert them all.
So there is something that dosen't work as it should..
Here is my code............
Public Sub BatchUpdate(ByVal dt As DataTable)
Dim MyConString As String = _
"Persist Security Info=False;" & _
"DATABASE=test;" & _
"PORT=3307;" & _
"SERVER=127.0.0.1;" & _
"Connect Timeout=30;" & _
"user id=test;" & _
"pwd=test;"
'Create a SQLConnection, passing in the proper connection string
Dim myConnection As New MySqlConnection()
myConnection.ConnectionStr
'Create a SQLDataAdapter
Dim da As New MySqlDataAdapter()
'Create and configure a SQLCommand for the DataAdapter
da.InsertCommand = New MySqlCommand()
With da.InsertCommand
'Set the connection
.Connection = myConnection
'Must be set to none
.UpdatedRowSource = UpdateRowSource.None
'Set the CommandType to Text
.CommandType = CommandType.Text
'Set the SQL Text, including parameters
.CommandText = _
"INSERT INTO testTbl" & _
"(ImportDate, FileDate, FileType) " & _
"VALUES (?ImportDate, ?FileDate, ?FileType) " & _
"ON DUPLICATE KEY UPDATE DuplicateInsert = 1;"
'Add the parameters.
' Make sure to map the source colum in our table to the parameter
With .Parameters
.Add("?ImportDate", MySqlDbType.Date, 0, "ImportDate")
.Add("?FileDate", MySqlDbType.Date, 0, "FileDate")
.Add("?FileType", MySqlDbType.VarChar, 15, "FileType")
End With
End With
'Setting the UpdateBatchSize to 0
' will submit all the rows in a single batch.
da.UpdateBatchSize = 0
'Perform the insert to the SQL table.
da.Update(dt)
End Sub
Need some help here, plz.. I can't solve this.. :(
My batch insert is extremely slow.. :( So ther must be something wrong with my code
It says that it dosen't suport updatebatchsize.
If i remove updatebatchsize it works ,but it takes over 6,5 secounds to insert 8000 rows,
If I create 8000 querys with a loop it take about 3,5 secounds to insert them all.
So there is something that dosen't work as it should..
Here is my code............
Public Sub BatchUpdate(ByVal dt As DataTable)
Dim MyConString As String = _
"Persist Security Info=False;" & _
"DATABASE=test;" & _
"PORT=3307;" & _
"SERVER=127.0.0.1;" & _
"Connect Timeout=30;" & _
"user id=test;" & _
"pwd=test;"
'Create a SQLConnection, passing in the proper connection string
Dim myConnection As New MySqlConnection()
myConnection.ConnectionStr
'Create a SQLDataAdapter
Dim da As New MySqlDataAdapter()
'Create and configure a SQLCommand for the DataAdapter
da.InsertCommand = New MySqlCommand()
With da.InsertCommand
'Set the connection
.Connection = myConnection
'Must be set to none
.UpdatedRowSource = UpdateRowSource.None
'Set the CommandType to Text
.CommandType = CommandType.Text
'Set the SQL Text, including parameters
.CommandText = _
"INSERT INTO testTbl" & _
"(ImportDate, FileDate, FileType) " & _
"VALUES (?ImportDate, ?FileDate, ?FileType) " & _
"ON DUPLICATE KEY UPDATE DuplicateInsert = 1;"
'Add the parameters.
' Make sure to map the source colum in our table to the parameter
With .Parameters
.Add("?ImportDate", MySqlDbType.Date, 0, "ImportDate")
.Add("?FileDate", MySqlDbType.Date, 0, "FileDate")
.Add("?FileType", MySqlDbType.VarChar, 15, "FileType")
End With
End With
'Setting the UpdateBatchSize to 0
' will submit all the rows in a single batch.
da.UpdateBatchSize = 0
'Perform the insert to the SQL table.
da.Update(dt)
End Sub
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I'm trying to understan how the code you gave me should be implemented in my code, :)
Can you give me an example..
Can you give me an example..
ASKER
What I can't figureout is how I get the data from the table to the insert query or how i bind the datatable with the command
ASKER
Ihave this code now.. but it takes even more time to execute 7 sec
Public Sub SQLUpdate(ByVal dt As DataTable)
Try
Dim MyConString As String = _
"Persist Security Info=False;" & _
"DATABASE=test;" & _
"PORT=3308;" & _
"SERVER=127.0.0.1;" & _
"Connect Timeout=30;" & _
"user id=test;" & _
"pwd=ttest;"
'Create a SQLConnection, passing in the proper connection string
Dim myConnection As New MySqlConnection(MyConStrin
myConnection.Open()
'Create new command
Dim cmd As New MySqlCommand
With cmd
'Set the connection
.Connection = myConnection
'Must be set to none
.UpdatedRowSource = UpdateRowSource.None
'Set the CommandType to Text
.CommandType = CommandType.Text
''Set the SQL Text, including parameters
.CommandText = _
"INSERT INTO resa " & _
"(ImportDate, FileDate, FileType) " & _
"VALUES (?ImportDate, ?FileDate, ?FileType) " & _
"ON DUPLICATE KEY UPDATE DuplicateInsert = 1"
End With
Dim row As DataRow
For Each row In dt.Rows
With cmd.Parameters
.AddWithValue("?ImportDate
.AddWithValue("?FileDate",
.AddWithValue("?FileType",
End With
'Perform the insert to the SQL table.
cmd.ExecuteNonQuery()
cmd.Parameters.Clear()
Next
Catch ex As Exception
End Try
End Sub
Public Sub SQLUpdate(ByVal dt As DataTable)
Try
Dim MyConString As String = _
"Persist Security Info=False;" & _
"DATABASE=test;" & _
"PORT=3308;" & _
"SERVER=127.0.0.1;" & _
"Connect Timeout=30;" & _
"user id=test;" & _
"pwd=ttest;"
'Create a SQLConnection, passing in the proper connection string
Dim myConnection As New MySqlConnection(MyConStrin
myConnection.Open()
'Create new command
Dim cmd As New MySqlCommand
With cmd
'Set the connection
.Connection = myConnection
'Must be set to none
.UpdatedRowSource = UpdateRowSource.None
'Set the CommandType to Text
.CommandType = CommandType.Text
''Set the SQL Text, including parameters
.CommandText = _
"INSERT INTO resa " & _
"(ImportDate, FileDate, FileType) " & _
"VALUES (?ImportDate, ?FileDate, ?FileType) " & _
"ON DUPLICATE KEY UPDATE DuplicateInsert = 1"
End With
Dim row As DataRow
For Each row In dt.Rows
With cmd.Parameters
.AddWithValue("?ImportDate
.AddWithValue("?FileDate",
.AddWithValue("?FileType",
End With
'Perform the insert to the SQL table.
cmd.ExecuteNonQuery()
cmd.Parameters.Clear()
Next
Catch ex As Exception
End Try
End Sub
I wasn't sure that the code with the loop you've written above, would be quicker (apparantly not according to your tests...). So I've got 2 other questions for you.
How do you load your recordset, from where does your data come ? Do you need to update all the records in your database ?
How do you load your recordset, from where does your data come ? Do you need to update all the records in your database ?
ASKER
This is the flow I working with
1: The data comes from a CSV file
2: Amost all the data are new, 99.9% of the 8000 rows
3:; Some of the data has a stange format in the CSV file 0.0 can be .0 (dot zero) or sometimes even empty string, I also have .00 (dot zero zero) that should be 0.00
4: This data has to be fixed before I insert it
5: I read all data to the datatable
6: I fix the values
7: I insert them
If I can solve the problem in a bertter way it would be great. but can't figure out any other methods
1: The data comes from a CSV file
2: Amost all the data are new, 99.9% of the 8000 rows
3:; Some of the data has a stange format in the CSV file 0.0 can be .0 (dot zero) or sometimes even empty string, I also have .00 (dot zero zero) that should be 0.00
4: This data has to be fixed before I insert it
5: I read all data to the datatable
6: I fix the values
7: I insert them
If I can solve the problem in a bertter way it would be great. but can't figure out any other methods
Do you have many indexes on the table you import ? Try to disable them, do the insert and put the indexes back. Sometimes that's helpful for quick loading a lot of data.
By the way, very slow is relative...
With update-batch: 705 insert's each second
With loop: 1715 insert's each second
I don't think that is very slow, but that's my opinion.
With update-batch: 705 insert's each second
With loop: 1715 insert's each second
I don't think that is very slow, but that's my opinion.
ASKER
Oki. how look you code for a batch-update/insert
I have 8000 rows. Batch-update: 6,5 sec: that's 1203 rec / sec
Loop: 3,5 sec:: thats 2285
It's not the problem that it's slow. the confusion is that a loop is much faster the a batch insert .
That's way im intrested in how other do there batch insert, and see if I can change my code to the better
I have 8000 rows. Batch-update: 6,5 sec: that's 1203 rec / sec
Loop: 3,5 sec:: thats 2285
It's not the problem that it's slow. the confusion is that a loop is much faster the a batch insert .
That's way im intrested in how other do there batch insert, and see if I can change my code to the better
ASKER
And here is the result if I send more the once.
1 3,5 sec
500: 1,3 sec
1500: 1,2 sec
2500: 1,14 sec
6000: 1,09 sec
So I thought a batch insert was even faster the that
But a question regarding turning the indexes off, is some of the records already exists in the database, is it still ok to turn them off?
1 3,5 sec
500: 1,3 sec
1500: 1,2 sec
2500: 1,14 sec
6000: 1,09 sec
So I thought a batch insert was even faster the that
But a question regarding turning the indexes off, is some of the records already exists in the database, is it still ok to turn them off?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I use ON DUPLICATE KEY UPDATE so thats ok.
ASKER
and it works on a batch-update to, i have ON DUPLICATE KEY UPDATE there to,,
But what I can understand now is that the fastest way to insert data is looping, except the LOAD FILE function but thats not a option here because I need to alter the data befor I insert it.
But what I can understand now is that the fastest way to insert data is looping, except the LOAD FILE function but thats not a option here because I need to alter the data befor I insert it.
ASKER
I don't need something back I just want to insert all data. so maybe your solution is better. :)
So how do I insert all data from the datatable with you code?
Thx for helping me.. :)