| Ken Sturgeon 2006-02-28, 8:23 pm |
| I have a database creation script and stored procedure creation script that
I've used numerous times in SQL 2000; I've used the same scripts to build a
database in SQL 2005. There are a number of stored procedures that support a
scheduled batch data load to populate the database with data from a
different system; there's also a procedure that clears out existing data
should the need arise.
The procedure that clears the database invokes a call to the system stored
procedure sp_fkeys. When I call this procedure against a table in SQL 2000 I
get results anywhere from 3 to 5 times faster than when I execute it in SQL
2005.
For example this statement, " Execute sp_fkeys 'code' ", returns results in
2 seconds in SQL 2000 but in SQL 2005 it takes 9 seconds. I've focused on
sp_fkeys simply because as we researched the matter we noticed the execution
time difference between the two versions.
I believe our problem is deeper than just the fact that sp_fkeys is taking
longer. Our overall process to clear and reload a very small set of test
data has gone from 51 seconds in SQL 2000 to 28:43 in SQL 2005. as we're
also seeing much longer execution times with SELECT INTO statements. INSERT
INTO statements that do not specifically list the columns have also shown
themselves to be suspect.
At this point I'm somewhat clueless as to what the issue(s) may be. I've
used the SQL Upgrade Advisor but didn't find it to be of much help other
than the fact that it does have a good help file that lists several items to
look for. I've reviewed everything in the "Database Engine Upgrade Issues"
section and just can't seem to pinpoint anything in particular that would
cause my dilemma.
I'd appreciate any help that anyone can offer regarding this issue as we
really want to announce that we're SQL 2005 compliant. We have a new client
that wants to purchase SQL 2005 but we just can't claim victory in this
matter until we resolve the performance problems.
Thanks very much,
Ken Sturgeon
|