Advice on SQL to resolve a Timeout Expired error

Posted on 2009-02-20
Last Modified: 2012-05-06
I've been given the unenviable task of taking an existing MS Access database complete with forms and making an ASP.Net/SQL Server 2005 web page out of it.

I did a great deal of analysis on the Access database, and how each form displayed the data so that I could approximate the look and feel of the Access DB, and hopefully make it smoother and faster, if possible.

What I found, though, is that the output of data on several of the forms came from queries that ran on other queries that ran on other queries, ad nauseum. For example, the display I just tried to replicate draws from a Query, that in turn draws from 3 queries. Of those 3 queries, 2 draw directly from tables, but the third draws from yet another query, which draws from yet another query, which FINALLY joins two tables. All of this is to do some calculations (such as sums and averages) on the data to return in a specific format for displaying.

Now, this seemed inefficient, but just until I understood the project better, after I imported the data tables into SQL Server 2005, I re-created each of those sub-queries using Views, so basically, I have views calling views calling views, etc. I have seen this same idea in one of my coworkers' database design in the past, so I figured it was valid and workable. My idea was that the final Query to display the data, that drew from the 3 initial queries, I would use a Stored Procedure to bring in the necessary parameter, and get the results. And when I use SQL Server Management Studio's Query window and execute the SP, it runs fine, although it runs very very slowly. It doesn't even really return that many rows, and none of the tables drawn on by the Views contains all that many rows (maybe a few thousand at most).

However, when I try to run this SP from my webpage, I get an error saying "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding." After Googling this, I have not really found anything that useful to me.

So I guess my biggest question is should I follow some advice I heard from a coworker and instead of chaining the views like i'm doing, chain SPs and use temp tables within those SPs? Or am I OK to keep using my chained Views? Anything to suggest/ More questions? This is a really important project and I need to have it done honestly by the end of the month.
Question by:SeTech
    LVL 25

    Accepted Solution

    Chaining views is probably the easy way out, but can turn out to be a bottleneck as everything has to be evaluated dynamically..

    Stored Procedures would definitely be the way to go, but the timeout points to a query timeout server setting being insufficient..

    Can you list the procedure here, temp tables or CTE's should definitely help improve the execution time
    LVL 3

    Expert Comment

    Until you get the database restructured, you can avoid the timeout error by increasing the amount of time allowed.


    Dim conn As SqlConnection
    Dim cmd As SqlCommand
    Dim rdr As SqlDataReader

    conn = New SqlConnection("your connection string goes here")
    cmd = New SqlCommand("SELECT * FROM MYTABLE", conn)
    cmd.CommandTimeout = 2400 ' 40-minute timeout for long queries
    rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
    While rdr.Read
    End While


    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    This video teaches users how to migrate an existing Wordpress website to a new domain.
    Use Wufoo, an online form creation tool, to make powerful forms. Learn how to choose which pages of your form are visible to your users based on their inputs. The page rules feature provides you with an opportunity to create if:then statements for y…

    729 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

    18 Experts available now in Live!

    Get 1:1 Help Now