November 3, 2005 at 2:02 pm
I am new to SQL Server. I'd like to know whether there are any performance implications of using the following SELECT syntaxes. I am getting the same result, but don't have enough data to test performance. I am getting all Contacts linked to a particular Account via the TRLNK001 linking table.
Syntax 1:
Select RTrim(CONTACT.LName) + ', ' + CONTACT.FName AS 'Contact Name' FROM CONTACT, ACCOUNT, TRLNK001
Where ACCOUNT.FormID = TRLNK001.ACCOUNT AND CONTACT.FormID = TRLNK001.CONTACT AND TRLNK001.ACCOUNT=2
Order By CONTACT.LName
Syntax 2:
SELECT RTrim(CONTACT.LName) + ', ' + CONTACT.FName AS 'Contact Name' FROM CONTACT
JOIN TRLNK001 ON CONTACT.FormID = TRLNK001.CONTACT
JOIN ACCOUNT ON ACCOUNT .AcctID = TRLNK001.ACCOUNT
WHERE ACCOUNT .AcctID = 2
Order By CONTACT.LName
November 5, 2005 at 6:31 am
syntax 2 has more chance to perform better and it is more readable.
since you use inner joins, you could trim it to
SELECT RTrim(CONTACT.LName) + ', ' + CONTACT.FName AS 'Contact Name' FROM dbo.CONTACT CONTACT
JOIN dbo.TRLNK001 TRLNK001 ON CONTACT.FormID = TRLNK001.CONTACT
JOIN dbo.ACCOUNT ACCOUNT ON ACCOUNT .AcctID = TRLNK001.ACCOUNT
AND ACCOUNT.AcctID = 2
Order By CONTACT.LName
just a minor remark: try to specify the owner of an object, so the processor hasn't extra work on permission checking and unnecessary recompilations
November 7, 2005 at 9:08 am
Thanks!
November 7, 2005 at 10:14 am
Happy to help
November 8, 2005 at 2:21 pm
Jo,
I have previously seen references to placing a filter on the join instead of in the where clause, exactly as you pointed out to Mario. The assertion is that placing the reference on the join will perform better. I am sure the assertion is probably correct, but I have not seen an explanation of why it performs better.
Why does it perform better?
Is there anything in books online, or elsewhere, that discusses the theory behind this?
Thanks in advance
Wayne
November 9, 2005 at 1:25 am
till I find some reference
The reason why a restriction on the join probably performs better because it eliminates (some) rows SQL server has to retrieve / compare. This results in smaller "tables" to join further on to.
The where clause normally takes place after the joins are made (acting like a filter), but the optimizer is quite smart and will transpone some of the where statements into restricting join statements.
*update*
from the books online
The rows selected by a query are filtered first by the FROM clause join conditions, then the WHERE clause search conditions, and then the HAVING clause search conditions. Inner joins can be specified in either the FROM or WHERE clause without affecting the final result.
Outer join conditions, however, may interact differently with the WHERE clause search conditions, depending on whether the join conditions are in the FROM or WHERE clause. Therefore, the ability to specify Transact-SQL outer joins in the WHERE clause is not recommended, is no longer documented, and will be dropped in a future release.
November 9, 2005 at 8:52 am
Excellent! Just what I was looking for. Thanks Jo.
Wayne
November 9, 2005 at 9:46 am
The statement "Inner joins can be specified in either the FROM or WHERE clause without affecting the final result" makes it look like there is no difference in the examples I posted? In other words, there is no performance advantage to using the JOIN syntax vs WHERE.
November 9, 2005 at 10:16 am
Without affecting the final result -> means the same result at the end <> the same path used to get the same result.
inner join only red and marbles-> collect all marbles from the box red marbles
where red and marbles->collect all red items, collect all marbles and then siphon to get all red marbles
Same result: all the red marbles
There can be a change in result when using left joins instead of where =* (or is it *= ?)
November 9, 2005 at 10:42 am
I get it. Thanks.
November 16, 2005 at 7:58 am
"Outer join conditions, however, may interact differently with the WHERE clause search conditions, depending on whether the join conditions are in the FROM or WHERE clause. Therefore, the ability to specify Transact-SQL outer joins in the WHERE clause is not recommended, is no longer documented, and will be dropped in a future release."
Here is a prime example of this that bit me yesterday. The first several statements are setting up a simple set of tables. The last two lines show how a 'WHERE' clause behaves differently than the same cause on the OUTER JOIN.
I found the solution, but I am still puzzling over exactly why the behavior is different. Your thoughts?
Wayne
CREATE TABLE TblSample (Label VARCHAR(200),
Color VARCHAR(10))
GO
INSERT INTO TblSample (Label, Color)
SELECT 'Alice', 'Red' UNION ALL
SELECT 'Bob', 'Red' UNION ALL
SELECT 'Cathy', 'Red' UNION ALL
SELECT 'David', 'Blue' UNION ALL
SELECT 'Ellen', 'Blue' UNION ALL
SELECT 'Fred', 'Blue' UNION ALL
SELECT 'Gale', 'Yellow' UNION ALL
SELECT 'Harold', 'Yellow' UNION ALL
SELECT 'Irene', 'Yellow'
GO
-- this is our sample population and their favorite color
CREATE TABLE TblSampleQuota (SampleLabel VARCHAR(200),
Color VARCHAR(10))
GO
INSERT INTO TblSampleQuota (SampleLabel, Color)
SELECT 'Alice', 'Red' UNION ALL
SELECT 'Bob', 'Red' UNION ALL
SELECT 'Cathy', 'Red' UNION ALL
SELECT 'David', 'Blue' UNION ALL
SELECT 'Ellen', 'Blue' UNION ALL
SELECT 'Fred', 'Blue' UNION ALL
SELECT 'Gale', 'Yellow' UNION ALL
SELECT 'Harold', 'Yellow' UNION ALL
SELECT 'Irene', 'Yellow'
GO
-- every person has a color that also appears in the quota table
CREATE TABLE TblQuotas (Color VARCHAR(10),
Threshhold INT,
Counter INT)
GO
DELETE FROM TblQuotas
INSERT INTO TblQuotas (Color, Threshhold, Counter)
SELECT 'Red', 2, 2 UNION ALL
SELECT 'Blue', 2, 2 UNION ALL
SELECT 'Yellow', 2, 2
GO
-- All quotas have been filled, counter = threshhold
CREATE VIEW VWClosedSampleQuotaView AS
SELECT DISTINCT SampleLabel
FROM TblSampleQuota SQ
INNER JOIN TblQuotas Q ON SQ.Color = Q.Color
WHERE Q.ThreshHold <= Q.Counter
-- SELECT * FROM VWClosedSampleQuotaView shows that everyone is "closed"
SELECT TOP 1 Label
FROM (SELECT Label, Color
FROM TblSample
) A
LEFT OUTER JOIN VWClosedSampleQuotaView X ON A.Label = X.SampleLabel
WHERE X.SampleLabel IS NULL -- WORKS as expected, by returning nothing
-- AND X.SampleLabel IS NULL -- DOES NOT WORK, returns a person who is already "closed"
November 16, 2005 at 11:05 am
This is because the order of execution. JOINS before where, where before having...
select Label,Color,SampleLabel
from TblSample
LEFT OUTER JOIN VWClosedSampleQuotaView X
ON TblSample.Label = X.SampleLabel
gives
Label Color SampleLabel
Alice Red Alice
Bob Red Bob
Cathy Red Cathy
David Blue David
Ellen Blue Ellen
Fred Blue Fred
Gale Yellow Gale
Harold Yellow Harold
Irene Yellow Irene
Apply where clause SampleLabel-> no records with SampleLabel NULL
select Label,Color,SampleLabel
from TblSample
LEFT OUTER JOIN VWClosedSampleQuotaView X
ON TblSample.Label = X.SampleLabel
AND X.SampleLabel IS NULL /*only where X.SampleLabel is null*/
Alice Red NULL
Bob Red NULL
Cathy Red NULL
David Blue NULL
Ellen Blue NULL
Fred Blue NULL
Gale Yellow NULL
Harold Yellow NULL
Irene Yellow NULL
Because there are no records in the view VWClosedSampleQuotaView
with SampleLabel IS NULL
-> return NULL values for the "missing" columns /*expected behaviour of left join*/
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply