[SOLVED?] Database Paging lazarus / 16-Databases ---------------------------------------------------------------------- pcurtis · 2020-10-16 05:03 Hi All, I need help / advice. I have a sqlite3 database of internet radio stations (43000 records). Each station has it's own logo (a 3 kb webp blob). The size of the DB is 150Mb. I access this DB with Zeos. Because of the size I return queries in pages of just 100 records. The problem is that each time I execute a query to return a pages of records there is a very long wait (20+ seconds). Is there anything I can do (options, properties, ...) to speed up the query? Here's a snippet of code I use to page the DB - 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 TForm1.DBGrid1KeyDown(Sender: TObject; var Key: Word; Shift: TShiftState);begin   case Key of   38 : begin // up         if ZQuery1.RecNo = 1 then           begin             if CURRENT_PAGE > 1 then               begin                 dec(CURRENT_PAGE);               // Code to retrieve page of records                 ZQuery1.Last;                 Key := 0;               end;           end;       end ;   40 : begin // down         if ZQuery1.RecNo = 100 then           begin             Caption := key.ToString;             if CURRENT_PAGE < 42 then               begin                 inc(CURRENT_PAGE);                 // Code to retrieve page of records                 ZQuery1.First;                 Key := 0;             end;           end;  end;end;  The query to return a page of records is something like ... SELECT * FROM tnSTATIONS LIMIT 100, :PAGE_NUMBER Thanks in advance. ---------------------------------------------------------------------- MarkMLl · 2020-10-16 06:44 Looking at it from a database POV, I'd ask for a specific order and would make sure there was an index for that (if Sqlite supports explicit indexes). ---------------------------------------------------------------------- pcurtis · 2020-10-16 08:37 The DB is indexed correctly. I think the fact is that sqlite is not designed / expected to hold such a large amount of data. I would love to hear otherwise. I am currently extracting the files. ---------------------------------------------------------------------- trev · 2020-10-16 09:21 Quote from: pcurtis on October 16, 2020, 10:37:51 amI think the fact is that sqlite is not designed / expected to hold such a large amount of data. I would love to hear otherwise. This page on the sqlite website would suggest that you are mistaken. Your database is tiny. ---------------------------------------------------------------------- MarkMLl · 2020-10-16 09:33 Quote from: trev on October 16, 2020, 11:21:50 amThis page on the sqlite website would suggest that you are mistaken. Your database is tiny. Also I'd suggest that a database is never indexed "correctly". Absolutely everything is a compromise, and even if it's only being used for a single type of insertion and a single type of selection there's still a compromise between the time spent updating data structures for the two cases. MarkMLl ---------------------------------------------------------------------- pcurtis · 2020-10-16 09:39 Those are theoretical limits. I would like to see the machine that can handle a 281 TB sqlite DB, without performance hits. I've got a HP laotop. ---------------------------------------------------------------------- trev · 2020-10-16 09:50 You missed: QuoteFor this reason, versions of SQLite since about release 3.5.8 (2008-04-16) have well-defined limits, and those limits are tested as part of the test suite. And in relation to the 281TB you missed: QuoteThis particular upper bound is untested since the developers do not have access to hardware capable of reaching this limit. However, tests do verify that SQLite behaves correctly and sanely when a database reaches the maximum file size of the underlying filesystem (which is usually much less than the maximum theoretical database size) and when a database is unable to grow due to disk space exhaustion. In any event, your original point is comprehensively debugged by that page. Your database is tiny. ---------------------------------------------------------------------- ttomas · 2020-10-16 10:27 Can you test speed of SQL without blob (logo) field? SELECT field1, field2,... without blob field FROM tnSTATIONS LIMIT 100, :PAGE_NUMBER also add ORDER BY ID ---------------------------------------------------------------------- pcurtis · 2020-10-16 10:53 The speed is the same. I have extracted all blobs from the DB and saved them in a structured directory, and load each image as needed. When I have time I will try MariaDB. ---------------------------------------------------------------------- MarkMLl · 2020-10-16 11:21 Quote from: pcurtis on October 16, 2020, 12:53:36 pmWhen I have time I will try MariaDB. Or PostgreSQL or something. I'd suggest that under the circumstances what you really need to know is how query execution is being planned, I mention Postgres since I know it has "explain verbose". I think what you're doing wrong is not having an order clause and matching index- and I don't care if you don't think it's important since you're the one with performance problems- and possibly not having a where clause. The result is that the database is reading the entire table into memory sequentially, then it's throwing away everything except the hundred rows it thinks you want. MarkMLl ---------------------------------------------------------------------- bracara · 2020-10-16 12:01 Sqlite has EXPLAIN and EXPLAIN QUERY PLAN which might help you work out what is going wrong. I work with a database with 90000 records that gives instant results on a Raspberry Pi, so I agree that things are not working as they should. See: https://www.sqlite.org/lang_explain.html and https://www.sqlite.org/eqp.html ---------------------------------------------------------------------- avra · 2020-10-16 12:36 Quote from: pcurtis on October 16, 2020, 07:03:21 amThe query to return a page of records is something like ... SELECT * FROM tnSTATIONS LIMIT 100, :PAGE_NUMBER Does that query take the same time when executed in some visual sqlite manager not related to Lazarus and Zeos? ---------------------------------------------------------------------- Martin_fr · 2020-10-16 12:59 Quote from: pcurtis on October 16, 2020, 07:03:21 amThe query to return a page of records is something like ... SELECT * FROM tnSTATIONS LIMIT 100, :PAGE_NUMBER "something like"..... Well, does the "real"  query have an "order by"? Because "limit" without "order by" is not recommended. I am not an sqlite expert (the above though can applies to sqlite, according to the sqlite doc). As for generic db knowledge, the "order by" column should then be indexed. (as you have no "where") Also this page https://www.sqlitetutorial.net/sqlite-limit/ says it is "limit offset, row_count"? (and "PAGE_NUMBER" differs to an offset) ---------------------------------------------------------------------- PierceNg · 2020-10-16 15:35 Quote from: pcurtis on October 16, 2020, 12:53:36 pmI have extracted all blobs from the DB and saved them in a structured directory, and load each image as needed. See https://www.sqlite.org/intern-v-extern-blob.html - Internal vs external blobs in SQLite As for your original question on SQLite paging, see this article from the old SQLite wiki: https://www2.sqlite.org/cvstrac/wiki?p=ScrollingCursor ---------------------------------------------------------------------- pcurtis · 2020-10-17 06:58 OK. It works at normal speed now. The problem is I don't know where the problem was. Here's the query = select * from tnstations order by fnstation limit 1000 Here's the database CREATE TABLE "tnCOUNTRIES"(   [fnIDX] INTEGER PRIMARY KEY AUTOINCREMENT,   [fnCOUNTRY] TEXT,   [fnFLAG] BLOB,   [fnDELETED] INTEGER DEFAULT 0); CREATE TABLE [tnGENRES](   [fnIDX] INTEGER PRIMARY KEY AUTOINCREMENT,   [fnDESCRIPTION] TEXT,   [fnDELETED] BOOLEAN DEFAULT 0); CREATE TABLE [tnSTATIONS](   [fnIDX] INTEGER PRIMARY KEY AUTOINCREMENT,    [fnSTATION] VARCHAR(255),   [fnCOUNTRY_IDX] INTEGER DEFAULT 0,   [fnSTREAM] TEXT,   [fnGENRES_IDX] INTEGER DEFAULT 0,   [fnLOGO] BLOB,   [fnLOCATION] VARCHAR(255), ,   [fnLANGUAGE] VARCHAR(255),   [fnCODEC] VARCHAR(100),   [fnBITRATE] VARCHAR(100), ,   [fnWEBSITE] VARCHAR(255),   [fnDELETED] INTEGER DEFAULT 0); ---------------------------------------------------------------------- MarkMLl · 2020-10-17 07:13 If all you're doing is trying to get things in chunks then order by the sequential numeric field and put in an appropriate where clause to control the output. Don't rely on limit by itself. Alternatively use a cursor etc. MarkMLl ---------------------------------------------------------------------- pcurtis · 2020-10-17 08:18 I don't need to get the data in chunks anymore. ---------------------------------------------------------------------- MarkMLl · 2020-10-17 08:31 Suit yourself, but you're the one who gave yourself a problem, you're the one who doesn't know why it went away, and I'm the one telling you it's still lurking :-) MarkMLl