trupka wrote:Don't leave ZConn.TransactionIsolationLevel to tiNone. Change it ( eg. readCommited ) or StartTransaction, Commit and Rollback methods wont work.
Thanks, i've tried this method, it does speed up the inserts tremendously, but there's a problem - when i tried to do a select on the same table at the same time using another app (the query was successful), the 'insert app' gives a "database is locked" exemption and rolls back all the inserts.
INSERT:
Code: Select all
procedure TForm1.Button2Click(Sender: TObject);
var i:integer;
siz : longint;
hr, mins, se, s1 : word;
hr2, min2, se2 : word;
begin
try
GetTime (hr,mins,se,s1);
if FileExists (ZConnection1.Database) then
begin
ZConnection1.connect;
ZConnection1.StartTransaction;
ZQuery1.Close;
ZQuery1.SQL.Clear;
For i := 1 to 10000 do
begin
ZQuery1.SQL.Add ('INSERT INTO t2 VALUES(24999,6,'+inttostr(i)+');');
end;
ZQuery1.ExecSQL;
ZConnection1.commit;
end
else
MessageDlg('Database file cannot be found: ' +
ZConnection1.Database, mtInformation, [mbok], 0);
except
on E : Exception do begin
ShowMessage(E.ClassName+' error raised, with message : '+E.Message);
ZConnection1.Rollback;
end;
end;
GetTime (hr2, min2, se2, s1);
siz := se2-se+(min2-mins)*60+(hr2-hr)*60*60;
ShowMessage (IntToStr(siz) + ' seconds');
FreeAndNil(ZQuery1);
end;
SELECT:
Code: Select all
procedure TForm1.Button1Click(Sender: TObject);
begin
try
if FileExists (ZConnection1.Database) then
begin
ZConnection1.connect;
ZQuery1.Close;
ZQuery1.SQL.Clear;
ZQuery1.SQL.Text := Memo1.Text;
if CheckBox1.Checked then begin
showmessage(ZQuery1.SQL.Text);
ZQuery1.Open;
end
else begin
ZQuery1.ExecSQL;
end;
end
else
MessageDlg('Database file cannot be found: ' +
ZConnection1.Database, mtInformation, [mbok], 0);
except
on E : Exception do
ShowMessage(E.ClassName+' error raised, with message : '+E.Message);
end;
end;
Meanwhile, i searched through the Internet, and found out that if i amended the code as follows, it will speed up the INSERTS while at the same time avoid the "db locked" error if i try and do SELECTS on the table. Note this method only works if only if TransactionIsolationLevel is set to none.
Code: Select all
procedure TForm1.Button2Click(Sender: TObject);
var i:integer;
siz : longint;
hr, mins, se, s1 : word;
hr2, min2, se2 : word;
begin
try
GetTime (hr,mins,se,s1);
if FileExists (ZConnection1.Database) then
begin
ZConnection1.connect;
ZQuery1.Close;
ZQuery1.SQL.Clear;
//take sqlite out of autocommit mode.
ZQuery1.SQL.text:= 'BEGIN TRANSACTION';
ZQuery1.Open;
ZQuery1.SQL.Clear;
//
For i := 1 to 10000 do
begin
ZQuery1.SQL.Add ('INSERT INTO t2 VALUES(24999,6,'+inttostr(i)+');');
end;
ZQuery1.ExecSQL;
//revert to autocommit mode.
ZQuery1.SQL.Clear;
ZQuery1.SQL.text:= 'COMMIT TRANSACTION';
ZQuery1.Open;
//
end
else
MessageDlg('Database file cannot be found: ' +
ZConnection1.Database, mtInformation, [mbok], 0);
except
on E : Exception do begin
ShowMessage(E.ClassName+' error raised, with message : '+E.Message);
ZConnection1.Rollback;
end;
end;
GetTime (hr2, min2, se2, s1);
siz := se2-se+(min2-mins)*60+(hr2-hr)*60*60;
ShowMessage (IntToStr(siz) + ' seconds');
FreeAndNil(ZQuery1);
end;
The question is, am i doing the right thing? Appreciate any pointers.
