Is there any SQL improvements that can be made to this SQL Table-valued Function WITH Common Table Expression?

Question

This function is a bit of a bottleneck but is only a small part of a larger query. Are there any changes that can be made to get it to perform better? The UNION is unavoidable, but is the WITH clause the only way? (this is not my original code, I can do pretty basic CRUD operations and am trying to improve performance)

USE [DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[ufnWidgetWithChildren] (@RootWidget INT)
RETURNS @WidgetList TABLE (
    WidgetID INT
    ,WidgetName VARCHAR(50)
    ,ParentWidgetID INT
    )
AS
BEGIN
    WITH [CTE]
    AS (
        SELECT WidgetID
            ,WidgetName
            ,ParentWidgetID
        FROM Widgets c
        WHERE c.WidgetID = @RootWidget

        UNION ALL

        SELECT c.WidgetID
            ,c.WidgetName
            ,c.ParentWidgetID
        FROM [CTE] p
            ,Widgets c
        WHERE c.[ParentWidgetID] = p.[WidgetID]
        )
    INSERT INTO @WidgetList
    SELECT WidgetID
        ,WidgetName
        ,ParentWidgetID
    FROM [CTE]
    ORDER BY WidgetID

    RETURN
END

Did you look at the execution plan to see where the issue is? — Christian Phillips, Jul 07 '14 at 16:29
@christiandev yes - they all say 0% : using 'Actual Execution Plan on this Function alone : the problem I think is maybe broader in scope so the question perhaps cannot be answered as asked. — JoJo, Jul 07 '14 at 16:40

score 2 · Accepted Answer · answered Jul 07 '14 at 16:45

2

Rewrite as an inline table valued function:

 ALTER FUNCTION [dbo].[ufnWidgetWithChildren] (@RootWidget INT) RETURNS
> TABLE AS RETURN (
>     WITH [CTE]
>     AS (
>         SELECT WidgetID
>             ,WidgetName
>             ,ParentWidgetID
>         FROM Widgets c
>         WHERE c.WidgetID = @RootWidget
> 
>         UNION ALL
> 
>         SELECT c.WidgetID
>             ,c.WidgetName
>             ,c.ParentWidgetID
>         FROM [CTE] p
>             ,Widgets c
>         WHERE c.[ParentWidgetID] = p.[WidgetID]
>         )
>     SELECT WidgetID
>         ,WidgetName
>         ,ParentWidgetID
>     FROM [CTE])

answered Jul 07 '14 at 16:45

Mladen Prajdic

15,457
2
43
51

is it currently a Multi-Statement Table-valued function? Do you have a good link for a difference between the two in a practical sense? ty! – JoJo Jul 07 '14 at 17:13
I just found this question: http://stackoverflow.com/questions/2554333/multi-statement-table-valued-function-vs-inline-table-valued-function – JoJo Jul 07 '14 at 17:15

Is there any SQL improvements that can be made to this SQL Table-valued Function WITH Common Table Expression?

1 Answers1