• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 393
  • Last Modified:

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()

Open in new window

0
shyanne
Asked:
shyanne
3 Solutions
 
Jeff CertainCommented:
Can't see enough of your code to see if this is the case, but... if you're creating a new connection for each command, that will be a HUGE bottleneck.
You may also want to consider spinning up BackgroundWorkers to allow multi-threaded updates. Much of what you're waiting on is network latencies, so you can run some pretty heavy parallelism.
0
 
CodeCruiserCommented:
ODBC is inherently slow. Can you not use Native .NET drivers? How are you reading the file and populating the variables being used in the command above?
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
shyanneAuthor Commented:
I open the database connection before I read the file.  The file is a simple comma delimited text file in which I use the streamreader object  along with the ReadLine method

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.
0
 
CodeCruiserCommented:
Did you explore the option of using the native drivers instead of ODBC?
0
 
shyanneAuthor Commented:
I'm trying that now.  I have the connection to the database using a dataset but I can't get an insert to work.
0
 
CodeCruiserCommented:
Did you configure the dataset using designer? You may want to try the commandbuilder object.
0
 
shyanneAuthor Commented:
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?  
0
 
CodeCruiserCommented:
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.
0
 
shyanneAuthor Commented:
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?
0
 
CodeCruiserCommented:
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.
0
 
shyanneAuthor Commented:
No solution was found but I was provided with some good feedback on different things to try.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now