CODEWITHSUNDEEP
c#mysql.net
Thanos Darkadakis
Thanos Darkadakis

Reputation: 1729

c# optimum way to run multiple queries

I am using a C# application, in order to manage a mySQL database.

What I want to do is:

In order to calculate n-th "stuff", I must have already calculated (n-1)-th "stuff".

This is what I do:

Declare:

static MySqlCommand cmd;
static MySqlDataReader dr;

My main loop is like following:

for (...)
{
    dr.Close();
    cmd.CommandText = "insert into....";
    dr = cmd.ExecuteReader();
}

This is taking way too long. Total number of rows to be inserted is about 2.5M.

When I use mySql database in regular server, it takes about 100-150 hours. When I use a localhost database, it takes about 50h.

I think there should be a quicker way. My thoughts:

Upvotes: 2

Views: 999

Answers (2)

O. Jones
O. Jones

Reputation: 108651

It's possible you are using InnoDB as an access method. In this case you should try wrapping every hundred or so rows of INSERT operations in a transaction. When I have had to handle this kind of application, it's made a huge difference. To do this, structure your code like so:

    MySqlCommand commit;
    start.CommandText = "START TRANSACTION";
    MySqlCommand commit;
    commit.CommandText = "COMMIT";
    int bunchSize = 100;
    int bunch = 0;

    start.ExecuteNonQuery();   /* start the first bunch transaction */

    bunch = bunchsize;
    for(/*whatever loop conditions you need*/) {

        /* whatever you need to do */

        /* your insert statement */
        if (--bunchsize <= 0) {
             commit.ExecuteNonQuery();   /* end one bunch transaction */
             start.ExecuteNonQuery();    /* and begin the next */
             bunchsize = bunch;
        } 
    }
    commit.ExecuteNonQuery();   /* end the last bunch transaction */

It is also possible that the table to which you're inserting megarows has lots of indexes. In this case you can speed things up by beginning your series of INSERTs with

 SET unique_checks=0;
 SET foreign_key_checks=0;
 ALTER TABLE tbl_name DISABLE KEYS;

and ending it with this sequence.

 ALTER TABLE tbl_name ENABLE KEYS;
 SET unique_checks=1;
 SET foreign_key_checks=1;

You must take great care in your software to avoid inserting rows that would be rejected as duplicates when you use this technique, because the ENABLE KEYS operation will not work in that case.

Read this for more information: http://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-bulk-data-loading.html

Upvotes: 0

DROP TABLE users
DROP TABLE users

Reputation: 1955

Try using a bulk insert. I found this syntax here. And then use ExecuteNonQuery() as SLaks suggested in the comments. Those combined may speed it up a good bit.

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

Upvotes: 2

Related Questions