Cedric Vincent Cedric Vincent - 2 months ago 12
MySQL Question

Insert data to Mysql getting slower and slower

I need to insert 388 datas per minute to local Database.
At first when the table is Empty, I only need 5 second to Insert to database.

But when the table gets larger, the program efficacy slow down to more than one minute when the amount of rows comes to 1,026,558.

And the useage of CPU is 100%. It's unusual.

here is my code:

public static void dataToDB(String[] routeIDArray,String[] levelArray,String[] valueArray,String[] travelTimeArray, int amountOfData)
{
MySqlConnection con = new MySqlConnection(connStr);
MySqlCommand cmd = null;
MySqlDataReader rdr = null;
String sqlCmd, updateSqlCmd = "UPDATE `datetimetable` SET ";
for(int counter = 0; counter < amountOfData; counter++)
{
sqlCmd = "ALTER TABLE `datetimetable` ADD COLUMN IF NOT EXISTS `" + routeIDArray[counter] + "` INT NULL;"
+ "INSERT INTO `roadvalue`.`data` (`level`,`value`,`traveltime`) VALUES ("
+ levelArray[counter] + ","
+ valueArray[counter] + ","
+ travelTimeArray[counter] + ");"
+ "SELECT LAST_INSERT_ID() FROM `data`;";
cmd = new MySqlCommand(sqlCmd, con);
con.Open();
rdr = cmd.ExecuteReader();
rdr.Read();
updateSqlCmd += "`" + routeIDArray[counter] + "` = " + rdr[0] + ",";
rdr.Close();
}
updateSqlCmd = updateSqlCmd.TrimEnd(',');
updateSqlCmd += " WHERE EXISTS (SELECT * WHERE dateTime = '" + dateTime.ToString("yyyy-MM-dd HH:mm:00") + "');";
cmd = new MySqlCommand(updateSqlCmd, con);//update data key to datetimetable
cmd.ExecuteNonQuery();
Console.WriteLine("Done.");
con.Close();
}
public static void checkDateTimeExisted()
{
MySqlConnection con = new MySqlConnection(connStr);
MySqlCommand cmd;
String sqlCmd;
sqlCmd = "INSERT INTO `datetimetable` (`dateTime`) SELECT * FROM (SELECT '" + dateTime.ToString("yyyy-MM-dd HH:mm:00")
+ "') AS tmp WHERE NOT EXISTS(SELECT `dateTime` FROM `datetimetable` WHERE `dateTime` = '" + dateTime.ToString("yyyy-MM-dd HH:mm:00") + "') LIMIT 1; ";

con.Open();
cmd = new MySqlCommand(sqlCmd, con);
cmd.ExecuteNonQuery();
con.Close();
}


And Mysql Engine is InooDB, table "data" has one Auto_Increment Primary key, table "datetimetable" has an Auto_Increment Primary key and a not duplicate datetime as index.

What have I done wrong?

Answer

I find the answer, the command "SELECT LAST_INSERT_ID() FROM data;" should add LIMIT 1 or it will get all the ID kill the performance.