Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»»

problem in select command Expand / Collapse
Author
Message
Posted Monday, March 18, 2013 3:18 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, April 13, 2014 9:51 PM
Points: 4,570, Visits: 8,315
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?
Post #1432360
Posted Monday, March 18, 2013 3:21 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, April 13, 2014 9:51 PM
Points: 4,570, Visits: 8,315
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.
Post #1432361
Posted Monday, March 18, 2013 3:23 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 6:41 AM
Points: 22,495, Visits: 30,202
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.



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)
Post #1432363
Posted Monday, March 18, 2013 3:29 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:24 PM
Points: 11,990, Visits: 11,007
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 Moden's 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)
Post #1432365
Posted Monday, March 18, 2013 4:06 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, April 13, 2014 9:51 PM
Points: 4,570, Visits: 8,315
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.

Post #1432376
Posted Monday, March 18, 2013 4:15 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, April 13, 2014 9:51 PM
Points: 4,570, Visits: 8,315
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.
Post #1432380
Posted Monday, March 18, 2013 4:16 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 5:44 PM
Points: 893, Visits: 6,894
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
Post #1432383
Posted Monday, March 18, 2013 4:22 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 2:53 PM
Points: 3,733, Visits: 7,072
How can I remove my post?


______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1432386
Posted Monday, March 18, 2013 4:25 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, April 13, 2014 9:51 PM
Points: 4,570, Visits: 8,315
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.
Post #1432388
Posted Monday, March 18, 2013 4:44 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 5:44 PM
Points: 893, Visits: 6,894
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
Post #1432396
« Prev Topic | Next Topic »

Add to briefcase «««12345»»»

Permissions Expand / Collapse