Timeout Expired Error When Running Long Query and Large Database

Posted on 2008-11-06
Last Modified: 2012-05-05
I have been getting the infamous 'Timeout Expired' problem with my web application developed using MS .NET and using Microsoft SQL Server 2005. The query is long and the database contains over 800K records further linked with some other tables. The query worked fine when the database contained about 500-600K records (we have a backup of the old database). On 500-600K records, the query takes less than a minute to complete.

Now, I've been browsing the internet for solutions and we did try them. These are:
1. Changing SqlCommand.CommandTimeout and SqlConnection.ConnectionTimeout to 0.
2. Adding 'with (nolock)' to the query.

However, neither of them work. I am in desperate needs for help. Thank you in advance.

Best regards,
Question by:tapanan2519
    LVL 22

    Expert Comment

    Dear friend,
    I reseolved a similar problem installing cmulative package 7 (after SQL SP2) that fixes several bugs:

    Accepted Solution

    I have found that the problem is with using SqlAdapter. After setting timeout = 0 for the SqlCommand object, I use it to construct a SqlAdapter object, where it seems the timeout value was not passed to it. So basically, I had to set timeout=0 on the SqlAdapter object, rather than the original of SqlCommand.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
    CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now