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

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

Seems reasonable, but how can i make this to a method where i can use it on multiple things and not just one thing.
@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,
Problem with this approach is that you are still doing the insert operation 'n' number of times
@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

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

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

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

3 Comments

Related