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 server
  • 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在每个版本中都会有显着的改进。无论何时在数据库版本之间进行迁移，您都应该查看新版本中所做的更改。

    链接地址: http://www.djcxy.com/p/95650.html

    上一篇: SQL Server，如何创建一个没有数据丢失的表后自动增量？

    下一篇: 查询SQL Server 2000和SQL Server 2005之间的性能差异