• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 458
  • Last Modified:

Advice on SQL to resolve a Timeout Expired error

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.
0
SeTech
Asked:
SeTech
1 Solution
 
reb73Commented:
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
0
 
sandygettingsCommented:
Until you get the database restructured, you can avoid the timeout error by increasing the amount of time allowed.

Example:

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

conn = New SqlConnection("your connection string goes here")
conn.Open()
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
rdr.Close()
conn.Close()


0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now