January 30, 2009 at 2:19 pm
Alright,
So I am just trying to figure this out as it is irritating the hell out of me.
Running SQL Server 2005 Standard Edition
.NET front end web application
We have a stored procedure, with some pretty complex logic, that takes roughly 30 seconds to run on the database from SSMS.
When the .NET application calls the procedure it times out. I run the same procedure call that the .NET runs and I still get a 30 second run time.
After looking at the obvious stuff, we start trying the odd stuff. Without boring everyone, we move one section of sql code to a temp table. The sql code was a sub query for an inner join. We dropped all the information from this into the temp table and litterally did an
INNER JOIN (Select * from #temp) as TMP on X.Y = T.Y
Same run time and information in the database. Now the .NET front end can pull it up without issues.
Can someone PLEASE explain to me, WTH is going on here. This makes no sense to me. The .NET should simply call the procedure and then wait for the results to be returned. Move code around inside of a store procedure should not affect this. An yet, it has in this instance, and we have run into another instance on another database on a different server.
Frustrated Fraggle:crazy:
January 30, 2009 at 2:49 pm
The default .NET Timout value is 30 seconds, if you explicily assign the value you can extend it.
Check this out, and let us know if any of it worked
January 30, 2009 at 3:02 pm
The web page was set at 5 minutes. SQL Server timeout are set to infinity.
the result set returned is less than 200 rows and when an actual execution plan is run I don't see more than 30-40k row being returned for any section of the query. There are some RID and KeyLookups that I am not fond of, but even with these, I am still returning it in less than 30 seconds both before and after the move into the temporary tables.
Fraggle.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply