Giant Table Leading To TimeOut Error RRS feed

  • Question

  • User-357433193 posted

    Hi friends,

    Sure am new to ASP. Rather my very first project to check out SQL access and temporary front-end structure building via scaffolding. But regretfully facing the common problem of a large table with 3Million+ entries leading to a TimeOut problem. Have found so many different suggestions that sure am confused. As I said am totally unaware of ASP and XML etc. sure would oblige if any of you experts can advise the way to overcome the said problem in simple words as to where the timeout property is located. Need to have it set to eternal mode so as to take as long as it wants. Have tried to add Connection Time Out portion to the Connection String but none of the values attempted worked out and the page results to the same error at near around the same default time of somewhere around 30 seconds. Where should the 0 value leading to indefinite term be implemented.

    Using Visual Studio Professional 2013 with SQL Server 2014 on Visual Basic platform.

    Desperately looking forward for an early help as sure do require a setup urgently.

    Thanks in advance.

    Thursday, March 10, 2016 7:04 PM


  • User36583972 posted

    Hi Faraz A. Qureshi,

    From your description, I think causes the timeout problem are so many, there are two possibilities:

    • Estimated time is not accurate.

    • SQL statement relates to memory-intensive queries (such as sorting and hashing operations), not enough memory, we need to wait in line resources as a result.

    you can try the following suggestion to optimize your program.

    • Query optimization (Query with order by can improving efficiency)

    • Using appropriate indexes

    You should avoid full table scan. The first consideration should be given based on where and order by columns involved in the index.

    • Increase query timeout

    SqlCommand com = new SqlCommand();
      com.CommandTimeout = 60; //s

    • Increase the connection time

                string connectionString = "Data Source=(local);Initial Catalog=AdventureWorks;Integrated Security=SSPI;Connection Timeout=300";
                using (SqlConnection connection = new SqlConnection(connectionString))
                    Console.WriteLine("State: {0}", connection.State);
                    Console.WriteLine("ConnectionTimeout: {0}",connection.ConnectionTimeout);

    • Increase memory

    Best Regards,

    Yohann Lu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, March 11, 2016 6:15 AM