C# Mysql Insert Many Rows, perfomance problem

Question

I wonder how can i bulk insert instead of execute this method everytime.

It's getting slow when i try to insert 1000 rows:

queryText = "INSERT INTO `player_items` (`player_id`, `item_id`, `count`) VALUES (@player_id, @item_id, @count)";
for (int i = 0; i < player.invenotrySize; i++)
{
    Item item = player.inventory.GetItem[i];
    MySqlParameter[] parameters = {
         new MySqlParameter("player_id", 1),
         new MySqlParameter("item_id", item.data.id),
         new MySqlParameter("count", item.amount),
    };

    ExecuteNonQuery(queryText, parameters);
}

public int ExecuteNonQuery(string queryText, params MySqlParameter[] parameters)
{
    int affectedRows = 0;
    using (MySqlConnection mySqlConnection = CreateConnection())
    {
        using (MySqlCommand mySqlCommand = new MySqlCommand(queryText, mySqlConnection))
        {
            mySqlCommand.CommandType = CommandType.Text;
            mySqlCommand.Parameters.AddRange(parameters);
            affectedRows = mySqlCommand.ExecuteNonQuery();
        }
    }

    return affectedRows;
}

I think the optimal way is to insert everything as a huge row. E.g

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

But i have no idea how i can make a method to take care of this setup

jason.kaisersmith · Accepted Answer · 2020-04-10 11:17:34Z

3

You are opening and closing your connection for every single insert.

using (MySqlConnection mySqlConnection = CreateConnection())

This is a very expensive procedure, and therefore not really the way to work with a DB.

You should open your connection just once, and then close it when finished. Depending on what you app does this might be when you start your App (or before you do your first DB query) and then close it when exiting the App (or after you are certain there will be no more DB queries.

Then ideally you should also reuse the SqlCommand instance as well. But you need to make sure that you clear your parameters in between. So then you have something like this

int affectedRows = 0;
using (MySqlConnection mySqlConnection = CreateConnection())
{
    string queryText = "INSERT INTO `player_items` (`player_id`, `item_id`, `count`) VALUES (@player_id, @item_id, @count)";
    using (MySqlCommand mySqlCommand = new MySqlCommand(queryText, mySqlConnection))
    {
        mySqlCommand.CommandType = CommandType.Text;
        for (int i = 0; i < player.invenotrySize; i++)
        {
            Item item = player.inventory.GetItem[i];
                MySqlParameter[] parameters = {
                    new MySqlParameter("player_id", 1),
                    new MySqlParameter("item_id", item.data.id),
                    new MySqlParameter("count", item.amount)};

                mySqlCommand.Parameters.Clear();
                mySqlCommand.Parameters.AddRange(parameters);
                affectedRows += mySqlCommand.ExecuteNonQuery();
        }
    }
}

answered Apr 10, 2020 at 11:17

jason.kaisersmith

9,7233 gold badges35 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Printer Over a year ago

Seems reasonable, but how can i make this to a method where i can use it on multiple things and not just one thing.

jason.kaisersmith Over a year ago

@Printer Then remove the using for connection, open your connection when the App starts up and close it on Application close, then just use it when needed,

Abhishek Over a year ago

Problem with this approach is that you are still doing the insert operation 'n' number of times

jason.kaisersmith Over a year ago

@Abhishek Of course you can always optimise more, do a single insert, use batch tools etc. But this will probably be sufficient for the OP and is significantly better than what they had before.

Stéphane M · Accepted Answer · 2020-04-14 09:00:52Z

1

It's not realy clean with your "ExecuteNonQuery" (do a multi row insert solution or just isolate/singleton the connection class like the solution above, will be better) but you can construct your whole query before execute instead of get/add connection, replace, execute foreach player.

queryText = "INSERT INTO `player_items` (`player_id`, `item_id`, `count`) VALUES";
for (int i = 0; i < player.invenotrySize; i++)
{
    Item item = player.inventory.GetItem[i];
    MySqlParameter[] parameters = {
         new MySqlParameter("player_id_"+i, 1),
         new MySqlParameter("item_id_"+i, item.data.id),
         new MySqlParameter("count_"+i, item.amount),
    };
    queryText+= " (@player_id_"+i+", @item_id_"+i+", @count_"+i+"),";

}
//remove the last ,
queryText= queryText.Remove(queryText.Length - 1)+";";

ExecuteNonQuery(queryText, parameters);

Altnernate for to skip params if you are sure about your data.

Item item = player.inventory.GetItem[i];
queryText+= " (1, "+item.data.id+", "+item.amount+"),";

edited Apr 14, 2020 at 9:00

answered Apr 10, 2020 at 11:33

Stéphane M

1437 bronze badges

3 Comments

Printer Over a year ago

I Will try it out

Printer Over a year ago

Can't get it working, it does not find the parameters.

Stéphane M Over a year ago

They should match/replace, maybe there is a limit :/ You can also construc directly your request if you are sure about de data, (i add an alternate for).

Collectives™ on Stack Overflow

C# Mysql Insert Many Rows, perfomance problem

2 Answers 2

4 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related