Блог

Использование SQL Server 2008 R2

У меня есть 20-секундный запрос от этой функции, которая использует курсор, цикл While и вызывает другую функцию.

Исходя из показанного кода — каким был бы наилучший способ повысить производительность этого узкого места?

 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
  

Вы должны иметь возможность использовать один 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
  

На первый взгляд, мы определенно можем исключить CURSOR . Хотя замена его WHILE циклом, как в моем комментарии, является одним из решений, мы можем пойти еще дальше и полностью исключить цикл, используя APPLY предложение для вызова функции непосредственно для каждого значения в строке.

 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
  

Этот код не тестировался, но должен работать. Пожалуйста, дайте мне знать, если у вас возникнут какие-либо проблемы.

Кроме того, в вашем вопросе вы опустили имя таблицы из первого запроса (где у вас был курсор), поэтому я вставил YourTable туда в качестве заполнителя.