Function with recursive CTE seems to be very slow

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.

Here is the code:

CREATE FUNCTION [dbo].[Web_GetDailyLoadListUDF]
(
    @CustomerID INT
    , @StartDate DATETIME
    , @Days INT
    , @IncludeChildren BIT
)
RETURNS @TableOfValues TABLE 
(  
    RowID SMALLINT ID开发者_开发百科ENTITY(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) > @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

---

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).

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).

---

Your main problem is just here

WHERE DATEADD(dd, 0, DATEDIFF(dd, 0, l.LoadDate)) = dct.DateValue

It should be

WHERE l.LoadDate >= dct.DateValue
  AND l.LoadDate <  dct.DateValue +1

Create composite indexs on Load(LoadDate, ID) and Load(ID, LoadDate) and drop the one that does not get used in the query plan.

You should show the query plan whenever you are asking questions about performance. To view the query plan, run the query inside the function on its own using variables for the input parameters. From the menu in SSMS, enable the option "Query -> Include Actual Execution Plan"

Since you don't have enough rep to post images, you can reveal the text plan as follows. Provide some sensible parameters in the first SELECT statement.

set showplan_text on;

Then, run the below in TEXT mode, i.e. press Ctrl-T then Ctrl-E.

DECLARE    
      @CustomerID INT
    , @StartDate DATETIME
    , @Days INT
    , @IncludeChildren BIT

SELECT 
      @CustomerID = 1
    , @StartDate = '20110201'
    , @Days = 10
    , @IncludeChildren = 1

DECLARE @TableOfValues TABLE 
(  
    RowID SMALLINT IDENTITY(1,1)
    , DailyLoadCount INT
    , DailyLoadDate VARCHAR(6)
    , FullDate DATETIME
) 

    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) > @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

SELECT * FROM @TableOfValues

Edit the plan into your question

---

You should use an inline UDF instead (right now you are actually using a temp table) See http://msdn.microsoft.com/en-us/library/ms189294.aspx

Or convert it into a view instead.

---

Correlated subqueries run row-by-row, do not use them. Use a join or a join to a derived table instead. You also need to make sure any where clauses can take advantage of the indexing. Search on saragble queries to see what kinds of things cannot use indexes and what can be done to make it use an index.