How to rewrite OdbcCommand for bulk insert?

Question

MySQL

How to write better code. which connects to mysql and inserts about 30k records into a table.

var sqlCmd_KSLP = SqlClient.CreateCommand("SELECT KSLP, NSLP FROM SLP ORDER BY KSLP ASC");
                using (var reader = sqlCmd_KSLP.ExecReader())
                {
                    string my_queryString_insert = "insert into org_iogjt_import_org_gate_job_title (iogjt_jobtitle_id, iogjt_jobtitle_name, iogds_datasource_id) VALUES (?, ?, 1)";
                    OdbcCommand my_command_insert = new OdbcCommand(my_queryString_insert);
                    my_command_insert.Parameters.Add("@KSLP", OdbcType.Int);
                    my_command_insert.Parameters.Add("@NSLP", OdbcType.VarChar, 255);
                    my_command_insert.Connection = my_connection;

                    while (reader.Read())
                    {
                        var row = reader.GetObject(new { KSLP = 0, NSLP = "" });
                        if (row.KSLP != 0)
                        {
                            
                            my_command_insert.Parameters["@KSLP"].Value = row.KSLP.ToString();
                            my_command_insert.Parameters["@NSLP"].Value = row.NSLP.ToString();

                            my_command_insert.ExecuteNonQuery();
                        }
                    }
                }

Now this code runs for more than one hour.

Answer 1

And the base itself will not be able to execute the insert query on the selection?

insert into org_iogjt_import_org_gate_job_title (iogjt_jobtitle_id, iogjt_jobtitle_name, iogds_datasource_id) 
    SELECT KSLP, NSLP, 1 FROM SLP WHERE KSLP <> 0 ORDER BY KSLP ASC

Then you can simply execute the query, without a loop and without parameters.

Answer 2

You have been given sound advice above. Plus - turn off indexing at the time of insertion. The write speed will increase significantly.

Answer 3

https://docs.microsoft.com/ru-ru/dotnet/api/system...
https://habr.com/ru/post/137038/

Answer 4

You can use the bulk paste command

INSERT INTO org_iogjt_import_org_gate_job_title (iogjt_jobtitle_id, iogjt_jobtitle_name, iogds_datasource_id) VALUES(@[email protected],@p3)
,(@[email protected],@p6)
,(@[email protected],@p9), ...

In the loop, complete the command and assign parameters
Thus, by grouping the query, more rows will be inserted at a time.