使用lazarus改善ms sql插入性能 [英] improving ms sql insert performance with lazarus

问题描述

我有一个简单的带有3个数据字段的插入语句. 不是主键且不会自动递增的Tag_ID， 一个时间戳，该时间戳将一个简单的DateTime Now保存为一个字符串，一个float值包含一个简单的devide计算.

sql服务器实际上是本地的，但是稍后它将在不在本地网络中的另一台计算机上.现在，我得到10.000个条目的时间为25.8秒.

我的代码如下:

procedure TForm1.testMssql(Datensaetze: integer);
var
  i: integer;
  before,after,result: real;
begin
  before := GetTickCount;
  for i:= 0 to Datensaetze do
  begin
    try
          query.DataBase := conn;
          query.UsePrimaryKeyAsKey:=false;
          query.SQL.Text := 'insert into speedTest(TagID,timestamp,Value) values(:tag_id,:timestamp, :value)';
          query.Params.ParamByName('tag_id').AsInteger := i ;
          query.Params.ParamByName('timestamp').AsString := DateTimeToStr(Now);
          query.Params.ParamByName('value').AsFloat := ((i*2) / 55);
          query.ExecSQL;
          SQLTransaction1.Commit;
    except
      on E: Exception do
         ShowMessage(E.Message);
    end;
  end;
  after := GetTickCount;

  result := (after - before)/1000;
  Memo1.Text := FloatToStr(result);
end;  

解决方案

批量插入

http://msdn.microsoft.com/de-de/library/ms188365.aspx

i have a simple insert statement with 3 datafields. A Tag_ID which is not the primary key and not auto increment, a timestamp which saves an easy DateTime Now as a string and a float value which contains a simple devide calculation.

the sql server is actually local but later it will be on another machine not in the local network. Now i get 25,8 sec for 10.000 entries.. how can i improve this?

my code looks like this:

procedure TForm1.testMssql(Datensaetze: integer);
var
  i: integer;
  before,after,result: real;
begin
  before := GetTickCount;
  for i:= 0 to Datensaetze do
  begin
    try
          query.DataBase := conn;
          query.UsePrimaryKeyAsKey:=false;
          query.SQL.Text := 'insert into speedTest(TagID,timestamp,Value) values(:tag_id,:timestamp, :value)';
          query.Params.ParamByName('tag_id').AsInteger := i ;
          query.Params.ParamByName('timestamp').AsString := DateTimeToStr(Now);
          query.Params.ParamByName('value').AsFloat := ((i*2) / 55);
          query.ExecSQL;
          SQLTransaction1.Commit;
    except
      on E: Exception do
         ShowMessage(E.Message);
    end;
  end;
  after := GetTickCount;

  result := (after - before)/1000;
  Memo1.Text := FloatToStr(result);
end;  

解决方案

Bulk Insert

http://msdn.microsoft.com/de-de/library/ms188365.aspx

这篇关于使用lazarus改善ms sql插入性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！