Recent

Author Topic: [SOLVED?] Database Paging  (Read 917 times)

pcurtis

  • Sr. Member
  • ****
  • Posts: 354
[SOLVED?] Database Paging
« 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.

« Last Edit: October 17, 2020, 09:01:51 am by pcurtis »
Windows 10 / Linux Mint 20
Laz 2.10.0
FPC 3.2.0

MarkMLl

  • Hero Member
  • *****
  • Posts: 1476
Re: Database Paging
« Reply #1 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).
Turbo Pascal v1 on CCP/M-86, multitasking with LAN and graphics in 128Kb.
Pet hate: people who boast about the size and sophistication of their computer.

pcurtis

  • Sr. Member
  • ****
  • Posts: 354
Re: Database Paging
« Reply #2 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.
Windows 10 / Linux Mint 20
Laz 2.10.0
FPC 3.2.0

trev

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 1079
  • Former Delphi 1-7, 10.2 User
Re: Database Paging
« Reply #3 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 on the sqlite website would suggest that you are mistaken. Your database is tiny.
o Lazarus v2.1.0 r63871, FPC v3.3.1 r47164, macOS 10.14.6, Xcode 11.3.1
o Lazarus v2.1.0 r64160, FPC v3.3.1 Nov 27 21:16:31, macOS 11.0.1 (aarch64), Xcode 12.2
o Lazarus v2.1.0 r61574, FPC v3.3.1 r42318, FreeBSD 12.1 amd64 (VMware VM)
o Lazarus v2.1.0 r61574, FPC v3.0.4, Ubuntu 20.04 (PD VM)

MarkMLl

  • Hero Member
  • *****
  • Posts: 1476
Re: Database Paging
« Reply #4 on: October 16, 2020, 11:33:26 am »
This 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
Turbo Pascal v1 on CCP/M-86, multitasking with LAN and graphics in 128Kb.
Pet hate: people who boast about the size and sophistication of their computer.

pcurtis

  • Sr. Member
  • ****
  • Posts: 354
Re: Database Paging
« Reply #5 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.
Windows 10 / Linux Mint 20
Laz 2.10.0
FPC 3.2.0

trev

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 1079
  • Former Delphi 1-7, 10.2 User
Re: Database Paging
« Reply #6 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.
o Lazarus v2.1.0 r63871, FPC v3.3.1 r47164, macOS 10.14.6, Xcode 11.3.1
o Lazarus v2.1.0 r64160, FPC v3.3.1 Nov 27 21:16:31, macOS 11.0.1 (aarch64), Xcode 12.2
o Lazarus v2.1.0 r61574, FPC v3.3.1 r42318, FreeBSD 12.1 amd64 (VMware VM)
o Lazarus v2.1.0 r61574, FPC v3.0.4, Ubuntu 20.04 (PD VM)

ttomas

  • Full Member
  • ***
  • Posts: 198
Re: Database Paging
« Reply #7 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
« Last Edit: October 16, 2020, 12:30:30 pm by ttomas »

pcurtis

  • Sr. Member
  • ****
  • Posts: 354
Re: Database Paging
« Reply #8 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.
Windows 10 / Linux Mint 20
Laz 2.10.0
FPC 3.2.0

MarkMLl

  • Hero Member
  • *****
  • Posts: 1476
Re: Database Paging
« Reply #9 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
Turbo Pascal v1 on CCP/M-86, multitasking with LAN and graphics in 128Kb.
Pet hate: people who boast about the size and sophistication of their computer.

bracara

  • New Member
  • *
  • Posts: 18
Re: Database Paging
« Reply #10 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
and https://www.sqlite.org/eqp.html

avra

  • Hero Member
  • *****
  • Posts: 2044
    • Additional info
Re: Database Paging
« Reply #11 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?
ct2laz - Conversion between Lazarus and CodeTyphon
bithelpers - Bit manipulation for standard types
pasettimino - Siemens S7 PLC lib

Martin_fr

  • Administrator
  • Hero Member
  • *
  • Posts: 6721
  • Debugger - SynEdit - and more
    • wiki
Re: Database Paging
« Reply #12 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)

PierceNg

  • New Member
  • *
  • Posts: 37
Re: Database Paging
« Reply #13 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 - 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

  • Sr. Member
  • ****
  • Posts: 354
Re: Database Paging
« Reply #14 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);

Windows 10 / Linux Mint 20
Laz 2.10.0
FPC 3.2.0

 

TinyPortal © 2005-2018