All geek questions in one place

Nested loops (Inner Join) cost 83%. Is there any way to rewrite it?

SP is very slow. When I look at the implementation plan, I see that 83% of its cost goes toNested Loops (Inner Join) enter image description here

Is there any way to somehow replace it?

Here is my SP

ALTER PROCEDURE [dbo].[EarningPlazaCommercial] 
    @State      varchar(50),
    @StartDate  datetime,
    @EndDate    datetime,
    @AsOfDate   datetime,
    @ClassCode  nvarchar(max),
    @Coverage   varchar(100)
AS
BEGIN
SET NOCOUNT ON;  
CREATE TABLE #PolicyNumbers  (PolicyNumber varchar(50))
INSERT INTO #PolicyNumbers SELECT  PolicyNumber FROM tblClassCodesPlazaCommercial T1 
                                WHERE NOT EXISTS    (
                                                    SELECT 1 FROM tblClassCodesPlazaCommercial T2  
                                                    WHERE  T1.PolicyNumber = T2.PolicyNumber
                                                    AND ClassCode  IN 
                                                    (SELECT * FROM [dbo].[StringOfStringsToTable](@ClassCode,','))
                                                    )   
CREATE CLUSTERED INDEX IDX_C_PolicyNumbers_PolicyNumber ON #PolicyNumbers(PolicyNumber)

