[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Advice on SQL to resolve a Timeout Expired error

Posted on 2009-02-20
2
Medium Priority
?
451 Views
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.
0
Comment
Question by:SeTech
2 Comments
 
LVL 25

Accepted Solution

by:
reb73 earned 2000 total points
ID: 23695924
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
 
LVL 3

Expert Comment

by:sandygettings
ID: 23697551
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

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A publishing tool, a Version Control System, or a Collaboration Platform! These can be some of the defining words for the two very famous web-hosting Git repositories: Bitbucket and Github. Git is widely used amongst the programmers and developers f…
Simulator games are perfect for generating sample realistic data streams, especially for learning data analysis. It is even useful for demoing offerings such as Azure stream analytics, PowerBI etc.
Wufoo.com provides powerful tools for surveying targeted groups, and utilizing data from completed surveys to find trends, discover areas of demand or customer expectation, and make business decisions on products or services.
Learn how to set-up PayPal payment integration in your Wufoo form. Allow your users to remit payment through PayPal upon completion of your online form. This is helpful for collecting membership payments, customer payments, donations, and more.

834 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