Last post May 03, 2012 02:24 AM by Decker Dong - MSFT
May 01, 2012 05:42 PMfirstname.lastname@example.org|LINK
I have an asp.net (c#) web site that was built a few years ago and no code changes for last couple years.
This web site takes user input (either incremental or all), imports data from one location to another and display the results at the end.
Depending on the option chosen, the web site could run for 2-4 hrs and only user intereaction required is to select the option and clicke "Import" at the beginning (then they usually do something else and come back to the page later to see the result).
Recently, users have reported that they are getting prompted for login about 5 min into the import. Once they provide login and that basically terminates the session.
Further investigation, I found that one sproc it calls via ADO is taking longer than it used to since we have more data to import now. This sproc, sp_refreshdata now takes about 6-7 min and I know while the sproc is running, the web site suddenly prompt
login. So I commented out teh parts that takes majority time from sp_refreshdata and all runs fine. I even updated the sproc so that it deals with subset of data and I get mixed result depending on the amount of records to process. So I am pretty sure this
behaviour is related to the time it takes to run the sproc via ado but can't seem to find a way to solve this.
Here is the code:
using (SqlConnection conn = new SqlConnection(connStr))
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandTimeout = 0; //for unlimit. I changed this to 600 (10 min) but no differences
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_refreshdata";
//log to indicate start
//log to indicate finish
So if I run this method, I see an event entry log that the method is starting. Then just about 5-7 min, I see a login popup. I then see an event entry log that the method is done.
Two other things I did was:
- increased Connection timeout property of IIS to 600 (no difference)
- increased ADO connection object timeout (within connection string) to 600 (no difference).
I think I am running out of optins. Could you please help??
May 01, 2012 05:46 PM|Ken Tucker|LINK
Is the session timeout set high enough in the web.config?
<forms loginUrl="~/Account/LogOn" timeout="2880"/>
May 01, 2012 06:14 PMemail@example.com|LINK
There is no login page and uses integrated windows auth.
May 03, 2012 02:24 AM|Decker Dong - MSFT|LINK
This sproc, sp_refreshdata now takes about 6-7 min and I know while the sproc is running, the web site suddenly prompt login.
Your stored procdure runs about for 6—7 mins？Maybe a bit too long……Please simplify your Stored procdure first。
For very complicated things，you should use Views and then call them through the Stored procdure to simply the complicated logic codes to shorten the execution time period……