Hey folks, I've been teaching myself ASP.net for the past 8 months as I've been taking over an existing application running ASP.net 1.1.  I've been testing successfully on my local machine using a small data set but I recently found an issue when the same code was being used on the production server.  In production, the data set (SQL Server 2000, btw) being queried is quite large and the stored procedure is fairly complex.  This adds up to a run-time for the stored procedure of around 2 hours.

The expected behavior of the page is as follows (and it works in ASP.net 1.1 mode):

1. Choose criteria in the form and submit.
2. Build stored procedure call and execute ( adapter.Fill(ds) ).
3. Bind DataGrid to DataSource.
4. Update a few labels, etc.

What is happening is that when IIS is configured to use ASP.net 2.0, it basically seems to stop after step 2.  The page churns for about an hour and a half and then stops as if it is finished.  However, the DataGrid is not displayed and none of the labels are updated.  It's almost as if it gave up interacting with the web controls.  Funny thing is, some steps ARE executed after the database call such as a write to the log file (see orange and green highlighting below).  So basically it looks like it finishes executing but anything referencing a web control is unable to complete.

While I come from an environment of responsive web applications, this one is used by only one or two people at a time and thus they are accustomed to clicking, leaving, and checking back later for the results.  Therefore I'm not looking for suggestions about asynchronous DB calls, which I'm sure would be a good end goal and the 'ideal' user experience for many.

Posting some of the code (not complete) below to give you an idea.  I appreciate anyone's insight into what may be causing this.

1    strSqlCode = "Execute " & strRepCode & " '" & sTaxYear & "','" & intCorrType & "','" & sSubFile & "', '" & sServCent & "', '" & sTreats & "'"
2                    
3    Write2Log(strSqlCode)
4    ' get report from SQL server
5    connx= New SqlConnection(Application("DSN"))
6    connx.Open()
7    cmdSelect = New SqlCommand(strSqlCode, connx)
8    cmdSelect.CommandTimeout = 10 * 60 * 60
9      
10   Dim myAdapter As SqlDataAdapter = New SqlDataAdapter()
11   myAdapter.SelectCommand = cmdSelect
12   Dim ds As DataSet = New DataSet()
13   
14   DataGrid2.Visible = True
15   'Load Data Set from XML file or refresh from Database
16   
17   myAdapter.Fill(ds)
18   
19   'Write out schema representation
20   ds.WriteXmlSchema(schemaFileName)
21   'Write out XML data form relational data
22   ds.WriteXml(reportFileName, XmlWriteMode.IgnoreSchema)
23   f = New IO.FileInfo(reportFileName)
24   modified = f.LastWriteTime
25   lblSavedReport.Text = "(Last Refreshed " & modified.ToShortDateString() & " " & modified.ToShortTimeString() & ")"
26   BtnRefreshReport.Visible = True
27   lblSavedReport.Visible = True
28   
30   DataGrid2.DataSource = ds
31   DataGrid2.DataBind()
32   
33   conAUR.Close()
34       
35   Finish = Timer    ' Set end time
36   TotalTime = Finish - Start    ' Calculate total time
37   sMsg = "Tool took " & Format(TotalTime, "###0.00") & " seconds to display " & lblResults.Text & ". More info: " & lblTYCorr.Text
38   Call Write2Log(sMsg)

There are any number of timeouts that could be triggered while you are trying to run something this long inside a web application.
Frankly - it seems like a task that is not really suitable to be run this way.

Not sure where to start but first off - what sort of SQL query are you running in this world of processor power that 
takes 2 hours on a well optimised database.

The relevance dataset would have to be HUGE - is this an UPDATE query or just a SELECT.
If its just a SELECT then it feels like you need to look seriously at the database to take advantage of query index optimisation.

As for whats timing out -  can you put a try catch around the offending task and tell us what error is firing since there will be one firing somewhere.

At a guess it could be the worker thread getting killed because it seems to be dead - and the OS thinks it needs to do a salvage job.

If you really do need to run this sort of query inside a web application - is there any way to compartmentalise the query into sections
or blocks - maybe passing it thru a couple of temporary tables.

I also noticed that you are using a dataset to handle the returned data
 - is it possible that the result set is very large and overflowing the memory allocation of the application pool.

As a thought - you could possibly try using a datareader in your code instead of a dataset to get a better idea of where memory is being used etc.

Westa

I can only guess that something has been fixed in 2.0, and now behaves differently than 1.1. It could be memory allocation, garbage collection, or even a different setting in the framework or IIS.

Typically how I would handle this would be to run SQL profiler to see what is being submitted and how the results could be improved. Sometimes adding an index or two may improve performance by an order of magnitude or more, which in your case would be a few mintutes instead of hours.

I will bet that this can be significantly improved through reducing the data domain, changes to the data schema, indices, temp tables or a combination of such.

I would really need to see the actual data schema and the SQL request. Then I would spend one morning and make the web application respond in real time like it should.