Sql-Server

將標量函式轉換為 TVF 函式以進行並行執行 - 仍在串列模式下執行

  • April 19, 2019

我的一個查詢在發布後以串列執行模式執行，我注意到在從應用程序生成的 LINQ to SQL 查詢中引用的視圖中使用了兩個新函式。所以我將這些 SCALAR 函式轉換為 TVF 函式，但查詢仍然以串列模式執行。

早些時候，我在其他一些查詢中進行了 Scalar 到 TVF 的轉換，它解決了強制串列執行的問題。

這是標量函式：

CREATE FUNCTION [dbo].[FindEventReviewDueDate]
(
      @EventNumber VARCHAR(20),
      @EventID VARCHAR(25),
         @EventIDDate BIT
)

RETURNS DateTime
AS
BEGIN

DECLARE @CurrentEventStatus VARCHAR(20)
DECLARE @EventDateTime DateTime
DECLARE @ReviewDueDate DateTime


SELECT @CurrentEventStatus = (SELECT cis.EventStatus
                                FROM CurrentEventStatus cis 
                                INNER JOIN Event1 r WITH (NOLOCK) ON (cis.Event1Id = r.Id)
                                WHERE (r.EventNumber = @EventNumber) AND r.EventID = @EventID)

SELECT @EventDateTime = (SELECT EventDateTime FROM Event1 r 
                         WHERE (r.EventNumber = @EventNumber) AND r.EventID = @EventID)

IF @CurrentEventStatus IN ('0','6') AND EventIDDate = 1
BEGIN

      SET @ReviewDueDate = DATEADD(DAY, 30, @EventDateTime)

      WHILE @ReviewDueDate < getdate() 
            SET @ReviewDueDate = DATEADD(DAY, 30, @ReviewDueDate)

      DECLARE @EventDateJournalDate DateTime

      SELECT @EventDateJournalDate = (SELECT TOP 1 ij.Date
                                      FROM EventPage_EventJournal ij 
                                      INNER JOIN EventJournalPages p ON ij.PageId = p.Id 
                                      INNER JOIN Journal f ON p.FormId = f.Id 
                                      INNER JOIN Event1 r WITH (NOLOCK) ON (f.Event1Id = r.Id)
                                      WHERE (r.EventNumber = @EventNumber AND r.EventID = @EventID) AND ij.ReviewType = 'Supervisor Monthly Review' ORDER BY ij.Date DESC)

     IF(DATEADD(DAY, 30, @EventDateTime) < getdate() AND
          (@EventDateJournalDate is null OR DATEADD(DAY, 30, @EventDateJournalDate) < getdate()) AND
             DATEADD(DAY, 14, @ReviewDueDate) > DATEADD(DAY, 30, getdate()))
                 SET @ReviewDueDate = DATEADD(DAY, -30, @ReviewDueDate)
        ELSE IF((@EventDateJournalDate is not null ) AND (DATEADD(DAY, 30, @EventDateJournalDate) >= @ReviewDueDate))
                 SET @ReviewDueDate = DATEADD(DAY, 30, @ReviewDueDate)

END
RETURN @ReviewDueDate

END

這是轉換後的 TVF 函式。

CREATE FUNCTION [dbo].[FindEventReviewDueDate_test]
(
      @EventNumber VARCHAR(20),
      @EventID VARCHAR(25),
         @EventIDDate BIT
)

RETURNS @FunctionResultTableVairable TABLE (
CurrentEventStatus varchar(20),
Event1DateTime DateTime,
ReviewDueDate DateTime
)
AS 
BEGIN

DECLARE @CurrentEventStatus VARCHAR(20)
DECLARE @EventDateTime DateTime
DECLARE @ReviewDueDate DateTime


SELECT @CurrentEventStatus = (SELECT cis.EventStatus
                                FROM CurrentEventStatus cis 
                                INNER JOIN Event1 r WITH (NOLOCK) ON (cis.Event1Id = r.Id)
                                WHERE (r.EventNumber = @EventNumber) AND r.EventID = @EventID)

SELECT @EventDateTime = (SELECT EventDateTime FROM Event1 r 
                         WHERE (r.EventNumber = @EventNumber) AND r.EventID = @EventID)

IF @CurrentEventStatus IN ('0','6') AND EventIDDate = 1
BEGIN

      SET @ReviewDueDate = DATEADD(DAY, 30, @EventDateTime)

      WHILE @ReviewDueDate < getdate() 
            SET @ReviewDueDate = DATEADD(DAY, 30, @ReviewDueDate)

      DECLARE @EventDateJournalDate DateTime

      SELECT @EventDateJournalDate = (SELECT TOP 1 ij.Date
                                      FROM EventPage_EventJournal ij 
                                      INNER JOIN EventJournalPages p ON ij.PageId = p.Id 
                                      INNER JOIN Journal f ON p.FormId = f.Id 
                                      INNER JOIN Event1 r WITH (NOLOCK) ON (f.Event1Id = r.Id)
                                      WHERE (r.EventNumber = @EventNumber AND r.EventID = @EventID) AND ij.ReviewType = 'Supervisor Monthly Review' ORDER BY ij.Date DESC)

     IF(DATEADD(DAY, 30, @EventDateTime) < getdate() AND
          (@EventDateJournalDate is null OR DATEADD(DAY, 30, @EventDateJournalDate) < getdate()) AND
             DATEADD(DAY, 14, @ReviewDueDate) > DATEADD(DAY, 30, getdate()))
                 SET @ReviewDueDate = DATEADD(DAY, -30, @ReviewDueDate)
        ELSE IF((@EventDateJournalDate is not null ) AND (DATEADD(DAY, 30, @EventDateJournalDate) >= @ReviewDueDate))
                 SET @ReviewDueDate = DATEADD(DAY, 30, @ReviewDueDate)
                  insert into @FunctionResultTableVairable
     select @CurrentEventStatus,@EventDateTime,@ReviewDueDate          

