CODEWITHSUNDEEP
c#mysql
Printer
Printer

Reputation: 465

C# Mysql Insert Many Rows, perfomance problem

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

Upvotes: 0

Views: 59

Answers (2)

St&#233;phane M
St&#233;phane M

Reputation: 143

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+"),";

Upvotes: 1

jason.kaisersmith
jason.kaisersmith

Reputation: 9650

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

Upvotes: 3

Related Questions