InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 11:13:05 GMT20http://www.sqlservercentral.com/Forums/Topic443400-1122-1.aspx[quote][b]tony rogerson (1/16/2008)[/b][hr]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.[/quote]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.Fri, 07 Nov 2008 17:57:54 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic443400-1122-1.aspxThis 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 ;) [quote]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[/quote]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 reflectWell done Prashant!Fri, 07 Nov 2008 10:04:20 GMTTed Kruegerhttp://www.sqlservercentral.com/Forums/Topic443400-1122-1.aspxI'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 preferenceFri, 01 Aug 2008 09:32:10 GMTkris.athertonhttp://www.sqlservercentral.com/Forums/Topic443400-1122-1.aspxVery interesting technique, I can see that coming in handy. I really need to read more. ;-)Tue, 22 Jan 2008 15:27:57 GMTWayne Westhttp://www.sqlservercentral.com/Forums/Topic443400-1122-1.aspxGood article, but I'd have included using derived tables to replace cursors and other use cases just as or more usefulSun, 20 Jan 2008 07:39:11 GMTmgeiserhttp://www.sqlservercentral.com/Forums/Topic443400-1122-1.aspxChristophe, an update to what? Perhaps a subset of a table where the subset is defined thru a derived table rather than a where clause? Honestly, I'm just trying to understand and move on.Thu, 17 Jan 2008 10:40:28 GMTsteitelbaumhttp://www.sqlservercentral.com/Forums/Topic443400-1122-1.aspxHi,thanks for the article, and some comments here were also useful.(frankly I was using the derived table in some case in order make sql code a bit clearer, but I didn't know it was a called a "derived" table ;) )for the question or remarks about the update part, some of us still use sql 2k, so knowing that an update is impossible is important to know.The sample in the article is simply that: a sample. It has no other value than to test the function and prove the point. (no need to propose a different way to do it :P)Thu, 17 Jan 2008 10:28:44 GMTChristophe-423236http://www.sqlservercentral.com/Forums/Topic443400-1122-1.aspxderived table concept is very usefull from seeing performance issuesit is very good articlei was using derived table , but my current company not advicing me to use, since here using temp tablesThu, 17 Jan 2008 03:45:10 GMTshamshudheenhttp://www.sqlservercentral.com/Forums/Topic443400-1122-1.aspxThis thread illustrates that there are many ways of writing exactly the same query.I find that derived tables are fine up to a point, beyond which temporary tables perform better.It isn't an exact science but I find that table variables work well with very small volumes of data, derived tables have similar performance characteristics and tempdb tables have efficiency benefits on large volumes of data.The point about the YEAR() function is that a function on a field in the WHERE clause means that SQL cannot use any index on the OrderDate. The query will fall back to row-by-row processing.If there is a clustered index on OrderDate then BETWEEN '2006-01-01T00:00:00.000' AND '2006-01-01T23:59:59.997' is very efficient. The funny date format is ISO8601 format and SQL seems to cope with it much more consistently across different installations than other formats.Wed, 16 Jan 2008 16:57:34 GMTDavid.Poolehttp://www.sqlservercentral.com/Forums/Topic443400-1122-1.aspxBe careful in your reasoning: clearly in some cases you can replace a temporary table with a CTE and boost performance. This is what you are describing and quite well. However do not jump to blanket conclusions just yet - in many other cases you will dramatically hurt performance when you replace a temporary table with a CTE, and you fail to mention such cases.Cheers,Alex Kuznetsov, SQL Server MVP.Wed, 16 Jan 2008 15:24:26 GMTAlexander Kuznetsovhttp://www.sqlservercentral.com/Forums/Topic443400-1122-1.aspxI am going by how long the procedure or function takes to run, with resources available to the server being basically the same for the 'derived table' run and the 'table variable or temp table' run. Replacing derived table(s) with table variables or temp tables has cured lengthy run time problems for me. These problems occurr when the tables accessed have large numbers of records. I do add primary keys and / or indexes to the table variable or temp table when possible. This may be a significant part of the performance advantage I have observed.Wed, 16 Jan 2008 13:18:23 GMTReginald J Ray Jrhttp://www.sqlservercentral.com/Forums/Topic443400-1122-1.aspx[quote][b]Reginald J Ray Jr (1/16/2008)[/b][hr]Derived tables are cool as far as being able to write elegant code and reduce the quantity of code. I started using them for these reasons. I have been removing them since I realized that they are performance dogs for decent-sized tables. Table variables or temporary tables are much better from a performance aspect.[/quote]Have you tested them fully, or just gone by Estimated Cost? Table variables have a problem with the estimated cost in execution plans, and often give a lower number than what they would have if the server knew how many rows they would have before-hand (table variables don't have statistics in them, so they are estimated in cost as if they were 1 row).The reason I ask is because in a large number of cases, using Profiler and other tracking and testing tools, I've found that derived tables are sometimes faster and less resource intensive than table variables or temp tables, and sometimes are slower and more resource intensive. Your statement implies that they are always worse. Actual performance seems to depend mostly on indexing and the number of joins being used.Wed, 16 Jan 2008 12:21:05 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic443400-1122-1.aspxI'm also confused about the update statement...what does it have to do with the rest of the article?Wed, 16 Jan 2008 11:55:13 GMTsteitelbaumhttp://www.sqlservercentral.com/Forums/Topic443400-1122-1.aspxInteresting and informative.However, I was very curious about the last section of the article where an update statement is executed on a derived table. Where are the results stored? How do you get to the data after the update is completed?Good articles stir up good discussions as this article confirm. Keep these articles coming!Wed, 16 Jan 2008 11:21:55 GMTJoseph Jandalhttp://www.sqlservercentral.com/Forums/Topic443400-1122-1.aspxGood article. I use derived table a lot in my query.Wed, 16 Jan 2008 09:11:45 GMTLonerhttp://www.sqlservercentral.com/Forums/Topic443400-1122-1.aspxDerived tables are cool as far as being able to write elegant code and reduce the quantity of code. I started using them for these reasons. I have been removing them since I realized that they are performance dogs for decent-sized tables. Table variables or temporary tables are much better from a performance aspect.Wed, 16 Jan 2008 07:57:42 GMTReginald J Ray Jrhttp://www.sqlservercentral.com/Forums/Topic443400-1122-1.aspxThanks for the article. I know I don't use derived tables enough and now I feel armed to do so.Wed, 16 Jan 2008 07:33:10 GMTsing4youhttp://www.sqlservercentral.com/Forums/Topic443400-1122-1.aspx[quote][b]jldsql (1/16/2008)[/b][hr][b][i]Microsoft documentation [/i][/b]suggest that both derived tables and temporary table will first use memory cache if available.Jim[/quote]Jim... First, I [i]absolutely [/i]agree with what you said... But, I also believe that when you're going to blow up someone's article by dropping the "Microsoft documentation" bomb on them, the courteous thing to do is to provide at least one reference URL (or BOL hint) that supports your claim that "Microsoft said"... like I did in my first post on this thread.Wed, 16 Jan 2008 07:24:14 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic443400-1122-1.aspxMicrosoft documentation suggest that both derived tables and temporary tablewill first use memory cache if available.It then will use the temp database as needed in both cases depending on available cache.I think the advantages and disadvantages are more dependant on other issuessuch as indexing etc than the fact of using the temp database.This is a confusing and often misrepresented area of discussion.JimWed, 16 Jan 2008 06:48:23 GMTjldsqlhttp://www.sqlservercentral.com/Forums/Topic443400-1122-1.aspxThanks for this article. I learned something new and I will be using derived tables in the future. I would not consider myself an expert but I am wondering if you could handle this scenario using a case statement as follows:SELECT C.CustomerID, C.CompanyName, Sum(Case when YEAR(O.OrderDate) = 1996 then 1 else 0 end) AS TotalOrders FROM Customers C LEFT OUTER JOIN Orders O ON C.CustomerID = O.CustomerIDMy questions are:1. would this produce the correct results?2. are there efficiency/index issues with this approach?Thanks for your helpWed, 16 Jan 2008 06:47:19 GMTmcieslinskihttp://www.sqlservercentral.com/Forums/Topic443400-1122-1.aspxHey Prashant...You remember I said "don't let it dampen your writing spirit"? Here's why... like I said, you have some good information in your article and people have already cited your article as an example in at least one of the posts... see the following URL for what I'm talking about...[url]http://www.sqlservercentral.com/Forums/Topic441707-5-1.aspx#bm443531[/url]Wed, 16 Jan 2008 06:43:18 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic443400-1122-1.aspxHiYou could update on driven table but in other way,You were trying :Update T SET Id=0 FROM (SELECT * FROM tt1) AS Tbut you could use this as follow , will work fine :update t set id =0from tt1 as T where ...You could add you where clause too, you could do that with complex driven tables too.Mnaouar Ben Khelifawww.mnaouar.bestilan.comWed, 16 Jan 2008 06:24:18 GMTmkheli@gmail.comhttp://www.sqlservercentral.com/Forums/Topic443400-1122-1.aspxAs others have said, this is generally a good article and the technique is very valuable, but you have many errors. The characterization of the locking in the temp table example is simply wrong. It implies that access to tempdb is single threaded. It fails to mention that there are locks in the database providing the data (shared, but still locks). "Everything happens in memory" only if the data being referenced is already there.Wed, 16 Jan 2008 06:19:59 GMTJimi Meyerhttp://www.sqlservercentral.com/Forums/Topic443400-1122-1.aspxUsing columns in functions means SQL Server can no longer do a seek; instead it must do a scan.If there was a unique index on CustomerID, orddate.Say you had 1 million rows per year between 2000 and 2007 for customerid = 1234Say you wanted the counts for customerid 1234 for year 2007Using the function Year( orddate ) = 2007 means that all the rows from 2000 for that customerid in the index need to be searched because SQL Server does not know the result until it has read the data and performed the function on the column.Using &gt;= '20070101' and &lt; '20080101' allows SQL Server to just seek straight to the start 20070101 and read from there.Wed, 16 Jan 2008 05:55:28 GMTtony rogersonhttp://www.sqlservercentral.com/Forums/Topic443400-1122-1.aspx[quote][b]daninmanchester (1/16/2008)[/b][hr]pain_killer , the point about NOT using the Year() function is that it will make better use of any index.[/quote]Sorry, my english is very poor, and sometimes I don't understand what is the point of the problem, so please be patient to me:). In this case I had compare IO, TIME statistics and execution plans. YEAR() function and explicit date comparing in this case looks identical. So the question is: why use YEAR() function? I think that this looks clearer to me, and it uses only one parameter (2006 in this example).Wed, 16 Jan 2008 05:43:00 GMTpain_killerhttp://www.sqlservercentral.com/Forums/Topic443400-1122-1.aspxAnd why not using this sentence:SELECT C.CustomerID, C.CompanyName, COUNT(O.OrderID) AS TotalOrders FROM Customers C LEFT OUTER JOIN Orders O [b]ON C.CustomerID = O.CustomerID AND (YEAR(O.OrderDate) = 1996 OR O.OrderDate IS NULL)[/b] GROUP BY C.CustomerID, C.CompanyNameWed, 16 Jan 2008 05:37:54 GMTJosephttp://www.sqlservercentral.com/Forums/Topic443400-1122-1.aspxI'm afraid I'm with Jeff Moden and Tony Rogerson on this. I think this is a good example of needing to be careful and understand the implications of what you are saying, and to have covered your research well. There is plenty of info out there on this area from 'good' sources to allow you to draw a conclusion(s) that you can back up (right or wrong, subjectively of course ;) )That said though, I would like to mirror Jeff's comments of not letting the replys dampen your spirit. You have put yourself forward for peer review and from my own experiences its a great way to gain experience and knowledge, and to improve, though it can seem quite painful along the route :crying: .Wed, 16 Jan 2008 04:02:51 GMThumbleDBAhttp://www.sqlservercentral.com/Forums/Topic443400-1122-1.aspxI would tend to put some of the criteria in the FROM clause in a lot of cases rather than move straight to derived tables. Although in complex situations derived tables can help simplify things. But if the derived table is of particular value and reusable i will often put it in a view anyway.pain_killer , the point about NOT using the Year() function is that it will make better use of any index.Wed, 16 Jan 2008 03:03:56 GMTdaninmanchesterhttp://www.sqlservercentral.com/Forums/Topic443400-1122-1.aspx[quote][b]David A. Long (1/16/2008)[/b][hr]Great article.BTW here id the fix for the original query:SELECT C.CustomerID, C.CompanyName , COUNT(O.OrderID) AS TotalOrdersFROM Customers C LEFT OUTER JOIN Orders O ON C.CustomerID = O.CustomerID AND O.OrderDate &gt;= '19960101' AND O.OrderDate &lt; '19970101'GROUP BY C.CustomerID, C.CompanyNameAndy[/quote]I think it also do the job:SELECT C.CustomerID, C.CompanyName, COUNT(O.OrderID) AS TotalOrdersFROM Customers C LEFT OUTER JOIN Orders O ON C.CustomerID = O.CustomerID AND [b]YEAR(O.OrderDate) = 1996[/b][i]--AND O.OrderDate &gt;= '19960101' [/i][i]--AND O.OrderDate &lt; '19970101'[/i]GROUP BY C.CustomerID, C.CompanyNameOR:SELECT C.CustomerID, C.CompanyName, COUNT(O.OrderID) AS TotalOrders FROM Customers C LEFT OUTER JOIN Orders O ON C.CustomerID = O.CustomerID WHERE YEAR(O.OrderDate) = 1996 GROUP BY [b]ALL[/b] C.CustomerID, C.CompanyNameWed, 16 Jan 2008 02:34:15 GMTpain_killerhttp://www.sqlservercentral.com/Forums/Topic443400-1122-1.aspxDerived tables are a great performance optimisation tool but always check because the derived table approach is not always the best and temporary tables are sometimes a necessary approach.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.Statistics are held for the temporary table; you can also create indexes on temporary tables.I use derived tables a lot, well, one hell of a lot to be honest and they are a good structure but also look at CTE's which is what I'm using more - they can be referenced more like a virtual table in your queries.Tony.Wed, 16 Jan 2008 00:56:48 GMTtony rogersonhttp://www.sqlservercentral.com/Forums/Topic443400-1122-1.aspx[quote]...and everything happens in memory instead of a combination of memory and disk....while a temporary table exists until the server is brought down and also it uses lot of disk space than a derived table in the temdb database.[/quote]You wrote a very good introduction to the use of Derived Tables... but you really need to check your resources on the two statements above....First, Derived Tables are NOT memory only constructs... just like [i]any [/i]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...[url]http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k[/url]... and pay particular attention to where it states [b]"If memory is available, both table variables [size="3"]and temporary tables are created and processed while in memory [/size](data cache). "[/b]You also need to check Books Online for what the persistance of a Temp Table actually is... you [i]DON'T [/i]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!Wed, 16 Jan 2008 00:44:22 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic443400-1122-1.aspx"Theta Joins" also can be used.For the given example in the article, the results should match for this query:select c.*, o.*from customers as cleft join orders as o on c.customerid = o.customerid and year(o.orderdate) = 1996--where-- ...Wed, 16 Jan 2008 00:18:58 GMTcorey lawsonhttp://www.sqlservercentral.com/Forums/Topic443400-1122-1.aspxGreat article.We have been using the derived table as a great complex query design tool:Break the complex query into derived tables, generally by the specific table's restrictions.Then JOIN the individual derived table queries into the complex query.BTW here id the fix for the original query:SELECT C.CustomerID, C.CompanyName , COUNT(O.OrderID) AS TotalOrdersFROM Customers C LEFT OUTER JOIN Orders O ON C.CustomerID = O.CustomerID AND O.OrderDate &gt;= '19960101' AND O.OrderDate &lt; '19970101'GROUP BY C.CustomerID, C.CompanyNameThe last step is to evaluate for optimization. A suggestion is no try and not use functions in the where clause as that can cause any available index to be skipped.AndyWed, 16 Jan 2008 00:08:44 GMTDavid A. Longhttp://www.sqlservercentral.com/Forums/Topic443400-1122-1.aspxI try to use derived tables whenever possible, but I recently improved performance by changing a derived table to a temp table. It was large and joined on a non-index column so I found that a temp table with an index on the join fields.Tue, 15 Jan 2008 21:53:28 GMTRob Hershfieldhttp://www.sqlservercentral.com/Forums/Topic443400-1122-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/DerivedTables/61388/">SQL - Derived Tables</A>[/B]Tue, 15 Jan 2008 21:44:19 GMTPrashant Pandey