Hello,
I have an application which is used in several devices (average 15 devices, may go up to 150 devices from time to time).
Application has two databases. One database I call FIX, other I call INSERTS. They have below PRAGMA set for both:
FIX database is biggest. It is a read-only database (meaning nothing is inserted in it, only select statements run for a specific table. Application has read-write access to database anyway. Do not know how to reach it read-only with Lazarus). Depending on customers that specific table holds around 1.5 to 4 million records. Other tables are not much of a trouble. They are read just once when application is first run. That biggest table structure is as follows:
CREATE TABLE URUN(
Barkod Char(30) NOT NULL PRIMARY KEY,
UrunKodu Char(50),
UrunAciklamasi Char(60),
UrunGrubu Char(30),
Renk Char(20),
Beden Char(20),
Cup Char(20),
OlcuBirimi Char(10),
SeriNo Char(1),
Lot Char(1),
SKT Char(1));
create unique index Idx_URUN_Barkod on URUN(Barkod desc);
INSERTS database is very small. It only holds records that are inserted and these records are *moved* to a PC (MSSQL Database) before they reach high record counts. That database rarely holds 3500 records. Mostly record count is around 1000. Even though record count is not high, information in that database is important. It will be quite a big problem if all/some records are lost in it. That database has only one table with following structure:
CREATE TABLE TERMINAL_SAYIM(
KayitId Integer NOT NULL PRIMARY KEY AUTOINCREMENT,
AdresKodu Char(30) not null,
BelgeNo Char(30) not null,
BelgeTarihi DateTime not null,
LokasyonKodu Char(30) not null,
BolgeKodu Char(30) not null,
GozKodu Char(30) not null,
SSCC Char(30) not null,
SSCCKapandi Char(1),
Barkod Char(30) not null,
Miktar Integer not null check(miktar > 0),
OlcuBirimi Char(10),
Kontrolsuz Char(1) not null,
TekParca Char(1) not null,
TekParcaIndex Integer not null,
TerminalId Char(30),
KullaniciKodu Char(30),
OkutmaTarihSaati DateTime NOT NULL,
SeriNo Char(25),
Lot Char(25),
SKT Char(10),
IslemTuru Char(2),
Eslesti Char(1),
Nakledildi Char(1)
);
create index Idx_TERMINAL_SAYIM_AdresKodu on TERMINAL_SAYIM(AdresKodu asc);
create index Idx_TERMINAL_SAYIM_BelgeNo on TERMINAL_SAYIM(BelgeNo asc);
create index Idx_TERMINAL_SAYIM_LokasyonKodu on TERMINAL_SAYIM(LokasyonKodu asc);
create index Idx_TERMINAL_SAYIM_BolgeKodu on TERMINAL_SAYIM(BolgeKodu asc);
create index Idx_TERMINAL_SAYIM_GozKodu on TERMINAL_SAYIM(GozKodu asc);
create index Idx_TERMINAL_SAYIM_SSCC on TERMINAL_SAYIM(SSCC asc);
I know that more indexes are created for a table, slower insert operations will be. Unfortunately, I need these indexes because of frequent select statements I use which needs to be fast.
I would very much appreciate "safe" suggestions to make it faster INSERTS database. And, all kinds of suggestions to make it faster FIX database.
Devices application is run are all same and not bad in terms of technical specs. You can find it under Mobile Solutions section at
http://www.dsic.co.kr/ Model is DS5.
Other than default system applications, it is only that application running on these devices.
I can confirm that battery of the devices goes to 0% when application is running on it (when both databases are open) and closes its power completely. When that happen, we replace to fully charged batteries because operation must go on non-stop 7/24 when started. There happened no data loss during battery replacement so far. (fingers crossed) because sometimes it is long enough until replacement that device reboots OS when full batteries inserted instead of continuing where left of before power loss.
Due to the size of the FIX database, application & databases are run on a Micro SDCard. I have no idea about its specs because there are lots of different brands used for that. Test device I have uses Sandisk Ultra 8GB.
You may have additional questions, please do ask them.
Looking forward to reading all suggestions here.
Thanks & regards,
-Ertan