Query performance difference between SQL Server 2000 and SQL Server 2005

recently been moving a legacy database in SQL Server 2000 to SQL Server 2005.

I did following things:

• Restore SQL 2000 DB on SQL 2005 serv开发者_如何转开发er
• Set compatibility mode of the new DB to SQL 2005
• Rebuilt all indexes, updated all stats, on all tables

One of the issues I stumbled upon was the fact that one query seemed to take ages (actually, like 5 minutes) on the restored SQL 2005 database, while in the original SQL 2000 database it took 3 seconds.

This is the specific query:

SELECT *
FROM Users
LEFT OUTER JOIN STAFF_Movement
ON Users.USERS_ID = STAFF_Movement.MOVEM_USERS_ID
WHERE
(
 (
  STAFF_Movement.MOVEM_Date = (
               SELECT MAX(MOVEM_Date)
               FROM STAFF_Movement sm
                   WHERE sm.MOVEM_USERS_ID = Users.USERS_ID
              )
 )
OR
 (
  STAFF_Movement.MOVEM_Date IS NULL
 )
)
AND (Users.USERS_IsUser = 1)
ORDER BY Users.USERS_LastName, Users.USERS_FirstName

After some headscratching and trial and error, I found out that when I rewrote this query like following on the SQL 2005 DB, it executed really fast again, as it did on SQL 2000:

SELECT *
FROM Users u1
LEFT JOIN  STAFF_Movement sm1 ON u1.USERS_ID = sm1.MOVEM_USERS_ID
AND
(
(
sm1.MOVEM_Date = (
                SELECT MAX(sm.MOVEM_Date)
                FROM STAFF_Movement sm
                WHERE sm.MOVEM_USERS_ID = u1.USERS_ID
                )
)
OR
(
sm1.MOVEM_Date IS NULL
)

)

WHERE 
(u1.USERS_IsUser = 1)
ORDER BY u1.USERS_LastName, u1.USERS_FirstName

So, basically, what I did was take the criteria out of the where-clause, and integrate it into the LEFT JOIN logic.

So, although I found the solution myself, I still have following questions unanswered:

• why is there a difference between SQL 2000 and SQL 2005 regarding this query ?
• why is there a difference in performance when putting the filter criteria in the JOIN logic, as opposed to in the WHERE clause ?

One important thing I must add: All primary keys in this database are of datatype GUID (not my design, ... I'd personally never use GUIDs). Might this affect performance ?

Thanks for any replies.

Mathieu

MSSQL Server undergoes significant improvements in every version.whenever you are migrating between the database versions you should take a look at the changes that have been made in the newer versions.