July 2, 2004 at 8:42 am
I have a stored procedure which accesses data from a number of tables each having several hundred thousand rows.
On SQL7 the stored procedure uses clustered index seeks and index seeks to obtain no more than 2 rows per table and the execution time is < 1 second.
On SQL2000 the same stored procedure uses a totally different execution plan which use clustered index scans, index scans and a hash match and access hundreds of thousands of rows with an execution time of 20 seconds.
I have updated the statistics and rebuilt indexes without any effect.
Has anyone any suggestions on why the execution plan should change so adversly when moving from SQL7 to SQL2000?
July 5, 2004 at 8:00 am
This was removed by the editor as SPAM
July 6, 2004 at 8:24 am
Check whether the indexes on the tables are the same - hash match and clustered index scan both indicate that no useful index has been found.
July 8, 2004 at 10:24 pm
I have seen SQL2000 problem with comparisons between Unicode (Nvarchar) and regular varchar after conversion from 7 (they did implicit conversion the opposite way).
Another one might be Collation - default collation of a new SQL2000 install is different than converting from SQL7. Although usually this causes errors not slowness.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply