to gain SQL Server performance can I use something like set-based SQL with INSERT to temp-table?

Question

Using SQL Server 2008 R2

I have a 20 second Query from this Function, which is using a Cursor, While-Loop, AND calls another function.

Based on the code shown - what would be the best way to improve the performance of this bottleneck?

USE [DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[ufnGetForUser] (@UserName VARCHAR(100))
RETURNS @User TABLE (
    ID INT
    ,Name VARCHAR(40)
    ,ParentID INT
    ,CompanyID INT
    ,CompanyName VARCHAR(100)
    ,CompanyDisplayName VARCHAR(100)
    ,AnotherID INT
    ,AnotherName VARCHAR(50)
    )
AS
BEGIN
    DECLARE @List TABLE (
        ID INT
        ,Name VARCHAR(40)
        ,ParentID INT
        )

    DECLARE List CURSOR
    FOR
    SELECT ID
    FROM YourTable WITH (NOLOCK)
    WHERE CompanyID IN (
            SELECT companyid
            FROM UserToCompany
            WHERE UserName = @UserName
            )

    DECLARE @ID AS INT

    OPEN List

    FETCH List
    INTO @id

    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        INSERT INTO @List
        SELECT *
        FROM ufnIncludeWithChildren(@ID)

        FETCH List
        INTO @ID
    END

    CLOSE List

    DEALLOCATE List

    INSERT INTO @User
    SELECT DISTINCT a.ID
        ,a.Name
        ,a.ParentID
        ,c.CompanyID
        ,c.CompanyName
        ,c.CompanyDisplayName
        ,d.AnotherID
        ,d.AnotherName
    FROM @List a
    JOIN YourTable b ON a.ID = b.ID
    JOIN CompanyInfo c ON b.CompanyID = c.CompanyID
    JOIN Anothers d ON b.AnotherID = d.AnotherID

    RETURN
END

Answer 1

You should be able to use a single select.

USE [DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[ufnGetForUser] (@UserName VARCHAR(100))
RETURNS @User TABLE (
    ID INT
    ,Name VARCHAR(40)
    ,ParentID INT
    ,CompanyID INT
    ,CompanyName VARCHAR(100)
    ,CompanyDisplayName VARCHAR(100)
    ,AnotherID INT
    ,AnotherName VARCHAR(50)
    )
AS
BEGIN
    INSERT INTO @User
        SELECT DISTINCT a.ID
            ,a.Name
            ,a.ParentID
            ,c.CompanyID
            ,c.CompanyName
            ,c.CompanyDisplayName
            ,d.AnotherID
            ,d.AnotherName
        FROM UserToCompany u
        JOIN CompanyInfo c ON u.CompanyID = c.CompanyID 
        CROSS APPLY ufnIncludeWithChildren(yt.ID) a
        JOIN YourTable b ON a.ID = b.ID
        JOIN Anothers d ON b.AnotherID = d.AnotherID
        WHERE u.UserName = @UserName
    RETURN
END

Answer 2

On first glance, we can definitely eliminate the CURSOR. While replacing it with a WHILE loop as in my comment is one solution, we can go a step further and eliminate the loop completely by using an APPLY clause to call the function directly against each value in the row.

ALTER FUNCTION [dbo].[ufnGetForUser] (@UserName VARCHAR(100))
RETURNS @User TABLE (
    ID INT
    ,Name VARCHAR(40)
    ,ParentID INT
    ,CompanyID INT
    ,CompanyName VARCHAR(100)
    ,CompanyDisplayName VARCHAR(100)
    ,AnotherID INT
    ,AnotherName VARCHAR(50)
    )
AS
BEGIN
    DECLARE @List TABLE (
        ID INT
        ,Name VARCHAR(40)
        ,ParentID INT
        )

    INSERT INTO @List
    SELECT sfn.*
    FROM YourTable yt WITH (NOLOCK)
    CROSS APPLY ufnIncludeWithChildren(yt.ID) sfn
    WHERE yt.CompanyID IN (
            SELECT companyid
            FROM UserToCompany
            WHERE UserName = @UserName
    )

    INSERT INTO @User
    SELECT DISTINCT a.ID
        ,a.Name
        ,a.ParentID
        ,c.CompanyID
        ,c.CompanyName
        ,c.CompanyDisplayName
        ,d.AnotherID
        ,d.AnotherName
    FROM @List a
    JOIN  b ON a.ID = b.ID
    JOIN CompanyInfo c ON b.CompanyID = c.CompanyID
    JOIN Anothers d ON b.AnotherID = d.AnotherID

    RETURN
END

This code is not tested but should work. Please let me know if you have any issues.

Also, in your question you omitted the table name from the first query (where you had the cursor), so I put in YourTable in there as a placeholder.