Last post Feb 05, 2020 08:38 AM by jiadongm
Feb 04, 2020 05:49 PM|Peter Cong|LINK
in my asp.net mvc web app, I have an Archive button, when user click it, a jasonResult will insert a huge records from current log table to the Archive table, but the current log table contains a huge records contains 964737 rows, and it will increase as
the users are using the system.
I got this run time error on the line of code of "db.Database.ExecuteSqlCommand( "Insert into archive_Log_Table select * from current_Log_Table" )"
"Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding."
here is the entire codes behind the Archive button:
strSQL = "INSERT INTO Archive_Log_Table ";
strSQL = strSQL + "SELECT * "; strSQL = strSQL + "FROM current_Log_Table ";
var result1 = db.Database.ExecuteSqlCommand(strSQL); //here is the error message in run time
strSQL1 = "Delete "; strSQL1 = strSQL1 + "current_Log_Table ";
var result2 = db.Database.ExecuteSqlCommand(strSQL1); //after insert, delete records from current log table
ViewBag.strCurrentSQL = strCurrentSQL;
Feb 04, 2020 06:47 PM|bruce (sqlwork.com)|LINK
you can extend the 60 second timeout
db.Database.CommandTimeout = 5 * 60; // 5 minutes
but a better solution is to make the insert run faster. you are inserting too many rows at a time. you should do in batches of 10-100k. you should be able to do a million rows in under a minute.
Feb 04, 2020 06:51 PM|mgebhard|LINK
I recommend using the correct tools for the job. Write an scheduled SQL job to do to perform this action. This not not a process a web site should handle.
If you must do this from a web application then you'll need to create a back ground task to execute the SQL command(s). This will allow you to return control to the browser while the code continues to execute. Still you need a connection open to the SQL
server which is not ideal.
Feb 05, 2020 08:38 AM|jiadongm|LINK
I searched some information about this error message, the complex query may take too much time which will result in timeout.
For more details, you can refer to this link: