在SQL Server 2008 R2中运行的游标比SQL Server 2000慢100倍?

I am upgrading an application from SQL Server 2000 to SQL Server 2008 R2, and running into timeout issues with stored procedures that use cursors. For instance, one SP that handled 500 rows per second in SQL Server 2000 is crawling along at 5 about rows per second (and 100% CPU) in SQL Server 2008. This is in a VM, but memory does not seem to be an issue.

我正在将一个应用程序从SQL Server 2000升级到SQL Server 2008 R2，并运行使用游标存储过程的超时问题。例如，SQL Server 2000中每秒处理500行的一个SP在SQL Server 2008中以每秒大约5行(和100% CPU)的速度爬行。这是在VM中，但是内存似乎不是问题。

I've tried resetting statistics, and rebuilding indices, with no effect. I can rewrite the SPs to avoid using cursors, but would prefer not to increase the scope to include those rewrites.

我尝试过重置统计数据和重建指数，但没有效果。我可以重写jsp以避免使用游标，但不希望增加包含重写的范围。

Has anyone encountered this issue before?

有人以前遇到过这个问题吗?

Here is a simple example of an SP that takes about 100 times as long after the upgrade. In my test on SQL Server 2008 R2, it takes 12 seconds to process 4000 rows.

这里有一个SP的简单示例，它在升级后需要大约100倍的时间。在我的SQL Server 2008 R2测试中，处理4000行需要12秒。

--set ids by alphabetical order
declare cursor1 CURSOR
FOR

select id from form_import_current_data
where master_formulary_id = @Master_Formulary_ID
order by description, description2
open cursor1

declare @id int
declare @id_counter int
set @id_counter = 1

FETCH NEXT FROM cursor1 INTO @id

while (@@FETCH_STATUS <> -1)
BEGIN
    IF(@@FETCH_STATUS <> -2)
    BEGIN
        update form_import_current_data
        set  id = @id_counter
        where master_formulary_id = @Master_Formulary_ID
          and id = @id

        set @id_counter = @id_counter + 1
    END

    FETCH NEXT FROM cursor1 INTO @id
END

CLOSE cursor1
DEALLOCATE cursor1

1 个解决方案

#1

After reading all the suggestions, I ended up doing some old trick and it worked miracles!

在读完所有的建议后，我最终做了一些老把戏，它产生了奇迹!

I had this cursor which was taking almost 3 mins to run, while the enclosing query was instant. I have other databases with more complex cursors that were only taking 1 second or less, so I ruled out the global issue on using cursors. My solution:

我有这个游标，运行大约需要3分钟，而包含的查询是即时的。我有其他更复杂的游标只需要1秒或更短时间的数据库，所以我排除了使用游标的全球性问题。我的解决方案:

Detach the database in question, but ensure you tick Update Statistics. Attach the database and check performance This seems to help optimize all the performance parameters without the detailed effort. I am using SQL Express 2008 R2.

分离有问题的数据库，但是要确保更新统计信息。附加数据库并检查性能，这似乎有助于优化所有性能参数，而无需进行详细的工作。我正在使用SQL Express 2008 R2。

Would like to know your experience.

我想知道你的经验。

#1