Stored procedure runs much slower than query

Question

I have a stored procedure which creates a 2x2 table for an odds ratio. A rudimentary odds ratio table looks like:

EDIT - this query finally did finish and it did return the correct answer after two minutes and 32 separate calls to the function. I can't see why this is running recursively, any ideas, SO?

A - only records that satisfy both thing 1 and thing 2 go here
B - only records that satisfy thing 1 (people with thing 2 CANNOT go here)
C - only records that satisfy thing 2 (people with thing 1 CANNOT go here)
D - people with thing 1 OR thing 2 cannot go here

All of the cells in the table will be integers representing a population of people.

I was trying to learn some new syntax and decided to use intersect and except. I wanted to make the thing 1 and thing 2s variables so I put the below query into a stored procedure.

CREATE PROC Findoddsratio (@diag1 NVARCHAR(5), 
                           @diag2 NVARCHAR(5)) 
AS 
    IF Object_id('tempdb..#temp') IS NOT NULL 
      DROP TABLE #temp 

    CREATE TABLE #temp 
      ( 
         squarenumber CHAR(1), 
         counts       FLOAT 
      ) 

    INSERT INTO #temp 
                (squarenumber, 
                 counts) 
    SELECT * 
    FROM   ( 
           --both + 
           SELECT 'a'                                    AS squareNumber, 
                  Cast(Count(DISTINCT x.counts)AS FLOAT) AS counts 
           FROM   (SELECT DISTINCT ic.patid AS counts 
                   FROM   icdclm AS ic 
                   WHERE  ic.icd LIKE @diag1 
                   INTERSECT 
                   SELECT DISTINCT ic.patid AS counts 
                   FROM   icdclm AS ic 
                   WHERE  ic.icd LIKE @diag2)x 
           UNION 
           --only 1+ 
           SELECT 'b', 
                  Count(DISTINCT x.counts) 
           FROM   (SELECT DISTINCT ic.patid AS counts 
                   FROM   icdclm AS ic 
                   WHERE  ic.icd LIKE @diag1 
                   EXCEPT 
                   SELECT DISTINCT ic.patid AS counts 
                   FROM   icdclm AS ic 
                   WHERE  ic.icd LIKE @diag2)AS x 
           UNION 
           --only 2+ 
           SELECT 'c', 
                  Count(DISTINCT x.counts) 
           FROM   (SELECT DISTINCT ic.patid AS counts 
                   FROM   icdclm AS ic 
                   WHERE  ic.icd LIKE @diag2 
                   EXCEPT 
                   SELECT DISTINCT ic.patid AS counts 
                   FROM   icdclm AS ic 
                   WHERE  ic.icd LIKE @diag1)AS x 
            UNION 
            --both - 
            SELECT 'd', 
                   Count(DISTINCT x.counts) 
            FROM   (SELECT DISTINCT ic.patid AS counts 
                    FROM   icdclm AS ic 
                    EXCEPT 
                    SELECT DISTINCT ic.patid AS counts 
                    FROM   icdclm AS ic 
                    WHERE  ic.icd LIKE @diag2 
                    EXCEPT 
                    SELECT DISTINCT ic.patid AS counts 
                    FROM   icdclm AS ic 
                    WHERE  ic.icd LIKE @diag1) AS x)y 

    --i used a pivot table to make the math work out easier 
    SELECT Round(Cast(( a * d ) / ( b * c ) AS FLOAT), 2) AS OddsRatio 
    FROM   (SELECT [a], 
                   [b], 
                   [c], 
                   [d] 
            FROM   (SELECT [squarenumber], 
                           [counts] 
                    FROM   #temp) p 
                   PIVOT ( Sum(counts) 
                         FOR [squarenumber] IN ([a], 
                                                [b], 
                                                [c], 
                                                [d]) ) AS pvt)t 

ICDCLM is a table with a structure like patid=int, icd=varchar(5)

There are ~ one million rows in ICDCLM. When I run this query without making it a stored procedure, it runs in seconds. If I try to exec FindsOddsRation 'thing1%','thing2%'. It runs and runs, but never returns anything (> 2 minutes). What could be the difference in the stored procedure taking so long? SQL Server 2008 R2 fiddle here

Answer 1

Before changing the procedure, be sure that the statistics are up to date. Also, check the actual execution plan (.xml doc) of the stored procedure and look for the value of the parameters used to create the plan. Also compare the profiling for the parameters that were sniffed and the actual parameters, if the distribution of values or whatever you are pulling is way too different for those sets, then you can talk about parameter sniffing.

After updating statistics and recompiling the stored procedure, check if the problem continues. You can play with the same set of parameters (the set that was sniffed and the one giving the problem).

Answer 2

If you're running the same exact SQL as the stored procedure and the times are that different, your stored procedure is probably relying on metadata that is out of date. Try updating statistics or recompiling the stored procedure.