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 12»»

Aggregate Queries Expand / Collapse
Author
Message
Posted Tuesday, November 29, 2005 8:28 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 7:10 AM
Points: 769, Visits: 244
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/kKellenberger/aggregatequeries.asp

Aunt Kathi
Microsoft
(Former SQL Server MVP)
Post #240597
Posted Monday, December 19, 2005 6:06 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 10:04 AM
Points: 585, Visits: 53
nice one aunt kathi
Post #245131
Posted Monday, December 19, 2005 6:30 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, February 8, 2006 5:36 AM
Points: 52, Visits: 1
Very nice, simple, and to the point.  Appreciate it!! 
Post #245137
Posted Monday, December 19, 2005 7:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 3, 2014 7:18 AM
Points: 44, Visits: 129
It was helpful, thank you. I am wondering about your comments at the end of the article. I think the derived table is easier to understand but did you mean that or did you mean the derived table would be more efficient? Thanks.
Post #245144
Posted Monday, December 19, 2005 7:34 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 2, 2007 7:06 AM
Points: 254, Visits: 1

Very helpful.  I have struggled with aggregate queries in the past.  Now I can just use your instructions!!

Thanks,

Gladys 

Post #245152
Posted Monday, December 19, 2005 8:28 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 7:10 AM
Points: 769, Visits: 244

Thanks, everyone.

From what I understand the derived table is more effiecient.  It took me a while to understand them, though.  The instructor I had when working on my degree taught us to do everything with sub-queries.  The class was actually on PL SQL (Oracle's version) and some of the features are different.

 



Aunt Kathi
Microsoft
(Former SQL Server MVP)
Post #245197
Posted Monday, December 19, 2005 8:31 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 3, 2014 7:18 AM
Points: 44, Visits: 129
Thanks so much! I like derived tables also so it's nice to know they are efficient also. 
Post #245199
Posted Monday, December 19, 2005 9:31 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 27, 2012 7:28 AM
Points: 18, Visits: 46
Thank you! Easy to digest than BOL for a newbie like me.
Post #245221
Posted Monday, December 19, 2005 1:02 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 14, 2008 12:28 PM
Points: 49, Visits: 4
Marie, in this case, I think the derived table just makes it more obvious what the query is doing than the "WHERE...IN" version. SQL should only have to actualize the recordset once either way though the join might be a little more effecient; I'd have to test a few cases to see if it mattered. Note that I generally avoid the IN and NOT IN constructs where possible since it usually results in the query optimizer generating a giant "OR" block to test each condition.

When you use a correlated subquery (essentially a sub query that uses fields from outside its parenthesis), SQL may execute the statement once per row that it needs to filter. In these cases, joining a derived table should almost always be more efficient, though this isn't the example in the article.

There's one other case that she didn't mention... What if you were doing a query for last order by Customer Name and your system doesn't have a unique key on name? I don't know of a way to do this without either setting up a temp table before running your aggregate query, or using a derived table. Maybe this only comes up when the schema is poorly defined, but I have found it relevant when querying third-party databases. (Specific case in point: we have such a database that allows SSN's to be duplicated in an employee table; when looking up aggregate data per employee, I have to get the most recent employee id number for each SSN and use it when agregating data for each employee.)


Matthew Galbraith



Post #245312
Posted Wednesday, December 21, 2005 7:42 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 3, 2014 7:18 AM
Points: 44, Visits: 129

Thank you Matthew, your comments about correlated subqueries were also helpful, I had added just that type of subquery to a procedure and watched it almost grind to a halt. Went back and made it a derived table and it's flying again.

Marie

Post #245844
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse