smart developer’s blog

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

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.

Advertisements

Written by smartdev

July 23, 2009 at 11:11 am

Posted in SQL

Tagged with , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: