improving ms sql insert performance with lazarus
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开发者_如何学Python 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
I guess it is the round trips to the db that takes the most time. Instead you can create a XML that looks like this including all your rows
<root>
<row>
<TagID>1</TagID>
<timestamp>2010-10-10T10:10:10</timestamp>
<value>10</value>
</row>
<row>
<TagID>2</TagID>
<timestamp>2011-11-11T11:11:11</timestamp>
<value>20</value>
</row>
</root>
Send that xml to stored procedure that insert the entire batch at once
create procedure InsertSpeedTest
@XML as xml
as
insert into speedTest (TagID, timestamp, Value)
select
r.r.value('TagID[1]', 'int'),
r.r.value('timestamp[1]', 'datetime'),
r.r.value('value[1]', 'int')
from @XML.nodes('root/row') r(r)
You have to modify the SP to match whatever data-types you are using. I believe this will be faster than what you are doing, but there is nothing like testing it yourself.
精彩评论