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 «««23456

Sub query expressions vs. Joins. Expand / Collapse
Author
Message
Posted Monday, July 26, 2010 6:18 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 8, 2011 1:04 PM
Points: 5, Visits: 27
Thank you.
Post #959153
Posted Friday, January 20, 2012 12:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Friday, January 20, 2012 12:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:05 AM
Points: 5,034, Visits: 10,564
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.


--
Gianluca Sartori

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1239216
Posted Friday, January 20, 2012 8:59 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 7:32 AM
Points: 28, Visits: 145
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


This DBA says - "It depends".
Post #1239544
Posted Friday, January 20, 2012 12:32 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, September 11, 2014 11:46 AM
Points: 137, Visits: 451
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
Posted Monday, January 23, 2012 6:54 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 11:29 AM
Points: 2,734, Visits: 939
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
Posted Monday, January 23, 2012 9:33 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 11:39 AM
Points: 34, Visits: 185
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 »

Add to briefcase «««23456

Permissions Expand / Collapse