如何提高数据插入/更新性能？

Question

我需要提高数据加载的性能。当前的算法是从一个表中进行完整的选择：

select Field1, Field2,...,FieldN from Table1 order by FieldM

新数据是从文本文件中读取的(例如，每个datatable行的文本文件行)。该表有一个主键，其中包含两个字段。对于文本文件的每一行，它通过这两个字段(即主键)来定位所需的行。

query.Locate('Field1;Field2',VarArrayOf([Value1,Value2]),[]);

如果Locate返回True，它将编辑该行，否则将追加一个新行。

因此，只要表由大约200000行组成，每个Locate操作就需要一定数量的time...so，它管理每秒更新大约5-6行。

我应该考虑哪些事情来改善它？

可能会用单独的查询来代替通过这个伟大的select进行定位？

Answer 1

不要使用Locate()。如果你使用locate()，那么Delphi会在客户端搜索行，只是扫描查询中的行集，这会花费很多时间。

如果你有权访问MSSQL来创建存储过程，那么就创建下面的过程，并在没有任何条件的情况下为文本文件中的每一行运行它(在Delphi语言中使用TAdoStoredProc.ExecProc )。因此在这种情况下，您不需要首先选择和定位过程。如果找到Filed1和Field2，它会更新记录，如果没有，则插入。

CREATE PROCEDURE dbo.update_table1 
@Field1 int, --key1
@Field2 int, --key2
@Field3 int, -- data fileds
@Field4 int

AS

SET NOCOUNT ON
update table1 set Field3=@Field3,Field4=@Field4 
        where Field1=@Field1 and Field2=@Field2;
IF(@@Rowcount=0)
BEGIN
     insert into table1(Field1,Field2,Field3,Field4) 
                values (@Field1,@Field2,@Field3,@Field4);
END
GO

下面是使用ADO调用此存储过程的Delphi代码：

......
var 
     ADOStoredP: TADOStoredProc;

  ......
begin

........
    ADOStoredP:=TADOStoredProc.Create(nil);
   try
      ADOStoredP.Connection:=DataMod.SQL_ADOConnection; //Your ADO Connection instance here
      ADOStoredP.ProcedureName:='Update_table1';
      ADOStoredP.Parameters.CreateParameter('@Field1', ftInteger, pdInput, 0, 0);
      ADOStoredP.Parameters.CreateParameter('@Field2', ftInteger, pdInput, 0, 0);
      ADOStoredP.Parameters.CreateParameter('@Field3', ftInteger, pdInput, 0, 0);
      ADOStoredP.Parameters.CreateParameter('@Field4', ftInteger, pdInput, 0, 0);

      While () -- Your text file loop here
      begin

      ADOStoredP.Parameters.ParamByName('@Field1').Value:=Field1 value from text file here;
      ADOStoredP.Parameters.ParamByName('@Field2').Value:=Field2 value from text file here;
      ADOStoredP.Parameters.ParamByName('@Field3').Value:=Field3 value from text file here;
      ADOStoredP.Parameters.ParamByName('@Field4').Value:=Field4 value from text file here;

      ADOStoredP.ExecProc;

      end

    finally
      if Assigned(ADOStoredP) then
        begin
         ADOStoredP.Free;
        end;
    end;

........
end;

Answer 2

1. 如果可能，则应将文本文件发送到运行SQL Server的服务器。然后使用OPENROWSET(BULK)打开文本文件(请参阅E.将OPENROWSET BULK provider与格式化文件一起使用以从文本文件中检索行)。
2. 如果无法将文本文件发送到服务器，则创建一个临时或永久DB表，并使用INSERT将所有文本文件行插入到表中。
3. 如果使用的是SQL Server2008，则应使用MERGE运算符。如果SQL Server版本较旧，则可以使用两个SQL命令: UPDATE和INSERT。并使用(1) OPENROWSET或(2) DB表作为数据源。