title: Why Is My Inline Table UDF so much slower when I use variable parameters rather than constant parameters?
body: <p>I have a table-valued, inline UDF.  I want to filter the results of that UDF to get one particular value.  When I specify the filter using a constant parameter, everything is great and performance is almost instantaneous.  When I specify the filter using a variable parameter, it takes a significantly larger chunk of time, on the order of 500x more logical reads and 20x greater duration.</p>
  
  <p>The execution plan shows that in the variable parameter case the filter is not applied until very late in the process, causing multiple index scans rather than the seeks that are performed in the constant case.</p>
  
  <p>I guess my questions are:  Why, since I'm specifying a single filter parameter that is going to be highly selective against an indexed field, does my performance go into the weeds when that parameter is in a variable?  Is there anything I can do about this?</p>
  
  <p>Does it have something to do with the analytic function in the query?</p>
  
  <p>Here are my queries:</p>
  
  <pre><code>CREATE FUNCTION fn_test()
  RETURNS TABLE
  WITH SCHEMABINDING
  AS
      RETURN
      SELECT DISTINCT GCN_SEQNO, Drug_package_version_ID
      FROM
      (
          SELECT COALESCE(ndctbla.GCN_SEQNO, ndctblb.GCN_SEQNO) AS GCN_SEQNO,
              dpv.Drug_package_version_ID, ROW_NUMBER() OVER (PARTITION BY dpv.Drug_package_version_id ORDER BY 
                  ndctbla.GCN_SEQNO DESC) AS Predicate
          FROM dbo.Drug_Package_Version dpv
              LEFT JOIN dbo.NDC ndctbla ON ndctbla.NDC = dpv.Sp_package_code
              LEFT JOIN dbo.NDC ndctblb ON ndctblb.SPC_NDC = dpv.Sp_package_code
      ) iq
      WHERE Predicate = 1
  GO
  
  GRANT SELECT ON fn_test TO public
  GO
  
  -- very fast
  SELECT GCN_SEQNO
  FROM dbo.fn_test()
  WHERE Drug_package_version_id = 10000
  
  GO
  
  -- comparatively slow
  DECLARE @dpvid int
  SET @dpvid = 10000
  SELECT GCN_SEQNO
  FROM dbo.fn_test()
  WHERE Drug_package_version_id = @dpvid
  </code></pre>