SQL Query runs fast in MSSMS but slow as stored procedure

 

It was the weirdest thing.  I had a fairly straight-forward query that when run in my Server Management Studio editor would only take a second or 2 but after building a stored procedure with the same exact query and executing it (either from a .NET page/object or even from a T-SQL call, the stored procedure would take FOREVER to execute.

After a few hours of Google, hair-pulling and searching for a white towel to throw in, I found a solution that works 99% of the time.

 

The cause is an issue called Parameter Scanning and occurs "sometimes" in procedures that pass a parameter in.

The fix is to use internalized parameters - basically creating a new parameter inside of the stored procedure and assigning it a value of the one being passed in.

I don't know the answer as to WHY this happens and why this fix corrects it but all I know is that it does work

 

Examples:

A typical stored procedure with a single passed-parameter might be something like:

ALTER PROC [dbo].[uspREPORTING_Sales]
(@parSALESPERSONID int)
AS

SET NOCOUNT ON

SELECT SUM(GROSS_PROFIT) FROM tblSALESCONTRACTS WHERE SALESPERSON_ID = @parSALESPERSONID

This same procedure rewritten to internalize the parameter would look something like

ALTER PROC [dbo].[uspREPORTING_Sales]
(@parSALESPERSONID int)
AS

SET NOCOUNT ON

DECLARE @intSALESPERSONID int
SET @intSALESPERSONID = @parSALESPERSONID

SELECT SUM(GROSS_PROFIT) FROM tblSALESCONTRACTS WHERE SALESPERSON_ID = @intSALESPERSONID

You're basically referencing the internally-declared parameter instead of the one passed to the procedure. 

Hope this helps those who are having this problem

Avoiding "Timeout Expired" errors from long running SELECT statements

a SQLDataSource object has a default command timeout of 90 seconds. Often you may be tasked with returning large amounts of data or doing some other tedious processing on the database side that takes while. If you exceed this you'll get the pain-in-the-ass runtime exception - Sys.WebForms.PageRequestManagerServerErrorException: Timeout expired.

 

 

To work-around this when all other methods have failed, I increase the command timeout value of the SqlDataSource object via it's SELECTING event as such:

protected void sdsYourSQLDataSource_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
    e.Command.CommandTimeout = 360;
}

The value is set in SECONDS. Hope this helps