Reputation: 4428
The SP runs very slow. When I look at execution plan - I can see that 83% of its cost goes to Nested Loops (Inner Join)
Is any chance to substitute it somehow?
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,','))
)
...
Here I am adding my full query for 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 production link:
And this is how my TempDB configured:
Upvotes: 6
Views: 6814
Reputation: 3026
I think problem is in your "calendar" subquery. It returns 10000 rows without any index. Maybe your actual date range between 1950 and 2033:
Try this
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
If it works, problem indeed is in "calendar" subquery.
Ideas to fix it:
TVP that returns a table contains only policy active months (I've changed last rows). I think it will be few rows
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)
put results of your subquery in table variable with clustered index
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
Upvotes: 1
Reputation: 3810
Try this:
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;
I essentially changed:
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, ','))),
Upvotes: 0