Different SQL behavior with and without a stored procedure call

Question

Running MS SQL Server 2008, if I execute the following query, it runs quite fast (2 seconds or less) and returns 906 rows:

DECLARE @ValueTime    datetime2
DECLARE @PriceUpdTime datetime2
SELECT  @ValueTime = '2014-11-28 23:00:00.000000'
SELECT  @PriceUpdTime = CURRENT_TIMESTAMP
SELECT  *
  FROM dbo.fMyTableFunction(@PriceUpdTime, @ValueTime) AS prices

However, when I move this code into a stored procedure:

CREATE PROCEDURE dbo.MDMTmp
(
    @ValueTime    datetime2 = NULL,
    @PriceUpdTime datetime2 = NULL
)
AS
BEGIN
    SET NOCOUNT ON;

    IF @PriceUpdTime IS NULL
        SELECT @PriceUpdTime = CURRENT_TIMESTAMP

    SELECT *
      FROM dbo.fMyTableFunction(@PriceUpdTime, @ValueTime) AS prices
END

the call

EXEC dbo.MDMTmp '2014-11-28 23:00:00.000000', NULL

runs much slower (takes forever - I stopped waiting after 30 minutes).

While experimenting, I just placed the code without parameters into a stored procedure like this

CREATE PROCEDURE dbo.MDMTmpVars AS
BEGIN
    DECLARE @ValueTime    datetime2
    DECLARE @PriceUpdTime datetime2

    SELECT  @ValueTime = '2014-11-28 23:00:00.000000'
    SELECT  @PriceUpdTime = CURRENT_TIMESTAMP

    SELECT  *
      FROM dbo.fMyTableFunction(@PriceUpdTime, @ValueTime) AS prices
END

and then executed

EXEC dbo.MDMTmpVars

which worked like a charm – same 906 rows in 1 second. Any idea what could be wrong and how to make the original stored procedure work?

UPDATE If I only pass one of the 2 parameters like this

ALTER PROCEDURE dbo.MDMTmp
(
    @ValueTime    datetime2 = NULL
)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @PriceUpdTime datetime2
    SELECT  @PriceUpdTime = CURRENT_TIMESTAMP

    IF @PriceUpdTime IS NULL
        SELECT @PriceUpdTime = CURRENT_TIMESTAMP

    SELECT *
      FROM dbo.fBondTickerValueAtRunValueDTs(@PriceUpdTime, @ValueTime) AS prices
END

and then execute

EXEC dbo.MDMTmp '2014-11-28 23:00:00.000000'

it works ok, but if I make PriceUpdTime a parameter instead, it does not work, either if I pass NULL or today's date as a string.

UPDATE 2 Here is what worked also:

ALTER PROCEDURE dbo.MDMTmp
(
    @ValueTimeIn    datetime2 = NULL,
    @PriceUpdTimeIn datetime2 = NULL
)
AS
BEGIN
    SET NOCOUNT ON;

    IF @PriceUpdTimeIn IS NULL
        SELECT @PriceUpdTimeIn = CURRENT_TIMESTAMP

    DECLARE @ValueTime    datetime2
    DECLARE @PriceUpdTime datetime2
    SELECT  @ValueTime    = @ValueTimeIn
    SELECT  @PriceUpdTime = @PriceUpdTimeIn

    SELECT *
      FROM dbo.fBondTickerValueAtRunValueDTs(@PriceUpdTime, @ValueTime) AS prices
END

and then executing either of

EXEC dbo.MDMTmp '2014-11-28 23:00:00.000000', NULL
EXEC dbo.MDMTmp '2014-11-28 23:00:00.000000', '2015-02-10'

So perhaps is parameter sniffing, how do i fix it?

What's the function code? What's the execution plan? If you are passing in different dates then it could be a parameter sniffing issue. — Taryn, Feb 10 '15 at 17:09
When you were waiting for 30 minutes, did you check to see if your procedure was being blocked by another process? — Dan, Feb 10 '15 at 17:14
@Dan there are no other processes on the db, i am the only user — gt6989b, Feb 10 '15 at 17:15
Just a guess, but maybe the reason is that the function gets the query plan when the procedure is being parsed -- and at that time you don't have the value for PriceUpdTime. Does it work ok if you run the procedure with current date as the parameter (and first run sp_recompile MDMTmp). — James Z, Feb 10 '15 at 17:15
@bluefeet function code is a `SELECT` from a table of prices with 2 million rows, based on these 2 dates. — gt6989b, Feb 10 '15 at 17:16
@JamesZ I did not understand - what would you like me to try? — gt6989b, Feb 10 '15 at 17:16
Could be *param sniffing*, try declaring two new params based on whats being passed in and use these, e.g. `DECLARE @ValueTimeInternal datetime2 = @ValueTime` — Christian Phillips, Feb 10 '15 at 17:16
@gt6989b Seeing the code would be helpful to see what you are actually doing. Also what's the execution plan for this? — Taryn, Feb 10 '15 at 17:16
run sp_recompile dbo.MDMTmp and then EXEC dbo.MDMTmp '2014-11-28 23:00:00.000000', '20150210 12:00' (or whatever your local time is) — James Z, Feb 10 '15 at 17:18
@christiandev this looks like it worked, please see update 2 — gt6989b, Feb 10 '15 at 17:22
@gt6989b If you are trying to optimize a query, I'd suggest doing some reading about execution plans, etc before continuing. — Taryn, Feb 10 '15 at 17:27
@JamesZ I ran `EXEC sp_recompile MDMTmp` and then `EXEC dbo.MDMTmp '2014-11-28 23:00:00.000000', '2015-02-10'` worked as you suspected -- how can I fix the original stored proc now? — gt6989b, Feb 10 '15 at 17:27
http://stackoverflow.com/questions/7637907/query-extremely-slow-in-code-but-fast-in-ssms/7638059#7638059 — billinkc, Feb 10 '15 at 17:27

score 1 · Answer 1 · edited Feb 10 '15 at 17:37

1

A stored procedure has a stored execution plan. This plan gets created and optimized using the first parameters passed to it. If it's a parameter that can differ wildly, consider using option (OPTIMIZE FOR (@ValueTime UNKNOWN, @PriceUpdTime UNKNOWN)) in your table valued function.

More information here

edited Feb 10 '15 at 17:37

Taryn

242,637
56
362
405

answered Feb 10 '15 at 17:32

Winds of Change

181
1
9

score 1 · Accepted Answer · answered Feb 10 '15 at 17:34

You've hit a thing called parameter sniffing, you'll find a lot of videos / other material for it. Basically, it happens because SQL Server will create the plan for the procedure based on the parameters it gets, and you're parameter is NULL @PriceUpdTime -- and the plan is done based on that.

If for this case, adding "option (optimize for (@PriceUpdTime unknown))" will create the plan with unknown values, instead of NULL, which might be good enough solution for you.

If you know you're going to get several different kind of dates as the parameter, you could think also to add "option (recompile)" into the statement. That way the query plan will be done every time the procedure is called. It will consume some CPU, but if you're table you're accessing is huge and bad plan can cause serious issues this might be worth doing.

where does the option go -- on the stored procedure? or in the body of the table-valued function? if the first case is clear (2nd value can be optimized for NULL) but in the second case -- how do i optimize for `CURRENT_TIMESTAMP`? — gt6989b, Feb 10 '15 at 17:35

Different SQL behavior with and without a stored procedure call

2 Answers2