January 20, 2008 at 7:39 am
Good article, but I'd have included using derived tables to replace cursors and other use cases just as or more useful
January 22, 2008 at 3:27 pm
Very interesting technique, I can see that coming in handy. I really need to read more. 😉
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
August 1, 2008 at 9:32 am
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
November 7, 2008 at 10:04 am
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!
November 7, 2008 at 5:57 pm
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
Viewing 5 posts - 31 through 34 (of 34 total)
You must be logged in to reply to this topic. Login to reply