Executing Stored Proc vs Sql

Question

I have a weird scenario where when I execute a stored the query takes 30 mins +. If I take the body of the stored proc and convert the parameters to variables it takes 1:30.

I'm running this in SSMS.

The stored proc does a select with a few joins. Something like this

CREATE OR ALTER PROCEDURE [report].[MyReport]
    @DataPackageId INT, 
    @DefaultAsOf DATETIME2(7),
    @DefaultDealAsOf DATETIME2(7)
    
AS
    SELECT 
        
         *

    FROM
        reporthelper.FuncA(@DataPackageId,@DefaultAsOf, @DefaultDealAsOf) FuncA
    LEFT JOIN reporthelper.FuncB(@DataPackageId,@DefaultAsOf, @DefaultDealAsOf) FuncB
        ON FuncA.Field1 = FuncB.Field2
        AND FuncA.Field1 = FuncB.Field2
    --More Joins
GO

Why would the executing times vary that much between the 2 scenarios if its essentially the same SQL executing?

Sounds like [parameter sniffing](https://www.red-gate.com/simple-talk/sql/t-sql-programming/parameter-sniffing/) — Thom A, Jun 26 '20 at 08:30
Correct. Param sniffing. See my answer from years ago: https://stackoverflow.com/a/3213627/269512 — Jacques Bosch, Jun 26 '20 at 09:21

score 2 · Answer 1 · answered Jun 26 '20 at 09:19

Almost certainly 'parameter sniffing', and this is how I usually fix it:

CREATE OR ALTER PROCEDURE [report].[MyReport]
    @DataPackageId INT, 
    @DefaultAsOf DATETIME2(7),
    @DefaultDealAsOf DATETIME2(7)
    
AS
    DECLARE @xDataPackageId INT
    DECLARE @xDefaultAsOf DATETIME2(7)
    DECLARE @xDefaultDealAsOf DATETIME2(7)

    SET @xDataPackageId = @DataPackageId
    SET @xDefaultAsOf = @DefaultAsOf
    SET @xDefaultDealAsOf =@DefaultDealAsOf
    
    SELECT 
        
         *

    FROM
        reporthelper.FuncA(@xDataPackageId,@DefaultAsOf, @xDefaultDealAsOf) FuncA
    LEFT JOIN reporthelper.FuncB(@xDataPackageId,@xDefaultAsOf, @xDefaultDealAsOf) FuncB
        ON FuncA.Field1 = FuncB.Field2
        AND FuncA.Field1 = FuncB.Field2
    --More Joins

score 0 · Answer 2 · answered Jun 26 '20 at 09:57

0

I ended up using the suggested posted in the comments by Larnu: Parameter Sniffing

CREATE PROC [dbo].[DisplayBillingInfo]
...
WITH RECOMPILE
AS

answered Jun 26 '20 at 09:57

Murdock

4,352
3
34
63

Executing Stored Proc vs Sql

2 Answers2