插入/更新150000条记录花费了一个多小时 [英] Insert/ Update 150000 records took more than one hour
问题描述
大家好，
我正在使用SQL Server2008.
我有一个窗口服务，用于在表中插入/更新记录.
我正在为每个记录调用存储过程.
我的表有一个名为ID [uniqueidentifier]的列，而我的存储过程就像这样.对于更新记录和新记录，此ID应该保持不变.我正在生成一个newid.
Hello All,
I am using SQL server 2008.
I have a window service which is used to insert / update records in a table.
I am calling the stored procedure for each record.
My table has a column named ID [uniqueidentifier] and my stored procedure is like something like this. This ID should remain same for the updated records and for a new record I am generaing a newid.
CREATE Proc NAME_XXXXXXX
(
@P1 nvarchar(255),
@P2 nvarchar(255),
@P3 nvarchar(255),
@P4 nvarchar(255)=null,
@P5 nvarchar(50)=null,
@P6 nvarchar(50)=null,
@P7 nvarchar(50)=null,
@P8 nvarchar(50)=null,
@P9 int=null,
@P10 nvarchar(50)=null
)
as
Begin
if EXISTS( Select top 1 1 from TABLE_XYZ Where P1=@P1 and P3=@P3)
Begin
update TABLE_XYZ
set
P2=@P2
,P4=@P4
,P5=@P5
,P10=@P10
,P6=@P6
,P7=@P7
,P8=@P8
,P9=@P9
Where P1=@P1 and P3=@P3
End
Else
Begin
insert into TABLE_XYZ(ID,P1,P2,P4,P3,P5,P10,P6,P7,P8,P9)
values(newid(),@P1,@P2,@P4,@P3,@P5,@P10,@P6,@P7,@P8,@P9)
End
End
如何提高性能.
谢谢，
Debata
How can I improve the performance.
Thanks,
Debata
推荐答案
几个月前已经回答了类似的问题，请参见
在哪里存储10亿条记录 [ Sql批量插入/更新 [运行数百万个插入. [使用ADO.NET和OPENXML执行批量数据库操作 [
Already answered a similar question few months ago, see that
Where to store 1 billion records[^]
Sql Bulk Insert/Update[^]
Running millions of inserts.[^]
ADO.NET and OPENXML to Perform Bulk Database Operations[^]
You can find more in CP & Google
0)您还没有告诉我们如何从服务中调用存储的proc.但是，如果您使用的是SqlConnection/SqlCommand方法，则可以通过在每个更新/插入周期中仅一次创建/打开连接和命令对象来提高性能.
0) You haven''t told us HOW you''re calling the stored proc from your service. However, if you''re using the SqlConnection/SqlCommand method, you can improve performance by only creating/opening the connection and command objects one time for each update/insert cycle.
// allocate your objects
SqlConnection conn = new SqlConnection(connstring);
SqlCommand cmd = new SqlCommand("storedproc_name", conn);
// open the connection
conn.Open();
// add your parameters (specifying the type of the parameter
cmd.Parameters.Add("@P2", SqlDbInt);
...
cmd.Parameters.Add("@P9", SqlWhateverType);
// loop through all your updates
for (int i = 0; i < list.Count; i++)
{
// set the paramters
cmd.Parameters["@P2"].Value = list[i].P2Value;
...
cmd.Parameters["@P9"].Value = list[i].P9Value;
// execute non-query
cmd.ExecuteNonQuery();
}
1)将记录组放入XML字符串中，然后将它们以较大的组传递给存储过程
1) Put groups of records into XML strings and pass them in larger groups to the store proc
如thatraja所发布的那样，您可以通过使用某种批量操作来大大加快操作速度
但是，如果由于某种原因无法实现，则此行可能是您的SQL中的瓶颈.
As posted by thatraja, you can massively speed this up by using some sort of bulk operation
However, if that is not possible for whatever reason, a possible bottle neck in your SQL is this line.
Select top 1 1 from TABLE_XYZ Where P1=@P1 and P3=@P3
如何为TABLE_XYZ对象建立索引?您在P1和P3上有索引吗?您无法将唯一标识符传递到存储过程中，因此可以选择...
How is the object TABLE_XYZ indexed? Do you have an index on P1 and P3? Is there no way you can feed through the unique identifier into your stored procedure so you can select like...
Select top 1 1 from TABLE_XYZ Where ID=@UniqueID
您在表上的聚集索引是什么? UniqueIdentifiers不是聚集索引字段的很好的候选者，由于它们的随机性，它们可能会在插入期间导致数据重组.聚集索引应基于某种增量标识符.
http://msdn.microsoft.com/en-us/library/ms190639.aspx [ ^ ]
查看SQL参数的数据类型，我认为您需要分析表和查询计划，您可以在此处优化流程.运行查询->显示估计执行计划.您是在进行表格扫描吗?
What is your clustered index on the table? UniqueIdentifiers aren''t very good candidates for clustered index fields, they will likely cause data reorganisation during inserts because of their random nature. A clusted index should be based on some sort of incrementing identifier.
http://msdn.microsoft.com/en-us/library/ms190639.aspx[^]
Looking at the data types for your SQL parameters, I think you need to analyse the table and query plan, you can probably optimise the process here. Run Query -> Display Estimated Execution plan. Are you causing table scans?
这篇关于插入/更新150000条记录花费了一个多小时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