; WITH Earned_to_date AS (
   SELECT Cast(@AsOfDate AS DATE) AS Earned_to_date
), policy_data AS (
    SELECT
            PolicyNumber
,           Cast(PolicyEffectiveDate AS DATE) AS PolicyEffectiveDate
,           Cast(PolicyExpirationDate AS DATE) AS PolicyExpirationDate
,           WrittenPremium
     FROM   PlazaInsuranceWPDataSet pid
     WHERE  NOT EXISTS (SELECT PolicyNumber FROM #PolicyNumbers pn WHERE pn.PolicyNumber = pid.PolicyNumber)
            AND State IN (SELECT * FROM [dbo].[StringOfStringsToTable](@State,',')) 
            AND Coverage    IN (SELECT * FROM [dbo].[StringOfStringsToTable](@Coverage,','))        
)

...

- part Execution Plan enter image description here

enter image description here

Here I add my full request for the stored procedure:

ALTER PROCEDURE [dbo].[EarningPlazaCommercial] 
    @State      varchar(50),
    @StartDate  datetime,
    @EndDate    datetime,
    @AsOfDate   datetime,
    @ClassCode  nvarchar(max),
    @Coverage   varchar(100)
AS
BEGIN
SET NOCOUNT ON;  
CREATE TABLE #PolicyNumbers  (PolicyNumber varchar(50))
INSERT INTO #PolicyNumbers SELECT  PolicyNumber FROM tblClassCodesPlazaCommercial T1 
                                WHERE NOT EXISTS    (
                                                    SELECT 1 FROM tblClassCodesPlazaCommercial T2  
                                                    WHERE  T1.PolicyNumber = T2.PolicyNumber
                                                    AND ClassCode  IN 
                                                    (SELECT * FROM [dbo].[StringOfStringsToTable](@ClassCode,','))
                                                    )   
CREATE CLUSTERED INDEX IDX_C_PolicyNumbers_PolicyNumber ON #PolicyNumbers(PolicyNumber)

; WITH Earned_to_date AS (
   SELECT Cast(@AsOfDate AS DATE) AS Earned_to_date
   --SELECT @AsOfDate AS Earned_to_date
), policy_data AS (
    SELECT
            PolicyNumber
,           Cast(PolicyEffectiveDate AS DATE) AS PolicyEffectiveDate
,           Cast(PolicyExpirationDate AS DATE) AS PolicyExpirationDate
,           WrittenPremium
--,         State
     FROM   PlazaInsuranceWPDataSet pid
     WHERE  NOT EXISTS (SELECT PolicyNumber FROM #PolicyNumbers pn WHERE pn.PolicyNumber = pid.PolicyNumber)
            AND State IN (SELECT * FROM [dbo].[StringOfStringsToTable](@State,',')) 
            AND Coverage    IN (SELECT * FROM [dbo].[StringOfStringsToTable](@Coverage,','))        
) 

, digits AS (
SELECT digit
   FROM (VALUES (0), (1), (2), (3), (4)
,      (5), (6), (7), (8), (9)) AS z2 (digit)
), numbers AS (
SELECT 1000 * d4.digit + 100 * d3.digit + 10 * d2.digit + d1.digit AS number
    FROM digits AS d1
    CROSS JOIN digits AS d2
    CROSS JOIN digits AS d3
    CROSS JOIN digits AS d4
), calendar AS (
SELECT
    DateAdd(month, number, '1753-01-01') AS month_of
,   DateAdd(month, number, '1753-02-01') AS month_after
    FROM numbers
), policy_dates AS (
SELECT
   PolicyNumber
,   CASE
        WHEN month_of < PolicyEffectiveDate THEN PolicyEffectiveDate
        ELSE month_of
    END AS StartRiskMonth
,   CASE
       WHEN PolicyExpirationDate < month_after THEN PolicyExpirationDate
       WHEN Earned_to_date.Earned_to_date < month_after THEN Earned_to_date
       ELSE month_after
    END AS EndRiskMonth
,   DateDiff(day, PolicyEffectiveDate, PolicyExpirationDate) AS policy_days
,   WrittenPremium
    FROM policy_data
    JOIN calendar
        ON (policy_data.PolicyEffectiveDate < calendar.month_after
        AND calendar.month_of < policy_data.PolicyExpirationDate)
    CROSS JOIN Earned_to_date
    WHERE  month_of < Earned_to_date
)
SELECT      --PolicyEffectiveDate,
            --PolicyExpirationDate,
            --PolicyNumber,
            Year(StartRiskMonth) as YearStartRisk, 
            Month(StartRiskMonth) as MonthStartRisk,
            c.YearNum,c.MonthNum,
            convert(varchar(7), StartRiskMonth, 120) as RiskMonth,
            sum(WrittenPremium * DateDiff(day, StartRiskMonth, EndRiskMonth) / policy_days) as EarnedPremium
FROM        tblCalendar  c
LEFT  JOIN policy_dates l ON c.YearNum=Year(l.StartRiskMonth) and c.MonthNum = Month(l.StartRiskMonth) AND l.StartRiskMonth BETWEEN @StartDate AND  @EndDate
WHERE c.YearNum Not IN (2017) --and PolicyNumber = 'PACA1000191-00'
GROUP BY    convert(varchar(7), StartRiskMonth, 120),
            Year(StartRiskMonth) , Month(StartRiskMonth),
            c.YearNum,c.MonthNum--,PolicyNumber--,PolicyEffectiveDate,PolicyExpirationDate
ORDER BY     c.YearNum,c.MonthNum
            --convert(varchar(7), StartRiskMonth, 120)
DROP TABLE #PolicyNumbers
END 
GO

Full actual execution plan from the production line:

https://aligngeneral-my.sharepoint.com/personal/oserdyuk_aligngeneral_com/_layouts/15/guestaccess.aspx?guestaccesstoken=VuiFBK6zMim%2fyIh%2bNrQaOcgrg%2fpIJNKDTStt765cBfQ%3d&docid=1abc31e385da14574a930e99e22f00c7b&rev=1&expiration=2017-01-06T22%3a20%3a34. 000Z

And here is how my TempDB is configured: enter image description here

+4
source share
2

, "". 10000 . , 1950 2033 :

ALTER PROCEDURE [dbo].[EarningPlazaCommercial] 
    @State      varchar(50),
    @StartDate  datetime,
    @EndDate    datetime,
    @AsOfDate   datetime,
    @ClassCode  nvarchar(max),
    @Coverage   varchar(100)
AS
BEGIN
    SET NOCOUNT ON;  

    CREATE TABLE #PolicyNumbers (PolicyNumber varchar(50))

    INSERT INTO #PolicyNumbers 
        SELECT PolicyNumber 
        FROM tblClassCodesPlazaCommercial T1 
        WHERE NOT EXISTS (SELECT 1 
                          FROM tblClassCodesPlazaCommercial T2  
                          WHERE T1.PolicyNumber = T2.PolicyNumber
                            AND ClassCode IN  (SELECT * 
                                               FROM [dbo].[StringOfStringsToTable](@ClassCode,','))
                         )   

CREATE CLUSTERED INDEX IDX_C_PolicyNumbers_PolicyNumber 
ON #PolicyNumbers(PolicyNumber)

DECLARE @Calendar TABLE (
    month_of     DATE, 
    month_after  DATE, 
    PRIMARY KEY (month_of, month_after)
);

WITH digits AS 
(
    SELECT digit
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS z2 (digit)
), numbers AS (
    SELECT 100 * d3.digit + 10 * d2.digit + d1.digit AS number
    FROM digits AS d1
    CROSS JOIN digits AS d2
    CROSS JOIN digits AS d3
), calendar AS 
(
    SELECT
        DateAdd(month, number, '1950-01-01') AS month_of,
        DateAdd(month, number, '1950-02-01') AS month_after
    FROM numbers
)
insert into @Calendar
    select * 
    from calendar

; WITH policy_data AS  
(
    SELECT
        PolicyNumber,
        Cast(PolicyEffectiveDate AS DATE) AS PolicyEffectiveDate,
        Cast(PolicyExpirationDate AS DATE) AS PolicyExpirationDate,
        WrittenPremium
        --,         State
    FROM   
        PlazaInsuranceWPDataSet pid
    WHERE 
        NOT EXISTS (SELECT PolicyNumber FROM #PolicyNumbers pn 
                    WHERE pn.PolicyNumber = pid.PolicyNumber)
        AND State IN (SELECT * FROM [dbo].[StringOfStringsToTable](@State,',')) 
        AND Coverage IN (SELECT * FROM [dbo].[StringOfStringsToTable](@Coverage,','))        
),  policy_dates AS 
(
    SELECT
        PolicyNumber,
        CASE
           WHEN month_of < PolicyEffectiveDate THEN PolicyEffectiveDate
           ELSE month_of
        END AS StartRiskMonth,
        CASE
           WHEN PolicyExpirationDate < month_after THEN PolicyExpirationDate
           WHEN Earned_to_date.Earned_to_date < month_after THEN Earned_to_date
           ELSE month_after
        END AS EndRiskMonth,
        DateDiff(day, PolicyEffectiveDate, PolicyExpirationDate) AS policy_days,
        WrittenPremium
    FROM 
        policy_data
    JOIN 
        @calendar calendar ON (policy_data.PolicyEffectiveDate < calendar.month_after
                           AND calendar.month_of < policy_data.PolicyExpirationDate)
    WHERE  
        month_of < Cast(@AsOfDate AS DATE)
)
SELECT      --PolicyEffectiveDate,
            --PolicyExpirationDate,
            --PolicyNumber,
    Year(StartRiskMonth) as YearStartRisk, 
    Month(StartRiskMonth) as MonthStartRisk,
    c.YearNum, c.MonthNum,
    convert(varchar(7), StartRiskMonth, 120) as RiskMonth,
    sum(WrittenPremium * DateDiff(day, StartRiskMonth, EndRiskMonth) / policy_days) as EarnedPremium
FROM
    tblCalendar  c
LEFT JOIN 
    policy_dates l ON c.YearNum = Year(l.StartRiskMonth) 
                   AND c.MonthNum = Month(l.StartRiskMonth) 
                   AND l.StartRiskMonth BETWEEN @StartDate AND @EndDate
WHERE 
    c.YearNum Not IN (2017) --and PolicyNumber = 'PACA1000191-00'
GROUP BY    
    convert(varchar(7), StartRiskMonth, 120),
    Year(StartRiskMonth), Month(StartRiskMonth),
    c.YearNum, 
    c.MonthNum    --,PolicyNumber
    --,PolicyEffectiveDate,PolicyExpirationDate
ORDER BY     
    c.YearNum,c.MonthNum
    --convert(varchar(7), StartRiskMonth, 120)

DROP TABLE #PolicyNumbers
END 
GO

, "calendar".

:

  • TVP, , ( ). ,

    SELECT
    PolicyNumber,
    CASE
       WHEN month_of < PolicyEffectiveDate THEN PolicyEffectiveDate
       ELSE month_of
    END AS StartRiskMonth,
    CASE
       WHEN PolicyExpirationDate < month_after THEN PolicyExpirationDate
       WHEN Earned_to_date.Earned_to_date < month_after THEN Earned_to_date
       ELSE month_after
    END AS EndRiskMonth, 
    DateDiff(day, PolicyEffectiveDate, PolicyExpirationDate) AS policy_days,
    WrittenPremium
FROM 
    policy_data
OUTER APPLY 
    TableFunction_ListOfMonth (PolicyEffectiveDate, PolicyExpirationDate)
WHERE  
    month_of < CAST(@AsOfDate AS DATE)


  • DECLARE @Calendar TABLE (
    month_of     DATE, 
    month_after  DATE, 
    PRIMARY KEY (month_of, month_after)
);

WITH digits AS (
   SELECT digit
   FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS z2 (digit)
), numbers AS (

    SELECT 100 * d3.digit + 10 * d2.digit + d1.digit AS   FROM AS d1   CROSS JOIN AS d2   CROSS JOIN AS d3 ), AS (   DateAdd (, , '1950-01-01') AS month_of , DateAdd (, , '1950-02-01') AS month_after    ) @Calendar *

+1

:

CREATE TABLE #PolicyNumbers(PolicyNumber VARCHAR(50));

INSERT INTO #PolicyNumbers
      SELECT PolicyNumber
      FROM   tblClassCodesPlazaCommercial T1
      WHERE  NOT EXISTS
                    (SELECT 1
                    FROM   tblClassCodesPlazaCommercial T2
                    WHERE  T1.PolicyNumber = T2.PolicyNumber
                          AND ClassCode IN
                                       (SELECT *
                                        FROM   [dbo].[StringOfStringsToTable]
                                            (@ClassCode, ',')));

CREATE CLUSTERED INDEX IDX_C_PolicyNumbers_PolicyNumber ON #PolicyNumbers(PolicyNumber);

WITH Earned_to_date
    AS (SELECT CAST(@AsOfDate AS DATE) AS Earned_to_date
    --SELECT @AsOfDate AS Earned_to_date
    ),
    policy_data
    AS (SELECT                 PolicyNumber
                        , CAST(PolicyEffectiveDate AS DATE) AS PolicyEffectiveDate
                        , CAST(PolicyExpirationDate AS DATE) AS PolicyExpirationDate
                        , WrittenPremium
        --,         State
        FROM                   PlazaInsuranceWPDataSet pid
                          OUTER APPLY
                                   (SELECT PolicyNumber
                                    FROM   #PolicyNumbers pn
                                    WHERE  pn.PolicyNumber = pid.PolicyNumber) AS pn
        WHERE pn.PolicyNumber IS NULL
            AND State IN
                      (SELECT *
                       FROM   [dbo].[StringOfStringsToTable]
                           (@State, ','))
            AND Coverage IN
                        (SELECT *
                         FROM   [dbo].[StringOfStringsToTable]
                             (@Coverage, ','))),
    digits
    AS (SELECT digit
        FROM   (VALUES
                    (0),
                    (1),
                    (2),
                    (3),
                    (4),
                    (5),
                    (6),
                    (7),
                    (8),
                    (9)) AS z2(digit)),
    numbers
    AS (SELECT 1000 * d4.digit + 100 * d3.digit + 10 * d2.digit + d1.digit AS number
        FROM   digits AS d1
             CROSS JOIN digits AS d2
                            CROSS JOIN digits AS d3
                                          CROSS JOIN digits AS d4),
    calendar
    AS (SELECT DATEADD(month, number, '1753-01-01') AS month_of
            , DATEADD(month, number, '1753-02-01') AS month_after
        FROM   numbers),
    policy_dates
    AS (SELECT PolicyNumber
            , CASE
                WHEN month_of < PolicyEffectiveDate THEN PolicyEffectiveDate
                ELSE month_of
             END AS StartRiskMonth
            , CASE
                WHEN PolicyExpirationDate < month_after THEN PolicyExpirationDate
                WHEN Earned_to_date.Earned_to_date < month_after THEN Earned_to_date
                ELSE month_after
             END AS EndRiskMonth
            , DATEDIFF(day, PolicyEffectiveDate, PolicyExpirationDate) AS policy_days
            , WrittenPremium
        FROM   policy_data
             JOIN calendar ON(policy_data.PolicyEffectiveDate < calendar.month_after
                           AND calendar.month_of < policy_data.PolicyExpirationDate
                          )
             CROSS JOIN Earned_to_date
        WHERE  month_of < Earned_to_date)
    SELECT      --PolicyEffectiveDate,
    --PolicyExpirationDate,
    --PolicyNumber,
    Year(StartRiskMonth) AS YearStartRisk
   , MONTH(StartRiskMonth) AS MonthStartRisk
   , c.YearNum
   , c.MonthNum
   , CONVERT( VARCHAR(7), StartRiskMonth, 120) AS RiskMonth
   , SUM(WrittenPremium * DATEDIFF(day, StartRiskMonth, EndRiskMonth) / policy_days) AS EarnedPremium
    FROM  tblCalendar c
         LEFT JOIN policy_dates l ON c.YearNum = YEAR(l.StartRiskMonth)
                                AND c.MonthNum = MONTH(l.StartRiskMonth)
                                AND l.StartRiskMonth BETWEEN @StartDate AND @EndDate
    WHERE c.YearNum NOT IN(2017) --and PolicyNumber = 'PACA1000191-00'
    GROUP BY CONVERT( VARCHAR(7), StartRiskMonth, 120)
          , YEAR(StartRiskMonth)
          , MONTH(StartRiskMonth)
          , c.YearNum
          , c.MonthNum--,PolicyNumber--,PolicyEffectiveDate,PolicyExpirationDate
    ORDER BY c.YearNum
          , c.MonthNum;
--convert(varchar(7), StartRiskMonth, 120)


DROP TABLE #PolicyNumbers;

:

policy_data
    AS (SELECT PolicyNumber
            , CAST(PolicyEffectiveDate AS DATE) AS PolicyEffectiveDate
            , CAST(PolicyExpirationDate AS DATE) AS PolicyExpirationDate
            , WrittenPremium
        --,         State
        FROM   PlazaInsuranceWPDataSet pid OUTER APPLY
        WHERE  NOT EXISTS
                     (SELECT PolicyNumber
                      FROM   #PolicyNumbers pn
                      WHERE  pn.PolicyNumber = pid.PolicyNumber)
             AND State IN
                       (SELECT *
                        FROM   [dbo].[StringOfStringsToTable]
                            (@State, ','))
             AND Coverage IN
                         (SELECT *
                          FROM   [dbo].[StringOfStringsToTable]
                              (@Coverage, ','))),

To:

policy_data
AS (SELECT                 PolicyNumber
                    , CAST(PolicyEffectiveDate AS DATE) AS PolicyEffectiveDate
                    , CAST(PolicyExpirationDate AS DATE) AS PolicyExpirationDate
                    , WrittenPremium
    --,         State
    FROM                   PlazaInsuranceWPDataSet pid
                      OUTER APPLY
                               (SELECT PolicyNumber
                                FROM   #PolicyNumbers pn
                                WHERE  pn.PolicyNumber = pid.PolicyNumber) AS pn
    WHERE pn.PolicyNumber IS NULL
        AND State IN
                  (SELECT *
                   FROM   [dbo].[StringOfStringsToTable]
                       (@State, ','))
        AND Coverage IN
                    (SELECT *
                     FROM   [dbo].[StringOfStringsToTable]
                         (@Coverage, ','))),
0

Source: https://habr.com/ru/post/1663140/

More articles:


All Articles