Unidev .NET Technology Blog

by Allan Sieker

Just recently I had the opportunity to investigate a timeout error happening on an ASP.NET web application of a new client.   This web application is a very intense and complex business solution involving tiered hierarchies of entities for companies, resources, processes, and projects.  Definitely not your typical web site.

The source of the timeout was an embedded SQL query consisting of a SQL view joined to another table.  A SqlDataAdapter was being used to fill a DataSet from a SQL Server database.   Very straight forward on the surface, but then I noticed that the a CommandTimeout setting of 60 was being used.  Why would such a simple query be taking more than 60 seconds to execute?

My first was to set a breakpoint just before the execution of the query so I could see what the actual parameter values were.   My second step was to copy/paste the query into an open query tab on SQL Server Management Studio and alter the query to use literal values from the first step.   To my surprise, the query did take a bit of time to execute.  Time to drill down.

I scripted out the SQL view and found that it consisted of a 13 table join where 3 of the joins were to views with joins of their own.  And one of the returning columns was a function having several querys of its own – and a 3 table join.  Time to get another Diet Mt. Dew…

I now understood why the previous developer slapped in a 60 second timeout.  Sure, I could have increased the timeout to 120 seconds and it would probably have solved the problem.  But would it be the professional way of solving the problem?  I thought not.  Besides, I didn’t want to put the client through a compile/rebuild/release cycle for just changing one line of code.  Another important thing to consider is that the users will have to endure this long query several times a day.  I couldn’t take that road.

This query and all of its branches was way too complicated to digest quickly, so I started breaking each branch down and checking to see if the queries were taking advantage of indexes. 

As it turned out, in about an hour I found four places where an index could be added and the yield of that effort took the 60+ second query down to 3 seconds.  The users were  ecstatic.

So the moral of my story is this:  Please do not blindly increase the timeout values without an honest attempt to tweak the query.    The time you take this one time will benefit your users every day.