Tuesday, July 21, 2020

How to solve ASP.NET MVC Exception: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated

ASP.NET MVC can give this error probably when you have a long running query. It can happen when:
1. You are running query on a very large dataset
2. Your query is complex and inefficient
3. Database statistics and/or query plan cache are incorrect
4. You are in deadlock

You should be able to find out the specific problem you are facing from stack trace.

Solutions:

Step 1:
If your query needs more than the default 30 seconds, you might want to set the CommandTimeout higher. To do that you'll change it after you instantiated the DataAdapter on the SelectCommand property of that instance, like so:

        protected DataTable GetDataTable(string Query, CommandType cmdType, params SqlParameter[] parameters)
        {
            string strCon = DataContext.Database.Connection.ConnectionString;
            DataTable dt = new DataTable();
            using (SqlConnection con = new SqlConnection(strCon))
            {
                using (SqlCommand cmd = new SqlCommand(Query, con))
                {
                    cmd.CommandType = cmdType;
                    cmd.Parameters.AddRange(parameters);
                    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                    {
                        da.SelectCommand.CommandTimeout = 60 * 10;
                        da.Fill(dt);
                    }
                }
            }
            return dt;
        }


Step 2:
To reduce query complexity you need to apply different efficient methods to rewrite query as there is no specific method for improving query performance. You can apply indexing if you have large datasets. DBCC command is helpful to re-index and rebuild indexes which can also improve if you have large datasets.
DBCC DBREINDEX ('HumanResources.Employee', ' '); 


Step 3:

Improve database table query plan statiscs for all tables and index with a single command:

exec sp_updatestats

If that doesn't work you could also try

dbcc freeproccache

Hope this can be helpful.

No comments:

Post a Comment

Please keep your comments relevant.
Comments with external links and adult words will be filtered.