Slow as a stored procedure, fast as a query - not parameter sniffing

Question

I have a confused problem. I have a SP that include a query with few JOINs (2 of them are LEFT JOINs). The SP take 10 seconds, and if I execute the query as a query - it's take 200 ms...

This is not parameter sniffing issue: even if I clean the cache and execute the SP with only one set of parameters - still slow. I also try to execute the SP with recompile, and to add option (recompile) in the query in the SP - still slow.

I try those links:

I have to mention also that:

2 parameters of the SP are table type.
There is no dynamic SQL in the SP.

So - what is the story here???

This is the SP code:

CREATE PROCEDURE [dbo].[spr_spr]
    @ListOfIDs dbo.tt_IDsList READONLY, -- (one column - ID)
    @ListOfTwoIDs dbo.tt_TwoIDsRelationList READONLY, -- (two columns - FirstID, SecondID)
    @SomeID int = NULL
AS

IF @SomeID IS NULL 
    SELECT  ..... , 
            cast ( (CASE WHEN le.ID IS NOT NULL THEN 1 ELSE 0 END) as bit) as HasLinkedID 
    FROM        @ListOfIDs ids
    JOIN        dbo.tbl1 ra             ON  ids.ID = ra.RR_RowID                                         
    JOIN        dbo.tbl2 rr             ON  ra.RR_RowID = rr.RowID
    JOIN        dbo.tbl3 res            ON  res.tbl3ID = ra.tbl3ID
    JOIN        dbo.tbl4 cal            ON  cal.ObjectID = rr.ObjectID 
    JOIN        @ListOfTwoIDs IdsRel    ON  cal.FirstID = IdsRel.FirstID
                                        AND res.SecondID = IdsRel.SecondID
    LEFT JOIN   dbo.tbl5 p              ON  ra.tbl5ID = p.tbl5ID 
    LEFT JOIN   dbo.tbl6 le             ON  le.tbl6ID = ra.tbl6ID

ELSE
    .... -- same query with one change

agreed with @jyparask, best is provide with your code for us to consider =) — Low Chee Mun, Aug 21 '13 at 07:23
Accepted answer from the first link you provided should do the trick. Just copy your input parameters into local variables and work with them instead. — Nenad Zivkovic, Aug 21 '13 at 07:38
What do you mean by "execute the query as a query"? Can you show the code that runs fast? — Martin Smith, Aug 21 '13 at 07:49
The code that runs fast is the same code that run as a query, not from the SP execution. — itaigitt, Aug 21 '13 at 08:20
@itaigitt - So the only difference is variables vs parameters? — Martin Smith, Aug 21 '13 at 09:06
When was the last time the SP was recompiled? If it's old, it may not be using the latest indexes. — Brian, Aug 21 '13 at 12:56

score 0 · Answer 1 · answered Aug 26 '13 at 08:24

This was the solution:

CREATE TABLE #ListOfIDs (ID int)
insert into #ListOfIDs (ID) select ID from @ListOfIDs 

CREATE TABLE #ListOfTwoIDs (FirstID int, SecondID int)
insert into #ListOfTwoIDs (FirstID, SecondID) select FirstID, SecondID from @ListOfTwoIDs

I guess that the issue was with the table-types parameters, but I still don't understand why and what...

Slow as a stored procedure, fast as a query - not parameter sniffing

1 Answers1