SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


problem in select command


problem in select command

Author
Message
Sergiy
Sergiy
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10206 Visits: 11958
Sean Lange (3/18/2013)
Of course a CTE can use tempdb. Just like a temp table, if the memory pressure is too much it will absolutely start writing data to tempdb.

Hmm...
I saw a server stopped operating because it's run out of memory, while its tempdb was basically empty.
At the moment I fixed the problem by changing CTE's to DT's.
But probably I did something else to improve the queries.
Will need to check later.

But anyway - even if you're right and CTE's can use tempdb - it makes them totally equal to DT's.
No advantage to any method, except for that extra line of code required by CTE syntax.

Do you agree with me on this?
Sergiy
Sergiy
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10206 Visits: 11958
Lynn Pettis (3/18/2013)

So a derived table in a FROM clause would have the same problem, wouldn't it.

It would seem that a view would also have the same issue as well.


Definitely not.

You may write a horrible query causing a huge hash join and see tempdb filled with data with no temporary objects created in it.
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38903 Visits: 38508
Sean Lange (3/18/2013)
Sergiy (3/18/2013)
Sean Lange (3/18/2013)
This type of discussion comes up around here over and over. Should we use a subquery or a cte. In cases like this they almost always proven to produce 100% identical execution plans. So in the end it all boils down to preference. There is no right or wrong. It is simply two ways of saying the same thing.

Exactly my point.

Unlike Lynn said, using CTE has no preference comparing to using derived tables.
+1.

But CTE imposes a danger.
CTE's are strictly in-memory objects. They do not use tempdb for storing excessive data.
So, if you carelessly put too big data set into CTE and then use them in some hash join it could easily kill the server, even if tempdb has terabytes of free space to spare.
Especially it's dangerous in multi-user environment.


Of course a CTE can use tempdb. Just like a temp table, if the memory pressure is too much it will absolutely start writing data to tempdb.


I think CTEs make complex queries easier to read and understand instead of using derived tables in multiple from clauses in a query.

But, to each their own.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25807 Visits: 17509
Sergiy (3/18/2013)
Sean Lange (3/18/2013)
Of course a CTE can use tempdb. Just like a temp table, if the memory pressure is too much it will absolutely start writing data to tempdb.

Hmm...
I saw a server stopped operating because it's run out of memory, while its tempdb was basically empty.
At the moment I fixed the problem by changing CTE's to DT's.
But probably I did something else to improve the queries.
Will need to check later.

But anyway - even if you're right and CTE's can use tempdb - it makes them totally equal to DT's.
No advantage to any method, except for that extra line of code required by CTE syntax.

Do you agree with me on this?


Yes I don't think there is a clear cut advantage for either approach as far as performance is concerned. I think it boils down to preference. My preference is the age old "it depends". I find that sometimes I prefer one over the other. I do often find that using a CTE can make an otherwise complicated query a bit easier to decipher.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Sergiy
Sergiy
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10206 Visits: 11958
Sean Lange (3/18/2013)
Of course a CTE can use tempdb. Just like a temp table, if the memory pressure is too much it will absolutely start writing data to tempdb.


What would you say about that?
http://www.sql-server-performance.com/2012/common-table-expressions-cte-developers/

f you are using larger data sets in table variables it will use tempdb to store data physically. However, a CTE always uses memory. This can be an issue in electing for CTEs as you need to ensure that you are not using larger data sets that will drain memory. The following graph shows tempdb write transactions/sec for temp tables, table variables and CTE scenarios.

Sergiy
Sergiy
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10206 Visits: 11958
Another one:

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2008/05/17/non-recursive-common-table-expressions-performance-sucks-1-cte-self-join-cte-sub-query-inline-expansion.aspx

I have some questions about it, though.
Will do some excersises around it, when have some spare time.
David Webb-CDS
David Webb-CDS
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1554 Visits: 8586
Hmmmmm... Based on what I saw of the article, I'd have to say that the conclusions drawn were unwarranted. I didn't see anywhere where the 3 different queries were proven to be equivalent, nor did I see the data volumes being worked by the queries. I don't think you can draw the conclusion from the evidence given that CTEs will never write to tempdb. Could be true, though counter-intuitive, but I don't think the article proved it.



And then again, I might be wrong ...
David Webb
MyDoggieJessie
MyDoggieJessie
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6628 Visits: 7390
How can I remove my post?
:-D

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience" ;-)
Sergiy
Sergiy
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10206 Visits: 11958
Sean Lange (3/18/2013)
Yes I don't think there is a clear cut advantage for either approach as far as performance is concerned. I think it boils down to preference.


If you come back to where it's started you'll see it's exactly what I said.
The only difference is as minor as an extra line of code required for syntax sake only.

I'm not so sure about that "in-memory" thing, therefore I did not bring it up, until you guys forced me. :-)
But I know there is controversy around it.
That's why I prefer (and suggest) not to use CTE where it's not necessary.

Again, it's my preference.
David Webb-CDS
David Webb-CDS
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1554 Visits: 8586
From the MS site:

\
A common table expression can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement.

When the query plan for a common table expression query uses a spool operator to save intermediate query results, the Database Engine creates a work table in tempdb to support this operation.


http://msdn.microsoft.com/en-us/library/ms345368(v=SQL.105).aspx



And then again, I might be wrong ...
David Webb
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search