導致串列計劃的函式。改用什麼？

我的 SQL Server 數據庫中的儲存過程很少。我看到這些儲存過程從它們的執行計劃中以串列模式執行。例如 ：

CREATE PROCEDURE [dbo].[DemoProc]
   @Arg1 VARCHAR(20),
   @Arg2 varchar(9) = NULL
AS
SELECT
   r.Plant_Id AS [Plant_Id],
   @LNumber AS [Incident Number],
   COALESCE(idps.AlternateNumber, '') AS [tblFormNumber],
   CASE l.[UserName]
       WHEN NULL THEN 'OPEN'
       WHEN '' THEN 'UNLOCKED'
       ELSE l.UserName
       END AS UserName,
   l.UserId,
   l.Lock LockCode,
   l.LockTime
FROM
   dbo.tblDetailPages idp WITH (NOLOCK)
       INNER JOIN tblForm f WITH (NOLOCK)
           ON idp.tblFormId = f.Id
       INNER JOIN tblReport r WITH (NOLOCK)
           ON f.tblReportId = r.Id
       LEFT OUTER JOIN dbo.tblDetailsPage_Alternate idps WITH (NOLOCK)
           ON idp.Id = idps.PageId
       LEFT OUTER JOIN [dbo].[Monitor] l  WITH (NOLOCK) 
           ON l.LNumber = SUBSTRING(r.LNumber, 5, 3) + '-' + SUBSTRING(r.LNumber, 8, 7) + '-' + SUBSTRING(r.LNumber, 3, 2)
           AND l.Plant_Id = r.Plant_Id
WHERE
   SUBSTRING(r.LNumber, 5, 3) + '-' + SUBSTRING(r.LNumber, 8, 7) + '-' + SUBSTRING(r.LNumber, 3, 2) = @LNumber AND
   r.Plant_Id LIKE CASE WHEN @Plant_Id = 'ALL' THEN '%' ELSE COALESCE(@Plant_Id, dbo.GetPlant_Id(@LNumber)) END
ORDER BY [tblFormNumber]
OPTION (MAXDOP 6)
GO

在這個儲存過程中，我在 where 條件下有一個函式

WHERE --- 
AND r.Plant_Id LIKE CASE WHEN @Plant_Id = 'ALL' THEN '%' ELSE COALESCE(@Plant_Id, dbo.GetPlant_Id(@LNumber))

dbo.GetPlant_Id(@LNumber) 是一個函式

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[GetPlant_Id]
(
   @LNumber varchar(50)
)
RETURNS VARCHAR(25)
AS
BEGIN
   IF EXISTS (SELECT 1 FROM tblReport r WHERE (SUBSTRING(r.LNumber, 5, 3) + '-' + SUBSTRING(r.LNumber, 8, 7) + '-' + SUBSTRING(r.LNumber, 3, 2)) = @LNumber HAVING COUNT(r.LNumber) > 1)
   BEGIN
       RETURN cast('Duplicate Incident number found :' + @LNumber as int)
   END

   RETURN
       (SELECT TOP 1 AgencyOri FROM tblReport r WHERE SUBSTRING(r.LNumber, 5, 3) + '-' + SUBSTRING(r.LNumber, 8, 7) + '-' + SUBSTRING(r.LNumber, 3, 2) = @LNumber)
END

GO

我如何避免使用函式？我嘗試轉換為內聯函式，但由於它是一個大函式，我不能將其作為內聯函式。為了讓我的問題清楚，

我在問如何避免使用我在問題中顯示的功能，或者有什麼辦法可以改變它以提高性能。？

標量 UDF 僅對呼叫它的查詢強制執行串列計劃。您可以在儲存過程中進行標量 UDF 呼叫，只要查詢不呼叫該函式，該儲存過程也有符合併行條件的查詢。本例中的函式是一個常量，因此您可以將其儲存在局部變數中。我不得不對數據類型進行猜測，但以下內容應該可以滿足您的需求：

CREATE PROCEDURE [dbo].[DemoProc]
   @Arg1 VARCHAR(20),
   @Arg2 varchar(9) = NULL
AS
DECLARE @Plant_Id_Filter VARCHAR(100);

SET @Plant_Id_Filter = CASE WHEN @Plant_Id = 'ALL' THEN '%' ELSE COALESCE(@Plant_Id, dbo.GetPlant_Id(@LNumber)) END;

SELECT
   r.Plant_Id AS [Plant_Id],
   @LNumber AS [Incident Number],
   COALESCE(idps.AlternateNumber, '') AS [tblFormNumber],
   CASE l.[UserName]
       WHEN NULL THEN 'OPEN'
       WHEN '' THEN 'UNLOCKED'
       ELSE l.UserName
       END AS UserName,
   l.UserId,
   l.Lock LockCode,
   l.LockTime
FROM
   dbo.tblDetailPages idp WITH (NOLOCK)
       INNER JOIN tblForm f WITH (NOLOCK)
           ON idp.tblFormId = f.Id
       INNER JOIN tblReport r WITH (NOLOCK)
           ON f.tblReportId = r.Id
       LEFT OUTER JOIN dbo.tblDetailsPage_Alternate idps WITH (NOLOCK)
           ON idp.Id = idps.PageId
       LEFT OUTER JOIN [dbo].[Monitor] l  WITH (NOLOCK) 
           ON l.LNumber = SUBSTRING(r.LNumber, 5, 3) + '-' + SUBSTRING(r.LNumber, 8, 7) + '-' + SUBSTRING(r.LNumber, 3, 2)
           AND l.Plant_Id = r.Plant_Id
WHERE
   SUBSTRING(r.LNumber, 5, 3) + '-' + SUBSTRING(r.LNumber, 8, 7) + '-' + SUBSTRING(r.LNumber, 3, 2) = @LNumber AND
   r.Plant_Id LIKE @Plant_Id_Filter
ORDER BY [tblFormNumber]
OPTION (MAXDOP 6)
GO 

其作者最初在問題中留下的答案：

轉換為 TVF：

   CREATE FUNCTION [dbo].[GetPlant_Id]
    (
        @LNumber varchar(50)
    )
    RETURNS table
    AS
    RETURN 
        SELECT PlantId = 
        (
            CASE
                WHEN EXISTS
                (
                    SELECT 1
                    FROM Report r
                    WHERE
                        (
                            SUBSTRING(r.LNumber, 5, 3) + '-' + 
                            SUBSTRING(r.LNumber, 8, 7) + '-' + 
                            SUBSTRING(r.LNumber, 3, 2)
                        ) = @LNumber
                    HAVING
                        COUNT(r.LNumber) > 1
                ) 
                THEN
                    (CAST('Duplicate Incident number found without agency specified:' + @LNumber AS int))
                ELSE
                (
                    SELECT TOP 1
                            AgencyOri
                    FROM Report r
                    WHERE
                        (
                            SUBSTRING(r.LNumber, 5, 3) + '-' + 
                            SUBSTRING(r.LNumber, 8, 7) + '-' +
                            SUBSTRING(r.LNumber, 3, 2)
                        ) = @LNumber
                )
            END
        );

效果更好。

引用自：https://dba.stackexchange.com/questions/218848