Join performance issue moving from 2000 to 2005

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)

Join performance issue moving from 2000 to 2005

Author

Topic

pvandermn
Starting Member

8 Posts

Posted - 2008-10-06 : 13:18:28

I have a query which runs in seconds in SQL 2000, but clocks and never finishes in SQL 2005. I have tracked the issue down to the join.

OLD WHICH DOESN'T WORK IN 2005 (bolded text is the offending issue)
FROM vwTranHeader h
INNER JOIN vwTranDetail d
INNER JOIN vwTranHeader h2 ON d.TranID = h2.TranID
INNER JOIN Learners l ON l.LearnerID = d.LearnerID
LEFT JOIN Courses co
INNER JOIN Subcategories sc
INNER JOIN Categories c ON c.CategoryID = sc.CategoryID ON co.SubcategoryID = sc.SubCategoryID ON d.CourseID = co.CourseID ON d.TranID = h.TranID

IF I CHANGE TO THIS IT RUNS FINE IN 2005
FROM vwTranHeader h
INNER JOIN vwTranDetail d ON d.TranID = h.TranID
INNER JOIN vwTranHeader h2 ON d.TranID = h2.TranID
INNER JOIN Learners l ON l.LearnerID = d.LearnerID
LEFT JOIN Courses co ON d.CourseID = co.CourseID
INNER JOIN Subcategories sc ON co.SubcategoryID = sc.SubCategoryID
INNER JOIN Categories c ON c.CategoryID = sc.CategoryID

Can anyone tell me why by moving the ON portion of the join there is a difference, and if there is anything I can do to configure SQL 2005 to allow the original query to run? We are just migrating, and I know there are a lot of examples of the former in our code. I'd HATE to have to rework everything. Thanks for any advice!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-06 : 13:26:27

i dont think you've posted the right query. As the query in red is syntactically wrong. There are some joins missing on conditions which are all given at end. i dont think this will parse at all.

pvandermn
Starting Member

8 Posts

Posted - 2008-10-06 : 13:32:16

Before I joined this job, I wouldn't have thought to write a query with that syntax either, or thought it would work, but that syntax does work in SQL 2000. And I have found that the developers here have written a lot of queries that way which all work in SQL 2000. So I am hoping someone else has encountered this.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-06 : 13:53:41

Haven't seen anyone with that syntax before. It didn't work for sql 2000:


select a.* 
from test1 a 
inner join test1 b 
inner join test1 c 
on a.Cell = b.Cell on b.cell = c.Cell


The column prefix 'a' does not match with a table name or alias name used in the query.

pvandermn
Starting Member

8 Posts

Posted - 2008-10-06 : 14:04:08

We have narrowed the offending part of the original join to just one piece out of place. Why it ran in 2000 and not in 2005 who knows - maybe SQL Server is getting better at helping us developers. I moved the struck-out code below to the bolded position, and it works fine.

FROM vwTranHeader h
INNER JOIN vwTranDetail d ON d.TranID = h.TranID
INNER JOIN vwTranHeader h2 ON d.TranID = h2.TranID
INNER JOIN Learners l ON l.LearnerID = d.LearnerID
LEFT JOIN Courses co
INNER JOIN Subcategories sc
INNER JOIN Categories c ON c.CategoryID = sc.CategoryID ON co.SubcategoryID = sc.SubCategoryID ON d.CourseID = co.CourseID ~~ON d.TranID = h.TranID~~

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-06 : 14:50:39

OOh... nested joins...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources