Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 600
  • Last Modified:

Fastest way to insert 8000 rows of data to MySQL

Hi guys,, need your expert advice here. :)

I need some help here to figure out what I should do to make a fast insert..

 I have q question open here regaring batch update.
http://www.experts-exchange.com/Programming/Languages/.NET/Visual_Basic.NET/Q_22713198.html

I need some advice what type of method I should use when inserting 8000 rows of data to my MySQL database.

What I have heard is that the DataAdpter is the fastest way to insert data to a MySQL table.

But when using dataadapter I got 6,5 sec and when using a loop that created 8000 querys where much faster 3,5 sec.

So now i'm confused,  which method should I use to get the best result :)

thx
0
AWestEng
Asked:
AWestEng
  • 6
  • 4
1 Solution
 
Priest04Commented:
Well, you said it yourself - Command object is more appropriate in this situation. Since you will be doing only insert queries, then no need for DataAdapter. Just use a loop, and pass paramter values to the Command object. And everything that is not going to change in a loop (like a CommandText) should be set outside of the loop, for the sake of performance.

Goran
0
 
AWestEngAuthor Commented:
Ok, but 3,5 sec is a long time  to for 8000 rows, should that have to take so long,?

A batch update should that not be faster?

I was installing the MySQL visual studio plugin. ANd when doing that i got he message that the driver  is not properly installed and configured, can that habe somthing to do with the lake of preformance?

And where can i config the driver if that so?
0
 
Priest04Commented:
>> Ok, but 3,5 sec is a long time  to for 8000 rows, should that have to take so long,?

Well, it is an acceptable time. Try to update using stored procedure, and see if it is faster.

Goran
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
AWestEngAuthor Commented:
It would be greate to go under 1 secound,

Peopele said that my loop thing was not good it takes to long time when I was akining about in another question. Anf they said that i shuold use dataadapter to increase the speed.

it makes me abit confused.. hehe :)

I'm a new to stored procedure, is it possible that you can help me. an example maybe?
0
 
AWestEngAuthor Commented:
and one other stange thing is that the Mysql.data is slower then ODBC 3.51 when it comes to the executeNonQuery.
ODBC is over 1 sec faster.
0
 
Priest04Commented:
>> Ok, but 3,5 sec is a long time  to for 8000 rows, should that have to take so long,?

Try to update using stored procedure, and see if it is faster.

Goran
0
 
AWestEngAuthor Commented:
ops.. I got you answer tvice.. :)

 would be greate to go under 1 secound,

Peopele said that my loop thing was not good it takes to long time when I was akining about in another question. Anf they said that i shuold use dataadapter to increase the speed.

it makes me abit confused.. hehe :)

I'm a new to stored procedure, is it possible that you can help me. an example maybe?
0
 
Priest04Commented:
Hmmm... I dont know what I have done, I thought that I have already posted stored procedure example. Well, here it is again, example how to create one and  call it

http://dev.mysql.com/doc/refman/5.1/en/connector-net-using-stored.html

Goran
0
 
AWestEngAuthor Commented:
I get errors on these row

Property access must assign to the property or use its value.

            cmd.Parameters.Add("?lname", 'Jones')
    cmd.Parameters["?lname"].Direction = ParameterDirection.Input

    cmd.Parameters.Add("?fname", 'Tom')
    cmd.Parameters["?fname"].Direction = ParameterDirection.Input
0
 
AWestEngAuthor Commented:
I solved it..
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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