CTE Performance, I'm confused!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

CTE Performance, I'm confused!

Author

Topic

Jeffuk
Starting Member

3 Posts

Posted - 2011-06-10 : 07:10:24

Morning All!

I'm running the below query (well, not the below query at all but it's a good simulation) to denormalise some data on the fly. why 'm doing this is irrelevant to the question.

When I run

with LotsofData(Name,Data1,Data2,Data3)
as
(select Name,Data1,Data2,Data3 from ComplexView)

select 'Data1' as datapoint, sum(data1) from LotsOfData

It takes 35 seconds to run, because complexview is complex!

I would expect

with LotsofData(Name,Data1,Data2,Data3)
as
(select * from ComplexView)

select 'Data1' as datapoint,sum(data1) as data from LotsOfData
union all
select 'Data2' as datapoint,sum(data2) as data from LotsOfData
union all
select 'Data3' as datapoint,sum(data3) as data from LotsOfData

to take approximately the same amount of time to run, as I thought the CTE would run the complex view once only, hold the result in memory, then use that to calculate each of the subsequent select statements.

This query is actually taking around 100 seconds to complete, as if the CTE is being calculated each time it is called, rather than just being calculated once.

Could anyone shed any light on this for me please, is this how they're supposed to work or am I missing something?

Thanks,

Jeff

PS, unless there are any syntax errors that would cause this problem, please ignore them, this is just to illustrate the point.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-10 : 07:16:05

Have a look at the query plan.
It often does calculate the cte every time it is referenced.

For this sort of thing a temp table would be best (and you can index it). Can also seperate the data extract from the processing.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-10 : 07:18:00

I don't believe it has to do with the CTE.
I think it has to do with the union query.
There are 3 queries and each query has to do the SUM() regardless of selecting from CTE or directly from the VIEW.

But that's my humble opinion

No, you're never too old to Yak'n'Roll if you're too young to die.

Jeffuk
Starting Member

3 Posts

Posted - 2011-06-10 : 07:26:07

quote:
Originally posted by webfred

I don't believe it has to do with the CTE.
I think it has to do with the union query.
There are 3 queries and each query has to do the SUM() regardless of selecting from CTE or directly from the VIEW.

But that's my humble opinion

thanks for that, but if the CTE was working as a temporary table as I'd hoped, it wouldn't take 30 seconds to add together a couple of thousand rows!

but apparently they don't do what I want, that's fine, I'll have to work out some sort of caching option.

For the record, I tried again, replacing any reference to the CTE with a subquery instead, and it took about the same amount of time. So in this instance at least it appears the CTE is simply acting as some sort of alias or macro for the underlying query.

Jeffuk
Starting Member

3 Posts

Posted - 2011-06-10 : 08:00:02

thanks again,

Changed it to a stored procedure something like:

select Name,Data1,Data2,Data3 into #lotsofdata from ComplexView

select 'Data1' as datapoint, sum(data1) from #LotsOfData
union all ....

Seems to work exactly as expected. Am I setting myself up for any problems?

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-06-10 : 08:03:36

Why not change your query. I realize this is only a sample... but have you tried something like:


with LotsofData(Name,Data1,Data2,Data3)
as
(select * from ComplexView)

, LotsOfDataSummed as (
	Select 
		data1 = sum(data1),
		data2 = sum(data2),
		data3 = sum(data3)
	From LotsOfData
)

Select 'Data1', data1 From LotsOfDataSummed 
Union All
Select 'Data2', data2 From LotsOfDataSummed 
Union All
Select 'Data3', data3 From LotsOfDataSummed

Corey

I Has Returned!!

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-10 : 08:13:34

can also try

with LotsofData(Name,Data1,Data2,Data3)
as
(select * from (select * from ComplexView) a)
....

or even

with LotsofData(Name,Data1,Data2,Data3)
as
(select * from (select * from (select * from ComplexView) a) a)
....

I can remember in the past having to put in two levels of derived tables to stop the optimiser merging it with the plan.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-06-10 : 11:27:13

Just for clarification CTE are NOT temp tables. You can think of it more like an old Macro where the CTE Reference is replaced with the CTE Definition just like using a Derived Table/Inline View. For example:

;with cte (SELECT * FROM Foo)

SELECT *
FROM Bar AS A
INNER JOIN cte AS B ON A.ID = B.ID
INNER JOIN cte AS C ON A.ID1 = C.ID
INNER JOIN cte AS D ON A.ID2 = D.ID

-- Equals

SELECT *
FROM Bar AS A
INNER JOIN (SELECT * FROM Foo) AS B ON A.ID = B.ID
INNER JOIN (SELECT * FROM Foo) AS C ON A.ID1 = C.ID
INNER JOIN (SELECT * FROM Foo) AS D ON A.ID2 = D.ID

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2011-06-12 : 01:50:44

quote:
Originally posted by Jeffuk

why 'm doing this is irrelevant to the question.

Maybe... maybe not. You could be absolutely correct, but remember that you're the one asking for help because you don't know about something so you might not want to jump to that conclusion so fast.

Calling a CTE more than once is like calling a View more than once. It WILL be executed each time it's called. You can verify that by looking at the execution plan.

In this case, it also means your complex view is being executed more than once.

Since you're calling the CTE more than once, you might want to tell us what you're really doing because there are ways around doing such a thing but you have to know the gazintas first.

--Jeff Moden

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2011-06-12 : 02:05:33

quote:
Originally posted by Jeffuk

I'll have to work out some sort of caching option.

Nah... caching isn't needed because you only need to look at the view and the CTE once. I don't have your data to test against, but something pretty close to the following will work.

WITH cteLotsOfData AS
(--==== Preaggregate the data from the view
 SELECT Data1 = SUM(Data1),
        Data2 = SUM(Data2),
        Data3 = SUM(Data3)
   FROM dbo.ComplexView
)
 SELECT ca.DataPoint, ca.Data
   FROM cteLotsOfData lod
  CROSS APPLY
        (
         SELECT 'Data1', Data1 UNION ALL
         SELECT 'Data2', Data2 UNION ALL
         SELECT 'Data3', Data3
        ) ca (DataPoint,Data)
;

--Jeff Moden

Sachin.Nand

2937 Posts

Posted - 2011-06-13 : 08:28:04

How about UnPivot approach



select datapoint,sum(data)data from (
select * from ComplexView )T
unpivot(data for datapoint in(Data1,Data2,Data3))U
group by datapoint

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources