SQL Server Performance

sp_fkeys performance differences in 2000 vs 2005

Discussion in 'SQL Server 2005 General Developer Questions' started by t1pimp, Feb 24, 2006.

  1. t1pimp New Member

    I have a stored procedure that utilizes sp_fkeys to assist dropping and recreating the fkey references on our database so we can truncate all the tables without regards to parent/child relationships. When I run this procedure in SQL 2000 against an empty database it occurs in 4 seconds. When I run it against a SQL 2005 database it takes 28 MINUTES.

    I took sp_fkeys from SQL 2000 and created my own sp_Paulsfkeys proc. When I execute it against any table it returns results in less than a second. When I run sp_fkeys (the SQL 2005 version) it takes 8 seconds.

    Since I'm using creation scripts from a database that was developed on SQL 2000, I'm wondering if there is a different way of adding foreign key constraints that I'm missing that would help with performance. How come the 2005 version takes such a significantly longer time to execute?

Share This Page