smart developer’s blog

This is a C# resource library! Free how to’s and best practices…

Posts Tagged ‘mssql

SQL queries running slow from web

leave a comment »

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.

Written by smartdev

July 23, 2009 at 11:11 am

Posted in SQL

Tagged with , ,