Devart Forums

I'm using the SQLite component under XE8 in a legacy flat file application. I've broken out the personal information from the flat file and am using in a SQLite Table in a DB that is encrypted.

Reading the table appears to be fine, but writing appears to be quite slow.

I have a read and a write procedure that would normally write or read a flat file record on a per bowler bases. I've placed code to call a function within the read and write procedures to read or write from the DB instead for portions of a record.

As the calls are made on a per person record, there isn't the tuning where one could read all records or write all records at once DB wise and use transactions.

Is there a way I can improve performance that is otherwise slow, bordering on too slow to use.

Here's roughly my procedure, with about 15 other columns removed for brevity of the example.

Any suggestions are welcome. i don't see how wrapping in a transaction would help or not. Self.Exists function call is a function that does a select to find the record and return true or false if the record is there, so that I know to update or insert.

Lance



function TBLSDBBLR.Write(var Value: tBlr_Data): boolean;
begin
result := False;
FDB.DModule.LiteQuery1.Close( );
FDB.DModule.LiteQuery1.SQL.Clear( );

if self.Exists(value.Blr_Number) then begin
FDB.DModule.LiteQuery1.SQL.Clear( );
FDB.DModule.LiteQuery1.Params.Clear;

FDB.DModule.LiteQuery1.SQL.Add( 'UPDATE BLR SET ');

FDB.DModule.LiteQuery1.SQL.Add( ' BLR_Initials= :BLR_Initials, BLR_Company= :BLR_Company,');
FDB.DModule.LiteQuery1.SQL.Add( ' BLR_FName= :BLR_FName, BLR_NickName= :BLR_NickName, BLR_MI= :BLR_MI,');
FDB.DModule.LiteQuery1.SQL.Add( ' BLR_LName= :BLR_LName, BLR_Vintage= :BLR_Vintage FDB.DModule.LiteQuery1.SQL.Add( ' BLR_Gender= :BLR_Gender ' );
FDB.DModule.LiteQuery1.SQL.Add( ' WHERE BLR_ID= :BLR_ID' );


FDB.DModule.LiteQuery1.ParamByName('BLR_ID').AsInteger := Value.Blr_Number;
FDB.DModule.LiteQuery1.ParamByName('BLR_Initials').AsString := Value.Initials;
FDB.DModule.LiteQuery1.ParamByName('BLR_Company').AsString := Value.Company;
FDB.DModule.LiteQuery1.ParamByName('BLR_FName').AsString := Value.fName;
FDB.DModule.LiteQuery1.ParamByName('BLR_NickName').AsString := Value.bNickName;
FDB.DModule.LiteQuery1.ParamByName('BLR_MI').AsString := Value.MI;
FDB.DModule.LiteQuery1.ParamByName('BLR_LName').AsString := Value.lName;
FDB.DModule.LiteQuery1.ParamByName('BLR_Vintage').AsString := Value.blr_Vintage;
FDB.DModule.LiteQuery1.ParamByName('BLR_Gender').AsInteger := Value.Sex;



FDB.DModule.LiteQuery1.ExecSQL;
Result := True;
end else begin
FDB.DModule.LiteQuery1.SQL.Clear( );
FDB.DModule.LiteQuery1.Params.Clear;
FDB.DModule.LiteQuery1.SQL.Add( 'INSERT INTO BLR (');
FDB.DModule.LiteQuery1.SQL.Add( 'BLR_ID,BLR_Initials,BLR_Company,BLR_FName,BLR_NickName,BLR_MI,BLR_LName,BLR_Vintage,BLR_Addr,');
FDB.DModule.LiteQuery1.SQL.Add( 'BLR_City,BLR_State,BLR_Zip,BLR_Gender)');
FDB.DModule.LiteQuery1.SQL.Add( 'Values(');
FDB.DModule.LiteQuery1.SQL.Add( ':BLR_ID,:BLR_Initials,:BLR_Company,:BLR_FName,:BLR_NickName,:BLR_MI,:BLR_LName,:BLR_Vintage,:BLR_Addr,');
FDB.DModule.LiteQuery1.SQL.Add( ':BLR_City,:BLR_State,:BLR_Zip,:BLR_Gender');
FDB.DModule.LiteQuery1.SQL.Add( ')');

FDB.DModule.LiteQuery1.ParamByName('BLR_ID').AsInteger := Value.Blr_Number;
FDB.DModule.LiteQuery1.ParamByName('BLR_Initials').AsString := Value.Initials;
FDB.DModule.LiteQuery1.ParamByName('BLR_Company').AsString := Value.Company;
FDB.DModule.LiteQuery1.ParamByName('BLR_FName').AsString := Value.fName;
FDB.DModule.LiteQuery1.ParamByName('BLR_NickName').AsString := Value.bNickName;
FDB.DModule.LiteQuery1.ParamByName('BLR_MI').AsString := Value.MI;
FDB.DModule.LiteQuery1.ParamByName('BLR_LName').AsString := Value.lName;
FDB.DModule.LiteQuery1.ParamByName('BLR_Vintage').AsString := Value.blr_Vintage;
FDB.DModule.LiteQuery1.ParamByName('BLR_Gender').AsInteger := Value.Sex;


FDB.DModule.LiteQuery1.ExecSQL;
Result := True;
end;
end;

ok. It does look like, so far, transaction is the resolution.

If I wrap a starttransaction and commit prior to making several writes, the speed improvement is massive.

Stopwatch := TStopwatch.StartNew;

LiteConnection1.StartTransaction;
for I := low( self.peeps ) to high( self.peeps ) do begin
write( peeps[ I ], False );
end;
LiteConnection1.Commit;

Elapsed := Stopwatch.Elapsed;

If I leave off Transaction, it's very poor.

My test without transaction writing 200 people in insert mode is - Elapsed Time is 19 seconds, 567 ms, Ticks: 195679279
My test without transaction writing 200 people in update mode is - Elapsed Time is 20 seconds, 993 ms, Ticks: 209932967
My test with transaction writing 200 people in insert mode is - Elapsed Time is 0 seconds, 545 ms, Ticks: 5454139
My test with transaction writing 200 people in update mode is - Elapsed Time is 0 seconds, 585 ms, Ticks: 5859661

You are absolutely right. Usage of transactions in SQLite database is one of the main approaches to increase performance when using several queries to the database. Since default SQLite settings are designed for better reliability, not performance. Contact us if you have questions concerning LiteDAC