SQL queries running slow from web

If you have a MSSQL query (stored procedure) that runs OK (fast) in the Query Analyzer or Management Studio but extremely slow when you run them from a web page, then the solution is one of the following (in this order):

– try to update statistics and recompile.
– try rebuilding indexes.
– try a query hint.

For me, recompiling (altering the procedure using WITH RECOMPILE option) always worked. Because SQL can optimize and compile a stored procedure, they run more quickly than the equivalent SQL statements executed from Query Analyzer (or perhaps passed in from a Web page or C# application). You can also run your procedure with the WITH RECOMPILE option:

EXEC procedure WITH RECOMPILE

But be careful when you use this option because sometimes the cost of recompilation can be big. If the procedure is long and have many queries, the recompilation itself could degrade performance.