title: Function with recursive CTE seems to be very slow
body: <p>I am currently working on a function in which I use a recursive CTE, but it seems that have poor performance. I need this to be in function (so no temp tables) so I can easily use it within stored procedures.</p>
  
  <p>Here is the code:</p>
  
  <pre><code>CREATE FUNCTION [dbo].[Web_GetDailyLoadListUDF]
  (
      @CustomerID INT
      , @StartDate DATETIME
      , @Days INT
      , @IncludeChildren BIT
  )
  RETURNS @TableOfValues TABLE 
  (  
      RowID SMALLINT IDENTITY(1,1)
      , DailyLoadCount INT
      , DailyLoadDate VARCHAR(6)
      , FullDate DATETIME
  ) 
  AS
  BEGIN
      DECLARE @MaxDate DATETIME;
      SET @MaxDate = DATEADD(dd, @Days * -1.7, DATEDIFF(dd, 0, @StartDate));
  
      WITH DateCTE AS
      (
          SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, @StartDate)) AS DateValue
          UNION ALL
          SELECT DATEADD(DAY, -1, DateValue)
          FROM DateCTE
          WHERE DATEADD(DAY, -1, DateValue) &gt; @MaxDate
      )
      INSERT INTO @TableOfValues
      SELECT * FROM
      (
          SELECT TOP (@Days)
              (
                  SELECT COUNT(*)
                  FROM dbo.[Load] l WITH (NOLOCK)
                  JOIN dbo.LoadCustomer lc WITH (NOLOCK)
                      ON lc.LoadID = l.ID
                  JOIN dbo.Customer c WITH (NOLOCK)
                      ON c.ID = lc.CustomerID
                  WHERE DATEADD(dd, 0, DATEDIFF(dd, 0, l.LoadDate)) = dct.DateValue
                      AND l.StateType = 1
                      AND lc.Main = 1
                      AND (c.ID = @CustomerID OR (@IncludeChildren = 1 AND c.ParentCustomerID = @CustomerID))
              ) AS DailyLoadCount
              , CONVERT(VARCHAR(6), dct.DateValue, 107) AS DailyLoadDate
              , dct.DateValue
          FROM DateCTE dct
          WHERE
              DATEPART(DW, dct.DateValue) NOT IN (1, 7)
              AND dct.DateValue NOT IN
              (
                  SELECT HolidayDate FROM Holiday
              )
          ORDER BY dct.DateValue DESC
      ) AS S
      ORDER BY s.DateValue ASC
  
      RETURN
  END
  </code></pre>
  
  <hr>
  
  <p>What this SQL is supposed to retrieve is the number of loads per day, for the past @Days that are business days (no weekends/holidays).</p>
  
  <p>I basically just need some help optimizing this so that it doesn't run so slow. (Takes up to 20 seconds per customer, and this will be called over thousands).</p>