Loading Large Sqlite DB file - hangs - performance advice lazarus / 16-Databases ---------------------------------------------------------------------- marcusadamski · 2020-10-24 19:58 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? Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i  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; ---------------------------------------------------------------------- marcov · 2020-10-24 20:02 Simple grids are fairly slow for anything above thousands/ten- thousands rows.  Store the results in memory and use some virtual grid that only touches actually shown data. (Like virtual string tree that can also be used in grid mode) First thing is to test if that hypothesis is correct. If you don't assign (and thus enlarge) the grid, is it faster then? ---------------------------------------------------------------------- marcusadamski · 2020-10-24 20:42 hi marcov, I understand this is not a real world scenario, I was just trying to get a comparison with other languages. I carried out the following two tests: 1) Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i     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;  which took around 60 seconds 2) Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i     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;  where the stringgrid was used, but overwrote the same first row each time.  Taking around 90 seconds I understand the "proper" approach would be to display items as they are viewed (e.g. virtual grid), but I was initially interested in a quick comparison with other languages (and to jog my memory on the FreePascal syntax) It's nice to discover FreePascal again. I'll persevere with a few mini apps, to see how I find the IDE, and language, these many years later... ---------------------------------------------------------------------- marcov · 2020-10-24 21:24 This is why comparing languages with a helicopter view is such bad idea. If one language has a virtual grid by default, and the other not, you get a lopsided impression, while it has nearly nothing to do with either language (and libraries) capabilities. It is a minor choice. Lazarus is modeled after Delphi which is older than both C# and Java, so its default choices are a bit more conservative. Same for the 30 seconds extra. It is dangerous to assume that it is merely a difference in string performance. Some languages might detect that you don't do anything with the assignment and eliminate it, some not. ---------------------------------------------------------------------- korba812 · 2020-10-24 22:01 Do you really need to display 1.7 million rows? No man can get through all these records. The strength of SQL databases is that you can select a subset of the data that you really need. But if you need to process this amount of data, it would be better to use native SQLite API without using TDataset. ---------------------------------------------------------------------- PierceNg · 2020-10-25 00:32 Quote from: korba812 on October 25, 2020, 12:01:21 amDo you really need to display 1.7 million rows? No man can get through all these records. The strength of SQL databases is that you can select a subset of the data that you really need. +1 OP's program loads all 1.7 million rows to display, what, 50 or 200 rows at a time? For such cases, best to use what is known as database paging or database cursor, no need to load all rows into memory ---------------------------------------------------------------------- Sieben · 2020-10-25 00:38 -1 - did you take the trouble of reading the thread...? ---------------------------------------------------------------------- winni · 2020-10-25 00:55 Hi! OP has to read the 1.7 Mio rows. Row by row. To check that there are no errors. After that we carry on with the discussion. Shake of the head. Winni ---------------------------------------------------------------------- marcusadamski · 2020-10-25 11:15 Thanks for the feedback - as I say, this is not a real world application. After 10-15 years away from Delphi, I'd like to start using a flavour of it again ... I.e. Lazarus.  Wanted to re- familiarise myself with the core packages and syntax through some basic apps - and what better way than a simple DB app.  In this case, there was a performance discrepancy and I was curious why. Btw - if using SQLite, what's the preferred component set and why: TSQLConnector or TSQLite3Connection ? Due to limited time, it will take me a little while to get up to speed - but I'm hoping the forum will be patient with me ---------------------------------------------------------------------- marcov · 2020-10-25 11:33 I use sqlite3conn, which is sqldb, which I assume TSQLConnector is a wrapper for. But I only use sqlite for small peanut local db. ---------------------------------------------------------------------- korba812 · 2020-10-25 17:22 TSQLQuery component from the SQLDB package uses TBufDataSet as base - downloaded data from database is additionally buffered inside TBufDataSet and hence such memory consumption and lower performance. You can try other SQLite database access components. See https://wiki.freepascal.org/SQLite The idea of TDataSet was borrowed from Delphi and is intended for rapid development of database applications linked to GUI. Therefore, it includes a large number of additional functionalities (data validation, GUI controls notification, code page conversion, etc.) that may slow down performance. As mentioned earlier, use the direct SQLite API for maximum performance. ---------------------------------------------------------------------- PierceNg · 2020-10-26 16:54 Quote from: marcusadamski on October 25, 2020, 12:15:35 pmBtw - if using SQLite, what's the preferred component set and why: TSQLConnector or TSQLite3Connection ? For a web microservice I use https://github.com/plashenkov/SQLite3-Delphi-FPC. I will be looking at Zeos next and also relook at what comes with FPC. My code doesn't use any abstract database API, just SQLite C API style prepare/step/finalize operations. ---------------------------------------------------------------------- Thaddy · 2020-10-26 16:56 Never load the whole database. Leave the performance to the DB engine. You do not load a database but simply open it. As said many times (also here) use a virtual control like TVirtualListview. TDbGrid is fine for smaller (<1000) solutions. The TVirtualXXX controls are windowed - not MsWindows! but an algorithm family - controls and perform therefor much faster since the file stays on disk and only what needs to be displayed is actually in memory. The Java grids work in the same way. There is more to it but that is the simplest explanation.