我有一个简单的带有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;解决方案
批量插入
msdn.microsoft/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
msdn.microsoft/de-de/library/ms188365.aspx
更多推荐
使用lazarus改善ms sql插入性能
发布评论