Hi there,
After using Delphi for many years, moving to C, Java,Python .... now I'm returning to Lazarus. My first project was to test out loading a large Sqlite file (~ 700mb, 1.7 million rows). With the sqlQuery limited to a few thousand rows, everything was very fast. However, it simply hangs on the full file. The following set up runs for around 30 mins, takes 3GB in memory, but still doesn't finish.
I want to load this manually, rather than DBGrid. Can someone please review my code to see if I've done something stupid? (Note - connection, transaction and sqlquery all created and freed in main form create/ destroy. POSTCODE_COL etc are just INT consts).
I've written similar test apps in Java and C++, using a similar structure, same db - and they're taking around 60 secs. So maybe I'm missing something here?
procedure TfrmMain.btnLoadClick(Sender: TObject);
var
RowIndex: integer;
StartTime: TDateTime;
begin
Self.Cursor:= crHourGlass;
sgPostcodes.BeginUpdate;
StartTime:= Now;
try
btnLoad.Caption:= 'Working ...';
btnLoad.Enabled:= False;
// connect to the db
FConn.DatabaseName:= ConcatPaths([extractfiledir(paramstr(0)), 'data', 'postcodes.dat']);
FConn.ConnectorType:='SQLite3';
FConn.Open;
FQuery.SQL.Text:='select ' +
'cast( "Postcode3" as TEXT), ' +
'cast( "LocalAuthorityName" as TEXT), ' +
'cast( "WardName" as TEXT), ' +
'"Longitude", ' +
'"Latitude" ' +
'from postcodes';
FQuery.Open;
// iterate through all the postcodes
RowIndex:= 1;
while not FQuery.EOF do begin
sgPostcodes.RowCount:= RowIndex +1;
sgPostcodes.Cells[0, RowIndex]:= FQuery.Fields[POSTCODE_COL].AsString;
sgPostcodes.Cells[1, RowIndex]:= FQuery.Fields[LOCAUTH_COL].AsString;
sgPostcodes.Cells[2, RowIndex]:= FQuery.Fields[WARDNAME_COL].AsString;
sgPostcodes.Cells[3, RowIndex]:= FloatToStr(FQuery.Fields[LONG_COL].AsFloat);
sgPostcodes.Cells[4, RowIndex]:= FloatToStr(FQuery.Fields[LAT_COL].AsFloat);
FQuery.Next;
inc(RowIndex);
end;
finally
sgPostcodes.EndUpdate();
Self.Cursor:= crDefault;
// update stats
lblTimeTaken.Caption:= lblTimeTaken.Caption + ' ' + FormatDateTime('nn : ss : z ', Now - StartTime);
lblCount.Caption:= lblCount.Caption + ' ' + IntToStr(RowIndex -1);
end;
end;