Order of tables in JOINS - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Order of tables in JOINS

Author

Topic

amsqlguy
Yak Posting Veteran

89 Posts

Posted - 2009-05-22 : 09:53:47

Guys,

I have two queries which produce the same results, as you would notice there is difference in the way the joins are done in each query. The first query goes from the lesser row table SCHOOL to EMPLOYEE table. I have noticed that QUERY1 gives better performance since doesnt do full table scan on large table EMPLOYEE. Below is the summary of the indexes. The question that I have is correct to conclude that lesser row column table should be the first table of the join.

S.STATE_ID - PK CLUSTERED INDEX
H.SCHOOL_ID - PK CLUSTERED INDEX
D.DEPARTMENT_ID - PK CLUSTERED INDEX
E.EMPLOYEE_ID - PK CLUSTERED INDEX

H.STATE_ID - NON CLUSTERED INDEX
D.SCHOOL_ID - NON CLUSTERED INDEX
E.DEPARTEMENT_ID - NON CLUSTERED INDEX

ROW COUNT

STATE - 52
SCHOOL - 67000
DEPARTEMENT - 7200
EMPLOYEE - 4370000

QUERY1
SELECT S.STATENAME, H.SCHOOLNAME, D.DEPARTMENTNAME, E.EMPLOYEENAME
FROM STATE S INNER JOIN SCHOOL H ON S.STATE_ID = H.STATE_ID
INNER JOIN DEPARTMENT D ON D.SCHOOL_ID = H.SCHOOL_ID
INNER JOIN EMPLOYEE E ON E.EMPLOYEE_ID = D.EMPLOYEE_ID
WHERE S.SCHOOLNAME = 'UND'
GO

QUERY2
SELECT S.STATENAME, H.SCHOOLNAME, D.DEPARTMENTNAME, E.EMPLOYEENAME
FROM STATE S INNER JOIN SCHOOL H ON S.STATE_ID = H.STATE_ID
INNER JOIN DEPARTMENT D ON D.SCHOOL_ID = H.SCHOOL_ID
INNER JOIN EMPLOYEE E ON E.EMPLOYEE_ID = D.EMPLOYEE_ID
WHERE S.SCHOOLNAME = 'UND'
GO

Any suggestions inputs would help.

Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-05-22 : 09:58:01

There is no difference between posted queries.
Or maybe i am blind.

No, you're never too old to Yak'n'Roll if you're too young to die.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-22 : 10:04:12

i have 1 pair of eyes with 2 pair of glasses + a text editor that can perform comparison. Maybe i am too old and my eye sight deceiving me and maybe the text editor has bug . . . but that 2 query sure looks the same to me

KH
[spoiler]Time is always against us[/spoiler]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-22 : 13:13:51

It is always a good idea to put the most restrictive "thing" first; whether that is a smaller table or a restricting join condition. With SQL 2005 up it is harder to control how sql does the joins because of parallel execution. But, it does a pretty good job of figuring these things out. If you are getting different performance just by moving the table order around you might want to check on your statistics. Sometimes, they get out of whack and that can cause havoc with your queries/execution plans.

amsqlguy
Yak Posting Veteran

89 Posts

Posted - 2009-05-22 : 13:45:07

My bad the second query is

SELECT S.STATENAME, H.SCHOOLNAME, D.DEPARTMENTNAME, E.EMPLOYEENAME
FROM EMPLOYEE E INNER JOIN DEPARTMENT D ON E.EMPLOYEE_ID = D.EMPLOYEE_ID
INNER JOIN SCHOOL H ON H.SCHOOL_ID = D.SCHOOL_ID
INNER JOIN STATE S ON S.STATE_ID = H.STATE_ID
WHERE S.SCHOOLNAME = 'UND'
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources