title: SQL Server - recompilation times based on query variables vs inline constant values
body: <p>Similar questions have been asked earlier, and I did perform my research; e.g. <a href="https://stackoverflow.com/questions/35627172/sql-any-performance-difference-using-constant-values-vs-parameters">here</a>, <a href="https://stackoverflow.com/questions/19133537/ensure-cold-cache-when-running-query/19134216#19134216">here</a>, <a href="https://stackoverflow.com/questions/27564852/why-is-sql-server-using-index-scan-instead-of-index-seek-when-where-clause-conta/27566881#27566881">here</a> and <a href="https://www.sqlshack.com/impact-of-sql-variables-on-performance/" rel="nofollow noreferrer">here</a>.</p>
  <p>However what we saw recently was quite boggling. We have a very complex query for some of our Invoice reconciliation that we were trying to optimise:</p>
  <pre><code>WITH cte1 AS
  (
      SELECT a.* 
      FROM APInvoices a WITH (NOLOCK)
      JOIN Profiles p WITH (NOLOCK) ON a.ProfileNumber = p.ProfileNumber 
                                    AND Round(a.Balance, 2) &lt;&gt; 0 
  ),
  cte2 AS
  (
      SELECT
          a.InvoiceID AS ID, a.OriginalInvoiceDetailID, a.ARInvoiceID, 'S' AS Type, 'I' AS Department, 
          a.APTransactionType, a.InvoiceNumber, a.BranchNumber, a.ProfileNumber, 
          a.InvoiceDate, a.Description, a.PayableAccount AS AccountNumber, a.InvoiceAmount AS InvoiceAmount, a.Balance AS Amount, 
          a.CurrencyCode, ISNULL(a.ExchangeRate, 1) AS ExchangeRate, --Mike: Can we trap for that, i.e. case arinvoiceblaances.exchangerate = Null then 1 else exchangerate? 
          ISNULL(d.TicketNumber, '')  AS TicketNumber,
          a.ProfileName AS FullName, 
          a.CashSelected, a.ChequeSelected, a.OnHold, a.DueDate, 
          Profiles.ProfileType, 
          ISNULL(d.PassengerName, '') AS PassengerName, 
          d.TransactionDate, 
          d.TravelDate, d.ReturnDate, d.VatOnCommission AS VatOnCommission, d.ReservationID, 
          d.ChainCode, d.ProductCode, d.SubmitTo, d.VoucherStatus, 
          d.InvoiceDetailID, d.CommissionAmount, d.GrossAmount, d.GroupCode, d.PstOnCommission, 
          d.TransactionType, d.CityCode, d.TransactionCode
      FROM 
          APInvoices a WITH (NOLOCK)
      JOIN 
          Profiles WITH (NOLOCK) ON a.ProfileNumber = Profiles.ProfileNumber 
      JOIN 
          ARInvoiceDetails d WITH (NOLOCK) ON d.InvoiceID = a.ARInvoiceID 
      WHERE
          d.TransactionType IN (1, 3)
          AND ((d.InvoiceDetailID = a.ARInvoiceDetailID)
               OR
               (a.ARInvoiceDetailID = 0
                AND a.ARInvoiceID = d.InvoiceID
                AND a.TicketNumber &lt;&gt; '''' 
                AND a.TicketNumber IS NOT NULL 
                AND a.TicketNumber = d.TicketNumber
                AND a.CurrencyCode = d.CurrencyCode
                AND (
                      CASE WHEN (LEN(a.ProfileNumber) = 8 AND a.ProfileNumber = d.VendorNumber) OR LEN(a.ProfileNumber) = 6
                          THEN 1
                          ELSE 0 
                      END) = 1
              )
              OR
              (
                  a.ARInvoiceDetailID = 0
                  AND a.ARInvoiceID = d.InvoiceID
                  AND (a.TicketNumber = '''' OR a.TicketNumber IS NULL)
                  AND a.CurrencyCode = d.CurrencyCode 
                  AND (
                      CASE WHEN (LEN(a.ProfileNumber) = 8 AND a.ProfileNumber = d.VendorNumber) OR LEN(a.ProfileNumber) = 6
                          THEN 1
                          ELSE 0
                      END) = 1
              )
          )
      
      UNION
  
      SELECT  a.InvoiceID AS ID, a.OriginalInvoiceDetailID, a.ARInvoiceID, 'S' AS Type, 'I' AS Department,
              a.APTransactionType, a.InvoiceNumber, a.BranchNumber, a.ProfileNumber,
              a.InvoiceDate, a.Description, a.PayableAccount AS AccountNumber, a.InvoiceAmount AS InvoiceAmount, a.Balance AS Amount, 
              a.CurrencyCode, ISNULL(a.ExchangeRate, 1) AS ExchangeRate, --Mike: Can we trap for that, i.e. case arinvoiceblaances.exchangerate = Null then 1 else exchangerate? 
              ISNULL(a.TicketNumber, '')  AS TicketNumber,
              a.ProfileName AS FullName, 
              a.CashSelected, a.ChequeSelected, a.OnHold, 
              a.DueDate, 
                  
              Profiles.ProfileType,
  
              '' AS PassengerName, 
              NULL as TransactionDate, 
              NULL as TravelDate, NULL as ReturnDate, NULL as VatOnCommission, 0 as ReservationID,
              '' as ChainCode, NULL as ProductCode, NULL as SubmitTo, NULL as VoucherStatus,
              NULL as InvoiceDetailID, NULL as CommissionAmount, NULL as GrossAmount, '' as GroupCode, NULL as PstOnCommission, 
  
              NULL as TransactionType,
              '' as CityCode,
              '' as TransactionCode
  
      FROM APInvoices a WITH (NOLOCK)
          JOIN Profiles WITH (NOLOCK) ON a.ProfileNumber = Profiles.ProfileNumber 
      WHERE  a.arinvoiceid = 0
  ) 
  SELECT DISTINCT TOP (2) t2.* 
  FROM cte2 t2
  WHERE  (t2.ProfileType = 2 OR t2.ProfileType = 3) 
       AND ((t2.SubmitTo = 2 AND t2.VoucherStatus = 0) OR t2.SubmitTo &lt;&gt; 2)  AND Len(t2.profilenumber) = 8  AND t2.ProfileType = 2  AND t2.TicketNumber = '12345';
  </code></pre>
  <p>The execution time for the above query was well over 30 seconds (the default Sql timeout in .Net), when it produced hardly 0-2 result rows.</p>
  <p>Trying to optimise the above query, all we accidently did was to introduce variables:</p>
  <pre><code>declare @profilenumberlength varchar(10)
  declare @profileType int
  declare @ticketNum varchar(10)
  set @profilenumberlength = 8
  set @profileType = 2
  set @ticketNum = '12345';
  
  WITH cte1 AS
  (
         SELECT a.*
         FROM APInvoices a WITH (NOLOCK)
               JOIN Profiles p WITH (NOLOCK) ON a.ProfileNumber = p.ProfileNumber
          AND Round(a.Balance, 2) &lt;&gt; 0
  ),
  cte2 AS
  (
         SELECT a.InvoiceID AS ID, a.OriginalInvoiceDetailID, a.ARInvoiceID, 'S' AS Type, 'I' AS Department,
                      a.APTransactionType, a.InvoiceNumber, a.BranchNumber, a.ProfileNumber,
                      a.InvoiceDate, a.Description, a.PayableAccount AS AccountNumber, a.InvoiceAmount AS InvoiceAmount, a.Balance AS Amount,
                      a.CurrencyCode, ISNULL(a.ExchangeRate, 1) AS ExchangeRate, --Mike: Can we trap for that, i.e. case arinvoiceblaances.exchangerate = Null then 1 else exchangerate?
                      ISNULL(d.TicketNumber, '')  AS TicketNumber,
                      a.ProfileName AS FullName,
                      a.CashSelected, a.ChequeSelected, a.OnHold,
                      a.DueDate,
  
                      Profiles.ProfileType,
  
                      Isnull(d.PassengerName, '') AS PassengerName,
                      d.TransactionDate,
                      d.TravelDate, d.ReturnDate, d.VatOnCommission AS VatOnCommission, d.ReservationID,
                      d.ChainCode, d.ProductCode, d.SubmitTo, d.VoucherStatus,
                      d.InvoiceDetailID, d.CommissionAmount, d.GrossAmount, d.GroupCode, d.PstOnCommission,
  
                      d.TransactionType,
                      d.CityCode,
              d.TransactionCode
  
         FROM APInvoices a WITH (NOLOCK)
               JOIN Profiles WITH (NOLOCK) ON a.ProfileNumber = Profiles.ProfileNumber
               JOIN ARInvoiceDetails d WITH (NOLOCK) ON d.InvoiceID = a.ARInvoiceID
  
         WHERE
               d.TransactionType IN (1, 3)
               AND
               (
                      (d.InvoiceDetailID = a.ARInvoiceDetailID)
                      OR
                      (
                             a.ARInvoiceDetailID = 0
                             AND a.ARInvoiceID = d.InvoiceID
                             AND a.TicketNumber &lt;&gt; '''' AND a.TicketNumber IS NOT NULL AND a.TicketNumber = d.TicketNumber
                             AND a.CurrencyCode = d.CurrencyCode
                             AND (
                                   CASE WHEN (LEN(a.ProfileNumber) = 8 AND a.ProfileNumber = d.VendorNumber) OR LEN(a.ProfileNumber) = 6
                                          THEN 1
                                          ELSE 0
                                   END) = 1
                      )
                      OR
                      (
                             a.ARInvoiceDetailID = 0
                             AND a.ARInvoiceID = d.InvoiceID
                             AND (a.TicketNumber = '''' OR a.TicketNumber IS NULL)
                             AND a.CurrencyCode = d.CurrencyCode
                             AND (
                                   CASE WHEN (LEN(a.ProfileNumber) = 8 AND a.ProfileNumber = d.VendorNumber) OR LEN(a.ProfileNumber) = 6
                                          THEN 1
                                          ELSE 0
                                   END) = 1
                      )
               )
  
         UNION
  
         SELECT a.InvoiceID AS ID, a.OriginalInvoiceDetailID, a.ARInvoiceID, 'S' AS Type, 'I' AS Department,
                      a.APTransactionType, a.InvoiceNumber, a.BranchNumber, a.ProfileNumber,
                      a.InvoiceDate, a.Description, a.PayableAccount AS AccountNumber, a.InvoiceAmount AS InvoiceAmount, a.Balance AS Amount,
                      a.CurrencyCode, ISNULL(a.ExchangeRate, 1) AS ExchangeRate, --Mike: Can we trap for that, i.e. case arinvoiceblaances.exchangerate = Null then 1 else exchangerate?
                      ISNULL(a.TicketNumber, '')  AS TicketNumber,
                      a.ProfileName AS FullName,
                      a.CashSelected, a.ChequeSelected, a.OnHold,
                      a.DueDate,
  
                      Profiles.ProfileType,
  
                      '' AS PassengerName,
                      NULL as TransactionDate,
                      NULL as TravelDate, NULL as ReturnDate, NULL as VatOnCommission, 0 as ReservationID,
                      '' as ChainCode, NULL as ProductCode, NULL as SubmitTo, NULL as VoucherStatus,
                      NULL as InvoiceDetailID, NULL as CommissionAmount, NULL as GrossAmount, '' as GroupCode, NULL as PstOnCommission,
  
                      NULL as TransactionType,
                      '' as CityCode,
              '' as TransactionCode
  
         FROM APInvoices a WITH (NOLOCK)
               JOIN Profiles WITH (NOLOCK) ON a.ProfileNumber = Profiles.ProfileNumber
         WHERE  a.arinvoiceid = 0
  )
  
  SELECT DISTINCT TOP (2) t2.*
  FROM cte2 t2
  WHERE  (t2.ProfileType = 2 OR t2.ProfileType = 3)
          AND ((t2.SubmitTo = 2 AND t2.VoucherStatus = 0) OR t2.SubmitTo &lt;&gt; 2)  AND Len(t2.profilenumber) = @profileNumberLength  AND t2.ProfileType = @profileType  AND t2.TicketNumber = @ticketNum;
  </code></pre>
  <p>There is absolutely no change in the query logic itself, just that inline constant values in WHERE clause are replaced by variables declared before-hand. This returned in like &lt; 1 second. SQL Server Management Studio showed an execution time of 00:00:00.</p>
  <p>If I add a &quot;<code>OPTION (RECOMPILE)</code>&quot; hint to the second query above (which was performing way better), leaving the variables intact, the performance degrades to the same as that of the first query.
  The above performance results were consistent over hundreds of executions in a loop for each query variant.</p>
  <p>Now I do understand from the literature SQL Server re-creates the execution plans for inline constant values while cached plans are used if the query contained variables. I also understand in some cases, inline constant values can generate better plans as the Query Optimiser can guess the number of rows better upfront if it has actual values during plan generation.</p>
  <p>What baffles me is the time taken to generate the Execution plan itself. Is it normal for SQL Server to take 30+ seconds in generating an execution plan only? Please note the queries executed on a production strength server, 32 cores, 70 gig RAM (dedicated to SQL Server, there's more RAM for OS and other apps), with CPU utilisation hovering between 0-3% at the time tests were run (during late night hours).</p>
  <p>3 tables in the query contained between 1 - 1.2 million rows each, all other participating tables hardly had a few hundred. The database in question itself is around 10 gigs.</p>