Lazarus

Programming => Databases => Topic started by: pcurtis on October 16, 2020, 07:03:21 am

Title: [SOLVED?] Database Paging
Post by: pcurtis on October 16, 2020, 07:03:21 am
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][+][-]
  1. procedure TForm1.DBGrid1KeyDown(Sender: TObject; var Key: Word; Shift: TShiftState);
  2. begin
  3.  
  4.   case Key of
  5.  
  6.   38 : begin // up
  7.          if ZQuery1.RecNo = 1 then
  8.            begin
  9.              if CURRENT_PAGE > 1 then
  10.                begin
  11.                  dec(CURRENT_PAGE);
  12.                  // Code to retrieve page of records
  13.                  ZQuery1.Last;
  14.                  Key := 0;
  15.                end;
  16.            end;
  17.        end ;
  18.  
  19.   40 : begin // down
  20.          if ZQuery1.RecNo = 100 then
  21.            begin
  22.              Caption := key.ToString;
  23.              if CURRENT_PAGE < 42 then
  24.                begin
  25.                  inc(CURRENT_PAGE);
  26.                  // Code to retrieve page of records
  27.                  ZQuery1.First;
  28.                  Key := 0;
  29.                end;
  30.            end;
  31.   end;
  32. end;
  33.  

The query to return a page of records is something like ...

SELECT * FROM tnSTATIONS LIMIT 100, :PAGE_NUMBER

Thanks in advance.

Title: Re: Database Paging
Post by: MarkMLl on October 16, 2020, 08:44:34 am
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).
Title: Re: Database Paging
Post by: pcurtis on October 16, 2020, 10:37:51 am
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.
Title: Re: Database Paging
Post by: trev on October 16, 2020, 11:21:50 am
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.

This page (https://www.sqlite.org/limits.html) on the sqlite website would suggest that you are mistaken. Your database is tiny.
Title: Re: Database Paging
Post by: MarkMLl on October 16, 2020, 11:33:26 am
This page (https://www.sqlite.org/limits.html) 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
Title: Re: Database Paging
Post by: pcurtis on October 16, 2020, 11:39:10 am
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.
Title: Re: Database Paging
Post by: trev on October 16, 2020, 11:50:25 am
You missed:

Quote
For 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:

Quote
This 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.
Title: Re: Database Paging
Post by: ttomas on October 16, 2020, 12:27:51 pm
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
Title: Re: Database Paging
Post by: pcurtis on October 16, 2020, 12:53:36 pm
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.
Title: Re: Database Paging
Post by: MarkMLl on October 16, 2020, 01:21:18 pm
When 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
Title: Re: Database Paging
Post by: bracara on October 16, 2020, 02:01:27 pm
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 (https://www.sqlite.org/lang_explain.html)
and https://www.sqlite.org/eqp.html (https://www.sqlite.org/eqp.html)
Title: Re: Database Paging
Post by: avra on October 16, 2020, 02:36:29 pm
The 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?
Title: Re: Database Paging
Post by: Martin_fr on October 16, 2020, 02:59:48 pm
The 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)
Title: Re: Database Paging
Post by: PierceNg on October 16, 2020, 05:35:12 pm
I 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 (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
Title: Re: Database Paging
Post by: pcurtis on October 17, 2020, 08:58:42 am
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);

Title: Re: [SOLVED?] Database Paging
Post by: MarkMLl on October 17, 2020, 09:13:15 am
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
Title: Re: [SOLVED?] Database Paging
Post by: pcurtis on October 17, 2020, 10:18:25 am
I don't need to get the data in chunks anymore.
Title: Re: [SOLVED?] Database Paging
Post by: MarkMLl on October 17, 2020, 10:31:25 am
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
TinyPortal © 2005-2018