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 Wednesday, January 16, 2008 9:11 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, September 11, 2014 10:39 AM
Points: 2,805, Visits: 3,068
Good article. I use derived table a lot in my query.
Post #443754
Posted Wednesday, January 16, 2008 11:21 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 8, 2014 10:24 AM
Points: 10, Visits: 357
Interesting 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!
Post #443876
Posted Wednesday, January 16, 2008 11:55 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 12, 2008 1:58 PM
Points: 16, Visits: 81
I'm also confused about the update statement...what does it have to do with the rest of the article?
Post #443903
Posted Wednesday, January 16, 2008 12:21 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
Reginald J Ray Jr (1/16/2008)
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.

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.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #443922
Posted Wednesday, January 16, 2008 1:18 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 21, 2013 8:12 AM
Points: 17, Visits: 55
I 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.
Post #443970
Posted Wednesday, January 16, 2008 3:24 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, May 24, 2014 7:58 PM
Points: 219, Visits: 824
Be 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.
Post #444036
Posted Wednesday, January 16, 2008 4:57 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:49 AM
Points: 2,902, Visits: 1,818
This 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.


LinkedIn Profile
Newbie on www.simple-talk.com
Post #444063
Posted Thursday, January 17, 2008 3:45 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 10, 2009 10:50 PM
Points: 120, Visits: 211
derived table concept is very usefull from seeing performance issues

it is very good article

i was using derived table , but my current company not advicing me to use, since here using temp tables

Post #444199
Posted Thursday, January 17, 2008 10:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 25, 2010 9:33 AM
Points: 1, Visits: 7
Hi,

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)
Post #444458
Posted Thursday, January 17, 2008 10:40 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 12, 2008 1:58 PM
Points: 16, Visits: 81
Christophe, 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.
Post #444464
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse