CTE to improve performance

Question

CTE to improve performance

nadersam

SSCertifiable

Points: 6542
More actions
October 11, 2015 at 3:12 am

#305379

Hi All,
I was wondering if using CTE could lead to performance enhancement.
My idea was if i have a big view referencing many tables and some are big tables.
If some of the big tables were isolated in a CTE that might lead to a performance improvement.
I tried it but the result was worse so please tell me if you have any ideas about that.
Thanks in advance.
Nader

Viewing 12 posts - 1 through 11 (of 11 total)

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

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 1

nadersam (10/11/2015)
Hi All,
I was wondering if using CTE could lead to performance enhancement.
My idea was if i have a big view referencing many tables and some are big tables.
If some of the big tables were isolated in a CTE that might lead to a performance improvement.
I tried it but the result was worse so please tell me if you have any ideas about that.
Thanks in advance.
Nader

No. A CTE is not a panacea of performance because CTEs are not resolved separately any more than a sub-query in a FROM clause would be or a call to a view would be. The key will likely be to either optimize the view or use "Divide'n'Conquer" methods to split up the view in a stored procedure.

Also be aware that a CTE is more like a view than you might imagine. If you reference it more than once in an outer query, it will be executed more than once.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

nadersam SSCertifiable Points: 6542 More actions · Answer 2

Thank you Jeff,

I guess CTE is not the answer to this issue, i will try to look into using a stored procedure instead.

I have a question please related to that.

If i have a view referencing many tables, what's the order of fetching for data, is it

1. The inner joins between tables.

2. Where conditions defined in the view

3. Conditions used when calling the view.

That could help me decide how to break that view into pieces and use a stored procedure instead.

Thanks again

Nader

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 3

Pick the piece that returns the smallest number of rows that the rest of the query will be dependent on. Stuff that into a Temp Table and join to the Temp Table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

nadersam SSCertifiable Points: 6542 More actions · Answer 4

nadersam

SSCertifiable

Points: 6542

October 11, 2015 at 10:51 am

#1832719

Will try that

Thank you very much

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

nadersam (10/11/2015)
If i have a view referencing many tables, what's the order of fetching for data, is it
1. The inner joins between tables.
2. Where conditions defined in the view
3. Conditions used when calling the view.

None of the above.

During parsing, SQL will replace a view with the definition of the view and then apply simplification rules. By the time the query reaches the optimiser, there's no sign of the view left, just a query referencing base tables.

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

nadersam SSCertifiable Points: 6542 More actions · Answer 6

Thanks Gail for your reply.

I asked this to be able to decide how will i break my view into parts and use a stored procedure instead.

Jeff suggested taking out the tables with small results and putting them in a temp or table variable then joining with that.

If you have other ideas please let me know.

Thanks

Nader

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

Jeff suggested temp tables. Be careful with table variables, because they don't have statistics, the row estimations are miles off usually and they can (and do) result in really bad performance.

I recently got a procedure from 30seconds to about 15ms just by replacing a table variable with a temp table.

Test carefully.

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

nadersam SSCertifiable Points: 6542 More actions · Answer 8

GilaMonster (10/12/2015)
Jeff suggested temp tables. Be careful with table variables, because they don't have statistics, the row estimations are miles off usually and they can (and do) result in really bad performance.
I recently got a procedure from 30seconds to about 15ms just by replacing a table variable with a temp table.
Test carefully.

About the timing do you mean the contrary?

So in your case the temp tables is better than table variables?

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

nadersam (10/12/2015)
GilaMonster (10/12/2015)
Jeff suggested temp tables. Be careful with table variables, because they don't have statistics, the row estimations are miles off usually and they can (and do) result in really bad performance.
I recently got a procedure from 30seconds to about 15ms just by replacing a table variable with a temp table.
Test carefully.
About the timing do you mean the contrary?

No, I mean precisely what I said.

So in your case the temp tables is better than table variables?

Maybe you missed the earlier part of my statement:

Be careful with table variables, because they don't have statistics, the row estimations are miles off usually and they can (and do) result in really bad performance.

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

nadersam SSCertifiable Points: 6542 More actions · Answer 10

GilaMonster (10/12/2015)
nadersam (10/12/2015)
GilaMonster (10/12/2015)
Jeff suggested temp tables. Be careful with table variables, because they don't have statistics, the row estimations are miles off usually and they can (and do) result in really bad performance.
I recently got a procedure from 30seconds to about 15ms just by replacing a table variable with a temp table.
Test carefully.
About the timing do you mean the contrary?
No, I mean precisely what I said.
So in your case the temp tables is better than table variables?
Maybe you missed the earlier part of my statement:
Be careful with table variables, because they don't have statistics, the row estimations are miles off usually and they can (and do) result in really bad performance.

Thanks Gila

Sergiy SSC Guru Points: 110206 More actions · Answer 11

GilaMonster (10/12/2015)
Be careful with table variables, because they don't have statistics

Not quite true.

They do not have statistics only if you have not created them.

But this table variable will have stats:

DECLARE @Table TABLE (

SerialNumber nvarchar(30),

Reference nvarchar(50),

PRIMARY KEY (SerialNumber),

UNIQUE (Reference)

)

_____________
Code for TallyGenerator