# Acquiring earnings information

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
,       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
,       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-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
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,
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:

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 waya 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 1b) 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 columnc 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

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

• Down vote may I ask your basis for the down vote? – paparazzo Feb 21 '17 at 20:46
• You have presented an alternative solution, but haven't reviewed the code. Please edit to show what aspects of the question code prompted you to write this version, and in what ways it's an improvement over the original. It may be worth (re-)reading How to Answer. – Toby Speight Aug 28 '19 at 16:53