Acquiring earnings information

Question

The first part of the query grabs Policy's Premiums, Effective and Expiration dates. The second part creates Calendar, and third part (final SELECT statement) returns Earnings broken down by Month and Year.

Everything works fine and takes just 3 seconds to display the result. But then I need to filter what PolicyNumbers to work with, basically I need to get rid of PolicyNumber's that dont have @ClassCode. So for that in the first part of the query I placed WHERE clause:

WHERE  State IN ('CA','NV','AZ')        
        AND PolicyNumber IN (
                            SELECT  PolicyNumber
                            FROM    tblClassCodesPlazaCommercial 
                            GROUP BY PolicyNumber
                            HAVING COUNT (CASE WHEN ClassCode NOT IN (@ClassCode)                                                                       
 THEN 1 END)=0
                            )

The CASE statement will generate 1 for the ClassCode that is present in the list, else NULL will be generated. Now the count aggregate will count of 1 for each PolicyNumber. By setting = 0 we can make sure the PolicyNumber does not have any ClassCode present in the given list.

After that query spinning forever, becase @ClassCode can have more that 200 ClassCodes in SSRS report.

Interesting things is that both those statements works fine separately. But when I use them together (which is placed that WHERE clause in cte policy_data then execution takes forever.

Is any way to tell engine to do the first part of the query, and then calculate and break down Earnings for only those policies that have been filtered?

; WITH Earned_to_date AS (
   SELECT Cast(EOMONTH (GETDATE(), -1) AS DATE) 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
        WHERE  State IN ('CA','NV','AZ') 
    /* -------This statement gives me trouble ----------------------*/
        AND PolicyNumber IN (
                            SELECT  PolicyNumber
                            FROM    tblClassCodesPlazaCommercial 
                            GROUP BY PolicyNumber
                            HAVING COUNT (CASE WHEN ClassCode NOT IN (5151)                                                                     
 THEN 1 END)=0
                            )
)

My entire code:

; WITH Earned_to_date AS (
   SELECT Cast(EOMONTH (GETDATE(), -1) AS DATE) 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
        WHERE  State IN ('CA','NV','AZ') 
    /* -------This statement gives me trouble ----------------------*/
        AND PolicyNumber IN (
                            SELECT  PolicyNumber
                            FROM    tblClassCodesPlazaCommercial 
                            GROUP BY PolicyNumber
                            HAVING COUNT (CASE WHEN ClassCode NOT IN (@ClassCode)                                                                       
 THEN 1 END)=0
                            )
)
, 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      
            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 '01-01-2015' AND  '12-31-2016'
WHERE       c.YearNum Not IN (2017) 
GROUP BY    convert(varchar(7), StartRiskMonth, 120),
            Year(StartRiskMonth) , Month(StartRiskMonth),
            c.YearNum,c.MonthNum
ORDER BY    c.YearNum,c.MonthNum

What would be the best way to improve the performance? I created non-clustered index on PolicyNumber on both tables. But still nothing.

Like I said, seems to me that if SQL Engine would process the first part(PolicyNumber filtering) that takes 3 seconds, and then do the second part (calculation for those PolicyNumber's) that takes another 3 seconds - that would be awesome.

Execution plan:

Final result:

Answer 1

I'm going to first address your main question:

Like I said, seems to me that if SQL Engine would process the first part(PolicyNumber filtering) that takes 3 seconds, and then do the second part (calculation for those PolicyNumber's) that takes another 3 seconds - that would be awesome.

Ultimately, T-SQL is a declarative language, meaning you tell it what you want, and it figures out how to do it. It does so by using a lot of behind-the-scenes information to estimate what the most efficient way of doing it will be, and then does it. If it estimated incorrectly, then you get poor performance. There isn't a way^a to get it to pick a different plan explicitly; you need to make sure it can pick the best plan for itself.

In your case, it is picking nested loops for your joins to PlazaInsuranceWPDataSet because it thinks that very few (probably 1^b) rows are going to actually join. Nested loops are the fastest way to join very small amounts of unsorted data; they require no startup cost, memory, or sorting. Nested loops start to perform very, very poorly if there is a decent amount of data there.

So why would the optimizer think that no rows are going to come out of them? Lets look at the joins you do:

1. In the policy_data, the PolicyNumber IN ( <<subquery>> ) is a semi-join, even though you don't have an explicit JOIN present.
2. In the policy_dates CTE you join from the policy data to the calendar data.
3. In the final query, you join from tblCalendar to your policy_dates CTE

Your entire query uses questionable methodology to filter and join data that makes it really, really hard for SQL Server to figure out how many rows are going to come out of a given operator. Eventually, SQL Server will get so confused that it will assume that nothing is going to join, and pick nested loops for everything. If SQL Server is wrong (as it appears to be) then it is going to run very, very slowly.

Specifically, SQL Server maintains cardinality estimates on most tables, indices, and columns in the database. This cardinality estimate tells you things about the approximate distribution of data in the table, and it can use that to determine what kind of plan is needed. For example, assume you have a query like this:

SELECT B.SomeFunData
  FROM MyTable A
    INNER JOIN MyOtherTable B
      ON B.SurrogateKey = A.ForeignKey
  WHERE A.SurrogateKey > 10000000 -- Get the rows after 10M

This seems like a very straightforward query, right? If SQL Server doesn't think there are rows where A.SurrogateKey > 10000000 then it might first filter A, then nested-loop join to B to avoid any performance issues. This would be bad if it was wrong, but if it is right then it has picked a great plan.

The problem with your query is that your predicates rely on things that SQL Server has no estimate for - functions.

SQL Server knows the distribution of values in tblClassCodesPlazaCommercial.ClassCode, but not of CASE WHEN ClassCode NOT IN ( @ClassCode ) THEN 1 END. Similarly, it doesn't know the distribution of CAST( PlazaInsuranceWPDataSet.PolicyEffectiveDate AS date). It is very important to use SARGable expressions in your query; joining on or filtering on non-sargable expressions will generally lead to poor performance

---

So how do we resolve this? There are a few low-hanging fruit we can grab immediately:

1. Instead of recalculating digits and numbers and calendar every time, you should create a numbers table and a date table. These can be persisted somewhere, and are very useful in many applications.
2. Earned_to_date is not a good thing to use in your joins; you would likely be better off if you could pre-compute it and use it as a variable; then SQL Server can use normal optimizations instead of shoddy cardinality estimates
3. If you have a filter on something that doesn't depend on other tables, don't include it in the join. l.StartRiskMonth BETWEEN '01-01-2015' AND '12-31-2016' should become part of populating policy_dates instead of the join to it.

After that it gets a bit trickier.

Your policy_data CTE needs to be cleaned up. Right now your HAVING clause is going to be pretty difficult for the optimizer to clean up. What you actually wanted was to find all PolicyNumbers that have only valid ClassCodes, correct? Using NOT EXISTS and a normal WHERE clause still gets you the semi-join, but SARGably.

AND NOT EXISTS ( SELECT 1
                    FROM tblClassCodesPlazaCommercial ValidPolicyNumbers
                    WHERE ValidPolicyNumbers.ClassCode NOT IN ( @ClassCode )
                    AND ValidPolicyNumbers.PolicyNumber = PlazaInsuranceWPDataSet.PolicyNumber )

Now, because you later join on PolicyEffectiveDate and PolicyExpirationDate, you really should try to either join on them using the same data-type, or store them as dates in the table. If you can't change how they're stored, a computed column^c may be appropriate.

We've already talked about the policy_dates CTE; the cleanups mentioend above should help this one a lot.

For the final query, you'll want to not join on YEAR and MONTH. One option here would be to materialize policy_dates into a temp table and then join on that, and include the calculated YEAR and MONTH in that table. Alternatively, if you just join from tblCalendar to the StartRiskMonth as a date, that will likely work, and then you can use a DISTINCT or GROUP BY to clean up the granularity.

Speaking of GROUP BY, using functions in there is usually a no-no as well; now you can't use any existing sorts in the data (for example, if you have an index that sorts by the first 3 columns it can't use that). I also suspect you don't need to actually group by that many things; using an aggregate over a value that doesn't change granularity will generally perform better than including it in the GROUP BY.

For example:

SELECT FirstName, LastName, COUNT( * ) Age
  FROM PeopleList
  GROUP BY FirstName, LastName

Suppose this table kept track of people's ages by having a row per year of their life. Further suppose that everyone has a unique first and last name, and that neither change (obviously not real-world data, but its fine as an example). It would perform better if we did this:

SELECT FirstName, MAX( LastName ) LastName, COUNT( * ) Age
  FROM PeopleList
  GROUP BY FirstName

This will perform better because the GROUP BY operation sorts data in the background; sorting on a single column is cheaper than the other, and we know that based on our real data the MAX doesn't change anything about the results.

---

Footnotes:

• ^{a: I'm lying, of course. Breaking your query up into separate chunks (for example, materializing data into a temp table) or using certain query hints can force the optimizer to do things in a certain order. These are both valid techniques to use, although restructuring should almost always be preferred to query hints}
• ^{b: If you ever notice an estimated number of rows in a plan of 1, SQL Server probably actually thinks that no rows will come out of that operator. If it actually optimized for 0 rows, and it was wrong, then it would generate the incorrect result. That is why it will always estimate 1 row to ensure that it gets the correct answer, even if very slowly}
• ^{c: I'm actually not sure if a computed column of a CAST will actually have decent cardinality estimates; certainly worth playing with, however}

Answer 2

I think this does what you are looking for (more efficiently)

 SELECT disticnt PolicyNumber
 FROM tblClassCodesPlazaCommercial 
 where not exist ( select 1 
                   from tblClassCodesPlazaCommercial
                   where ClassCode NOT IN (@ClassCode) ) 

Original code

SELECT  PolicyNumber
FROM    tblClassCodesPlazaCommercial 
GROUP BY PolicyNumber
HAVING COUNT (CASE WHEN ClassCode NOT IN (@ClassCode) THEN 1 END) = 0