查询性能在不同版本的SQL Server中是不同的 [英] Is Query Performance is different in different versions of SQL Server
问题描述
你好朋友。
我在我的存储过程中为3个不同的表发出了3个更新查询。每个表包含近2,00,000条记录,所有记录都必须更新。我正在使用索引来加速性能。它与 SQL Server 2008 完全兼容。存储过程只能执行 12到15分钟。 (在所有三个表中,在1秒内更新近1000行)
但是当我使用 SQL Server 2008 R2 运行相同的场景时,则存储过程获得更多时间来完成执行。它大约 55到60分钟。 (在所有三个表中,在1秒内更新几乎100行)。我找不到任何理由或解决方案。
我也用 SQL Server 2012 测试了相同的场景。但结果与上面相同。
这是我在存储过程中的3表更新查询。
Hello Friends.
I have fired 3 update queries in my stored procedure for 3 different tables. Each table contains almost 2,00,000 records and all records have to be update. I am using indexing to speed up the performance. It quite working well with SQL Server 2008. stored procedure gets only 12 to 15 minutes to execute. (updates almost 1000 rows in 1 second in all three tables)
But when I run same scenario with SQL Server 2008 R2 then stored procedure get more time to complete execution. its about 55 to 60 minutes. (updates almost 100 rows in 1 second in all three tables). I couldn't find any reason or solution for that.
I have also tested same scenario with SQL Server 2012. but result is same as above.
Here is my 3 table update query in stored procedure.
if (select COUNT(*) from table where conditions)>0
begin
update table1
set Coulmnname= @ColumnName
where Conditions
update table2
set Coulmnname= @ColumnName
where Conditions
update table3
set Coulmnname= @ColumnName
where Conditions
end
请给我建议。
Please give me suggestions.
推荐答案
试试这个.. :)
更新行的最快方法在SQL Server的大表中 [ ^ ]
try this.. :)
Fastest Way to Update Rows in a Large Table in SQL Server[^]
这篇关于查询性能在不同版本的SQL Server中是不同的的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!