END
return;
END

GO

我的 TVF 函式的實現有什麼問題會阻止查詢以並行模式執行。

我在查詢中使用了 TVF 函式，如下所示；

select ReviewDueDate from dbo.FunctionResultTableVairable('a','b','c')

我使用視圖的實際查詢非常複雜，如果我在視圖中註釋掉函式部分並在執行時，查詢將並行執行。所以它是強制查詢並行執行的函式。

我的實際查詢採用以下格式。

select 
dv.column1,
dv.column2,
---------
---------
--------
(select ReviewDueDate from dbo.FunctionResultTableVairable('a','b','c')) AS 'Columnx'
from
DemoView dv
Where 
condition1
conditon 2

任何幫助表示讚賞。

是否可以將我的標量函式轉換為內聯 TVF？

是的。像下面這樣的東西會做到這一點。

它仍然非常龐大，如果執行相關可能會非常低效。正如 Aaron 在評論中指出的那樣，您使用常量值呼叫它，因此希望查詢計劃反映這一點並且只執行一次。

CREATE FUNCTION [dbo].[FindEventReviewDueDateInline] (@EventNumber VARCHAR(20),
                                                     @EventID     VARCHAR(25),
                                                     @EventIDDate BIT)
RETURNS TABLE
AS
   RETURN
     WITH X
          AS (SELECT cis.EventStatus AS CurrentEventStatus,
                     r.EventDateTime
              FROM   CurrentEventStatus cis
                     INNER JOIN Event1 r
                             ON cis.Event1Id = r.Id
              WHERE  r.EventNumber = @EventNumber
                     AND r.EventID = @EventID
                     AND cis.EventStatus IN ( '0', '6' )
                     AND @EventIDDate = 1)
     SELECT X.CurrentEventStatus,
            X.EventDateTime,
            CA4.ReviewDueDate
     FROM   X
            --SET @ReviewDueDate = DATEADD(DAY, 30, @EventDateTime)
            CROSS APPLY(VALUES(DATEADD(DAY, 30, X.EventDateTime))) CA1(ReviewDueDate)
            -- WHILE @ReviewDueDate < getdate() 
            --       SET @ReviewDueDate = DATEADD(DAY, 30, @ReviewDueDate)
            CROSS APPLY(VALUES( IIF(CA1.ReviewDueDate >= GETDATE(), CA1.ReviewDueDate, DATEADD(DAY, 30 * CEILING(( IIF(CAST(GETDATE() AS TIME) > CAST(CA1.ReviewDueDate AS TIME), 1, 0)
                                                                                                          + DATEDIFF(DAY, CA1.ReviewDueDate, GETDATE()) ) / 30.0), CA1.ReviewDueDate)))) CA2(ReviewDueDate)
            --SELECT @EventDateJournalDate = ....
            CROSS APPLY(SELECT TOP 1 ij.Date
                        FROM   EventPage_EventJournal ij
                               INNER JOIN EventJournalPages p
                                       ON ij.PageId = p.Id
                               INNER JOIN Journal f
                                       ON p.FormId = f.Id
                               INNER JOIN Event1 r WITH (NOLOCK)
                                       ON ( f.Event1Id = r.Id )
                        WHERE  ( r.EventNumber = @EventNumber
                                 AND r.EventID = @EventID )
                               AND ij.ReviewType = 'Supervisor Monthly Review'
                        ORDER  BY ij.Date DESC) CA3(EventDateJournalDate)
            -- IF(DATEADD(DAY, 30, @EventDateTime) < getdate()
            CROSS APPLY(VALUES ( CASE
                         WHEN ( DATEADD(DAY, 30, X.EventDateTime) < GETDATE()
                                AND ( CA3.EventDateJournalDate IS NULL
                                       OR DATEADD(DAY, 30, CA3.EventDateJournalDate) < GETDATE() )
                                AND DATEADD(DAY, 14, CA2.ReviewDueDate) > DATEADD(DAY, 30, GETDATE()) )
                           THEN DATEADD(DAY, -30, CA2.ReviewDueDate)
                         WHEN( ( CA3.EventDateJournalDate IS NOT NULL )
                               AND ( DATEADD(DAY, 30, CA3.EventDateJournalDate) >= CA2.ReviewDueDate ) )
                           THEN DATEADD(DAY, 30, CA2.ReviewDueDate)
                         ELSE CA2.ReviewDueDate
                       END )) CA4(ReviewDueDate);

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

相關問答

Sql-Server

索引尋求 OR 運算符的成本

  • June 27, 2018
檢視問答
Sql-Server

為什麼添加 TOP 1 會顯著降低性能？

  • December 29, 2021
檢視問答
Sql-Server

SQL Server：記憶體壓力、使用者連接數和 CPU 使用率

  • March 6, 2021
檢視問答
Sql-Server

查詢計劃中有關記憶體“過度授予”的警告 - 如何找出導致它的原因？

  • January 26, 2021
檢視問答
Sql-Server-2008

處理 CXPACKET 等待 - 設置並行成本門檻值

  • May 7, 2020
檢視問答
Sql-Server

即使通過使其以並行模式執行來改進查詢，查詢性能也會更差

  • March 30, 2020
檢視問答