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 «««1234

SQL - Derived Tables Expand / Collapse
Author
Message
Posted Sunday, January 20, 2008 7:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, January 20, 2008 7:33 AM
Points: 40, Visits: 2
Good article, but I'd have included using derived tables to replace cursors and other use cases just as or more useful


Post #445287
Posted Tuesday, January 22, 2008 3:27 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: Wednesday, May 9, 2012 10:26 AM
Points: 891, Visits: 1,958
Very interesting technique, I can see that coming in handy. I really need to read more.
Post #446146
Posted Friday, August 1, 2008 9:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 5, 2014 8:13 AM
Points: 34, Visits: 121
I've Recently been working with derived and temporary tables and in most instances I've found the performance issues to be neglible. To be fair I'm using relatively small data sets.

But I find by using temporary tables I can make the SQL scripts much easier to interprete at a later date especially when there are multiple, complicated derived tables being used and various analyst using the scripts.

Surely in these instances it's down to personal preference
Post #545249
Posted Friday, November 7, 2008 10:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 14, 2014 7:09 AM
Points: 47, Visits: 789
This is a great article and a technique overlooked often for increasing performance.

After reading the article I had a few things that I was going to add but I read Jeff's reply and sense it was the same as mine I'll quote him ;)

First, Derived Tables are NOT memory only constructs... just like any query, if they run out of room in memory, they WILL make a thing called a "Working" table and you can frequently see them in the text version of execution plans. Guess where those bad boys live... you guessed it... memory if they fit and if they don't, TEMPDB!

Also, your information about Temp Tables living only on disk is dead wrong. Please read the following Microsoft provided URL...

http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k

... and pay particular attention to where it states "If memory is available, both table variables and temporary tables are created and processed while in memory (data cache). "

You also need to check Books Online for what the persistance of a Temp Table actually is... you DON'T need to bring the server down to get rid of a Temp Table.

Don't let this dampen your writing spirit... you provided a really good intro to derived tables... I just had to correct your statements about temp tables. In fact, there are some occasions where a Temp Table will blow the doors off of derived tables... usually in a multi-table-joined update when aggragates are necessary. They work pretty well as a substitute for CTE's, too!

--Jeff Moden


Another thing to be careful of is indexing and statistics. You have to keep in mind things will change when you change the structure of your query like derived tables. Run the execution plan after doing so and make sure you are getting the full speed out of SQL Server by the means of supporting objects. Also don't forget to plan for adding indexes and stats to support the query alterations. Maintenance jobs that have already been written should be updated to reflect

Well done Prashant!
Post #599109
Posted Friday, November 7, 2008 5:57 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:14 PM
Points: 35,266, Visits: 31,756
tony rogerson (1/16/2008)
The derived table itself is just expanded into the main query (just like a view) this means that there are no statistics available for the derived table.

Tony.


I'm not sure that's true... it's true that neither a view nor a derived table will have statistics on them, but the underlying data data does and that's how it selects which indexes on the underlying tables to use in the execution plan.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #599357
« Prev Topic | Next Topic »

Add to briefcase «««1234

Permissions Expand / Collapse