3

I'm a huge fan of CTE's. They are the best. You know it, I know it, we all know it.

Today, I got burned with a query that is about 1000x faster if the CTE is written to a real table.

The query itself is in the link, but I suspect it isn't important. To simplify, I needed a list of all the claims in an insurance policy where one claim met a certain condition (cat = 1).

A simplified version looks like this:

    with cat_claims as (select distinct pol_nbr from claims where cat = 1)

    select * from claims c
    inner join policies p on p.polnbr = c.polnbr
    inner join cat_claims cat on c.pol_nbr= cat.pol_nbr

which was super duper slow. If I rewrite it to look like this:

    with cat_claims as (select distinct claim_nbr from claims where cat = 1)

    select * into cat_claims
    from cat_claims

    select * from claims c
    inner join policies p on p.polnbr = c.polnbr
    inner join cat_claims cat on c.pol_nbr = cat.pol_nbr

    drop table cat_claims

it's way, way faster. This is odd to me.

Question Why would writing the CTE to an actual table provide a performance improvement?

Slow plan:

https://www.brentozar.com/pastetheplan/?id=HkyrpAtH4

Fast Plan:

https://www.brentozar.com/pastetheplan/?id=ByGkAAKrN

3
  • 4
    The query is always important. This other question might have some of the background info you're looking for. Feb 19, 2019 at 20:17
  • 3
    Why so many SQL Server developers resist the use of statement terminators? (;) Do they like conffusing themselves and others? (the scripts above have 1 and 3 statements respectively while they look like 2 and 4)... Not to mention the error we'll get if we run them in a single batch. Feb 19, 2019 at 20:20
  • 6
    My interpretation is that CTEs are not meant to make your query faster since they just act like a disposable view or as a temp results set. When joins are involved, a physical table (in your case or just creating a temp table) - the results are stored and evaluated once vs for a CTE, they are for every row. See dba.stackexchange.com/q/13112/8783
    – Kin Shah
    Feb 19, 2019 at 20:28

1 Answer 1

6

Even though they are estimated plans and not the actual ones, the difference to me looks like it is coming from joining the tables: RATING_CONTRIB_LOSS, RATING_CONTRIB_USR, cmbgrp, rating_revision_set, and the Rating table from the ratings CTE.

Joining these tables spawn huge table spools (lazy spool), that could be even bigger on the actual plan.

enter image description here

For every row in the top part of the nested loops operator, the bottom result set is scanned resulting in

3361 rows * 3359 in the first table spool --> Nested loops --> Top(1)

and

1541 Rows * 3359 in the second table spool --> Nested loops --> Top(1)

enter image description here

These table spools are not present in either of the two plans in the temp table queries.

enter image description here

What I am getting at is that the query part that inserts into the table is alike to the corresponding query part of the CTE. There are some differences but I think that the major cause of the slowdown is because of the table spools.

An example of a difference is that the full table scan on RATING_CONTRIB_LOSS in the CTE plan is changed to a scan with a residual predicate in the temp table plan.

CTE

enter image description here

Temp table

enter image description here enter image description here

And you could argue that the second part of the query that uses the temp table might access the RATING_CONTRIB_LOSS table again. And I would have to agree because it will be doing an expensive key lookup.

enter image description here

However, the second table spool in the CTE plan is also based on a nested loops join with theRATING_CONTRIB_LOSS table, which is not present in the temp table plan, and that is a big plus.

Conclusion

I do believe that the difference in execution time comes from the query using the temp table's result in such a way that costly operators are not used. More specifically the Table spools. I would need the actual plan to be 100% sure.

As a sidenote, rewriting CTE's into temp tables or just splitting up queries in general can in many cases give better execution times.

It is easier to guess how many apples are in a bag with apples and pears than guess how many apples are in a bag with apples, pears, mango's, oranges, peaches, .... (Yes the fruit = tables).

I would also use actual #temp tables to store your intermediary results in.


Comparison CTE & Temp table insert query parts

Plan with CTE part 1

enter image description here

Plan with CTE part 2 enter image description here

Plan with temp table part 1 enter image description here

Difference on RATING_CONTRIB_LOSS & Hash match join <> Nested Loops Join. Rest is very much alike, Same operations (2x) on Rating table

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.