huge number of rows in table spool

Question

huge number of rows in table spool

sqlnoobie

Mr or Mrs. 500

Points: 528
More actions
July 6, 2015 at 8:35 am

#318831

Hi All: I have a CTE query against a table with 32K rows that runs fine in 2008R2. I am running it in 2014 Std Ed. against the same data and it runs very slowly. Looking at the execution plan I think I see what's contributing to the slowness.
Note that the "actual number of rows" is some 351M...how is this possible?
the query:
declare @amts table (claim int,allowed decimal(12,2),copay decimal(12,2),deductible decimal(12,2),coins decimal(12,2));
;with unpaid (claimID) as (select claimID from claim where amt+copay + disct+mm + ded=0)
insert @amts
select lineID, sum(rc), sum(copay), sum(deduct),
case when sum(mm)>0 and (sum(mm)<sum(mmamt)) then sum(mm) else 0 end
from claimln
where status is null
and lineID not in (select claimID from unpaid)
group by lineID
it's like there's some massively recursive process going on?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Actual Number of Rows = Rows in the table spool * Number of Executions. It's the total number that have been read out of the spool, not the number inserted.

Can you post the table and index definitions and the actual execution plan please?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sqlnoobie Mr or Mrs. 500 Points: 528 More actions · Answer 2

Thanks Gail: I was able to return the query to normal operations removing the "NOT IN (SELECT..." and changing the CTE to include the matching IDs, so I am just joining on the CTE instead of using it recursively.

I'm surprised though at the performance disparity between 2008 and 2014 running the identical query.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

I'm not. The cardinality estimator changed completely in SQL 2014. Most queries have an improved performance, but some do get a degradation.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398872 More actions · Answer 4

I'm just surprised you didn't get a table spool in 2008 with a recursive CTE. That's pretty common.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Grant Fritchey (7/7/2015)
I'm just surprised you didn't get a table spool in 2008 with a recursive CTE. That's pretty common.

A recursive CTE plan always features spools. It's not a recursive CTE:

select lineID, sum(rc), sum(copay), sum(deduct),

case when sum(mm)>0 and (sum(mm)<sum(mmamt)) then sum(mm) else 0 end

from claimln

where status is null

and lineID not in (select claimID from unpaid)

group by lineID

The spool is due to the NOT IN. Using NOT EXISTS might yield a better plan:

select

c.lineID,

sum(rc),

sum(copay),

sum(deduct),

case when sum(mm)>0 and (sum(mm)<sum(mmamt)) then sum(mm) else 0 end

from claimln c

where c.[status] is null

and NOT EXISTS (SELECT 1 FROM unpaid u WHERE u.claimID = c.lineID)

group by c.lineID

Edit: I'm an idiot today.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden