sp_fkeys performance differences in 2000 vs 2005 | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

sp_fkeys performance differences in 2000 vs 2005

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?
]]>