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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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(CommandB
While rdr.Read
...
End While
rdr.Close()
conn.Close()