shyanne
asked on
How do I speed up writing to a MySQL database when using VB 2005
I am writing very large files to a MySql database (5.0) through VB 2005 (using MySQL ODBC 3.51 driver) and the speed is very slow. It is taking 20 to 25 minutes to write 51,000 records. I have enclosed the code for the insert statement and the execute command. Any help to speed this up would be greatly appreciated.
myCommand.CommandText = "Insert into cmix " _
& "values('" & strDate & "', " & seq & ", " & endSeq _
& ", '" & strWhse & "', " & batchNo & ", '" _
& mailId & "', " & rank & ", '" _
& sku & "', '" & desc _
& "', " & qty & ", " & weight & ")" myCommand.ExecuteNonQuery()
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Did you explore the option of using the native drivers instead of ODBC?
ASKER
I'm trying that now. I have the connection to the database using a dataset but I can't get an insert to work.
Did you configure the dataset using designer? You may want to try the commandbuilder object.
ASKER
I did use the designer. I wanted to see how long it would take with an access database and it took even longer. What sort of time is resonable to write 51,000 + records?
I think the main time consuming task is file reading. Comment out the db code and see how long it takes to just read the file and assign variables. Also try to use the file.readalllines method.
ASKER
I commented out the db code and read the file and wrote to a text file and it took seconds. I was wondering if it was something in vb.net so I wrote and ran the code in VB 6.0 and I still have the same problem. I am going to test the network next. Any other ideas? Does it matter that the table is of the InnoDB type? Would I see increased performance if it was a MyISAM table type?
To be honest, i have no knowledge of mysql so can not comment about the table type. Do test the network. Try writing to a file on the mysql server computer.
ASKER
No solution was found but I was provided with some good feedback on different things to try.
ASKER
myCommand.Connection = conn
conn.open
inputFile = File.OpenText("filename")
do until inputFile.Peek = -1
strInput = inputFile.ReadLine
***Here I set the variables***
Loop
I was experimenting with the load statement in MySQL. Since this is a comma delimited file it might be faster but I have to add some fields (like the date and a calculated field) and without creating a new comma delimited file I don't know how I could use the load statement.