SQLite Forum

SQlite Speed
Login

SQlite Speed

(1) By Abin (AbinSaydah) on 2020-04-26 08:45:03 [link] [source]

Hello All

I have a table defined as follows:

"ID" INTEGER PRIMARY KEY AUTOINCREMENT, "Name" TEXT, "Cat" TEXT, "LData" BLOB

In my android app when I try to open this table it takes almose 5 or 6 seconds to load this data into a Memo in Delphi XE7 Firemonkey. The are about 650 records in this table.

I tried creatinf a different table with obly the BLOB field in it linked to this table by ID.

It doesnt improve matters.

Any sugestions kn how I can speed up this table?

Regards Abin

(2) By Simon Slavin (slavin) on 2020-04-26 13:17:33 in reply to 1 [link] [source]

Your problem is not with SQLite. It is with Delphi, or with some anti-virus or backup program running on your computer.

To investigate, download the SQLite shell tool and install it on the same computer. Use the shell tool to open that same database and execute the command

SELECT Name,Cat FROM MyTable;

(replace 'MyTable' with whatever the table is called). Does that operation also take 5 or 6 seconds ? If it's faster add the LData column in too and check the time for that.

(3) By Ryan Smith (cuz) on 2020-04-27 08:31:35 in reply to 1 [source]

SQLite cannot ever be that slow.

Having used Delphi somewhat I can promise you it can never be that slow either (unless, perhaps, you are compiling in Debug mode + other special flags).

My guess here would be to find what is opening the database, probably the Firemonkey tools or DB connector, then try to measure (by timing) which exact bit takes time.

It is quite possible there is a wait time or some timeout needing to happen before the connection is made, or most likely (as Simon suggested), there is some other tool (such as Antivirus or AntiRansomware or such) that prohibits file writes from being more speedy.

Whatever the result is - this is not an SQLite problem, so you might get a lot more valuable help from an Android or Delphi forum.

Good luck!

(4) By Abin (AbinSaydah) on 2020-04-27 09:06:37 in reply to 3 [link] [source]

Hi Ryan

You are quite right.

After some diging around I found this:

  1. it works fine if the app is generated for Windows

  2. It lags when I deploy to Android so it is definitely some or other firemonkey tool that delphi has installed thats the culprit. I am using Delphi XE7 which may be the issue

  3. The DB Browser for SQLite has ZERO lag.

Thanks for the help tho I really appreciate it

(5) By Ryan Smith (cuz) on 2020-04-27 14:02:38 in reply to 4 [link] [source]

The Firemonkey stuff usually works just fine, though XE7 is a little long in the tooth by now, but should still work ok.

Personally I've ditched the firemonkey DB tools long ago for SQLite specifically and re-made it with direct API calls to the library[1] - which I am happy to share[2] but isn't Android-ready.

Anyway, current tests via FPC on Android seem to work just fine and fast, so I still think it's not so much a Firemonkey problem as an Android file-system peculiarity with which Delphi XE7 either doesn't know how to play nice, or is specifically using a thing that is now frowned on by Android (Again, XE7 is old by now).

It's really hard to guess at, but Delphi-Android users/forum might know some ways to hunt it down.

Good luck!

[1] Check out https://sqlitespeed.com for a tool running it made in Delphi.

[2] In fact, the whole SQLitespeed project is going open-source, but moved from Delphi to FPC first, because while I like the compiler and RAD features, Delphi is to open-source-cross-platform as Oral-hygienist is to Dentist.