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
