Sub query expressions vs. Joins.

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Brian Ellul » Sub query expressions vs. Joins.

57 posts, Page 6 of 6

«««2 3 4 5 6

Sub query expressions vs. Joins.

Rate Topic

Display Mode

Topic Options

Author

Message

cmapowers

Posted Monday, July 26, 2010 6:18 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, March 08, 2011 1:04 PM
Points: 5, Visits: 27

Thank you.

Post #959153

Posted Friday, January 20, 2012 12:30 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, January 23, 2012 4:17 PM
Points: 1, Visits: 3

Why not the following?

SELECT a.EmployeeID,a.Salary a.RActive
b.ManagerName
FROM Employees a (nolock)
,Managers b (nolock)
WHERE b.ManagerID = 1
AND a.ManagerID = b.ManagerID;

Post #1239213

spaghettidba

Posted Friday, January 20, 2012 12:48 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:43 AM
Points: 4,804, Visits: 8,091

IG (1/20/2012)

Why not the following?

SELECT a.EmployeeID,a.Salary a.RActive
b.ManagerName
FROM Employees a (nolock)
,Managers b (nolock)
WHERE b.ManagerID = 1
AND a.ManagerID = b.ManagerID;

NOLOCK can return inconsistent data (dirty reads, non repeatable reads and the like), but can also return duplicate data or no data at all for some rows. NOLOCK means that data is not locked at all, even from the query itself, that could end up reading the same row twice in case of a page split.

If you are ok with inconsistent data, then go on and use NOLOCK, just make sure your users are ok with inconsistent data as well.

Get your two-cent-answer quickly
The Spaghetti DBA

Post #1239216

SQLBoar

Posted Friday, January 20, 2012 8:59 AM

SSC Rookie

Group: General Forum Members
Last Login: Today @ 3:35 AM
Points: 25, Visits: 116

I pity any already confused developer who reads this article. As, despite the title, it isn't about sub-queries. What the author refers to are called Derived Tables, Table Expressions (which is where Common Table Expressions get their name from) or sometimes (almost equally confusingly) Inline Views. Any clues - the execution plan has no SEMI-JOIN in it. Refer to Chapter 6 "Subqueries, Expressions and Ranking Functions" of the IBG SS2008 T-SQL Querying book for reference.
Sorry to be negative, but one of Paul Randall's top myths busted is "sub-queries are always slower". And, this kind of confusing article just adds to the myth.
But, if it was titled Table Expressions vs Joins Exclamation

This DBA says - "It depends".

Post #1239544

Mad Myche

Posted Friday, January 20, 2012 12:32 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 07, 2013 7:35 AM
Points: 135, Visits: 416

I can honestly say that I have never seen subqueries used as such, these queries have always been of the inner join variety. And judging by the discussion from months ago, this will be something I remember in the future.

Anyways, I have seen a variation of the "standard" inner join

SELECT    EmployeeID, Salary, RActive, ManagerName
FROM    Employees
INNER JOIN Managers
    ON Manangers.ManagersID = Employees.ManagerID
WHERE    Employees.ManagerID = 3

In which the WHERE is eliminated and is replaced with using an AND on the JOIN portion

SELECT    EmployeeID, Salary, RActive, ManagerName
FROM    Employees    
INNER JOIN Managers
    ON    Manangers.ManagersID = Employees.ManagerID
    AND    Managers.ManagerID = 3

On larger datasets I have seen better performance with the second version, but on smaller I see no performance advantage.

I did do a test on a Products:Categories relationship on one of the DBs I have mounted, where there most populated category had about 50 items. I used the subset and the 2 variations of the join on both an '08 box and a 2K instance.
The results were fairly equal, with the subset at 34% and the joins at 33% apiece. This was consistent between both server instances

Director of Transmogrification Services

Post #1239684

jcb

Posted Monday, January 23, 2012 6:54 AM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 2:15 PM
Points: 1,176, Visits: 674

After some years of observation I found if 2 queries are similar the optimizer ill process both in a similar way.
It means its likely there's no difference in performance for most joins x sub-queries scenarios.

And also it's mean
If you found differente query plans it's likely the queries are different and that's the case here.

Put yourself in the compiler place.

You can read the second query as:
fetch manager-1 (a single clustered index seek)
now fetch me all employers managed by manager-1 (40k index seek)

First query reads as:
fetch me all employers managed by manager-1 (40k index seek)--same cost as the other way
and for each fetched employer seek if there's a manager for him and fetch it as well.
Since its not a strict inner join its possible there are rows where the id is null.

The compiler still try (futile) to optimize by using a lazy spool.

I cannot test by now but I guess a FK can improve the subquery.

Post #1240140

UKGav

Posted Monday, January 23, 2012 9:33 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 04, 2013 4:03 AM
Points: 34, Visits: 152

I had to use subqueries for 1-to-many tables where I needed to only return only 1 row for each record (Select top 1 date from InnerTable joined to OuterTable order by date desc) as [LastDate], thereby the results become 1-to-1.

This was noticable slow; but I don't believe there is an alternative (*??*) with JOINs without either missing out records from the primary table where the is no join or returning too many rows (LEFT JOIN) with a lot of null values from forgein table.

Might CTE's be the answer instead? I keep meaning to learn about them..

- Gav.

Post #1240294

« Prev Topic | Next Topic »

57 posts, Page 6 of 6

«««2 3 4 5 6

Permissions