Forum > Windows CE

Suggestions for making SQLite database operations faster

(1/2) > >>

ertank:
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:

--- Code: Pascal  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---PRAGMA page_size=65536;
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:

--- Code: Pascal  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---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:

--- Code: Pascal  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---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

lazjump:
To make the FIX database read only, you could use PRAGMA query_only (https://www.sqlite.org/pragma.html#pragma_query_only)

To make writes in INSERTS database faster, you could:

* Wrap your insert, update & delete SQLs inside transactions
* Play with PRAGMA synchronous (https://www.sqlite.org/pragma.html#pragma_synchronous) to find good tradeoff between speed and safety (best speed = worst safety; worst speed = best safety)

sky_khan:
Did you benchmark all indexes to see that if you really need all of them ? Indexes without diversity may not increase speed.
e.g If an indexed column has only 5-10 different values it may not be a real necessary index

ertank:

--- Quote from: SkyKhan on April 13, 2016, 03:24:48 am ---Did you benchmark all indexes to see that if you really need all of them ? Indexes without diversity may not increase speed.
e.g If an indexed column has only 5-10 different values it may not be a real necessary index

--- End quote ---

I didn't benchmark indexes. Is it possible you explain me how to do that? I don't know myself.

vincococka:
Hints from quiet fresh experience:
- use pagesize 4096
- use SLC SD cards / Sandisk Xtreme Pro SD cards - SLC are built for inndustrial needs, but those Xtreme Pro have wear-leveling as only one card on market I`ve found
- consider multi-table design in your read-only DB and delete index (which you don`t need).
  - in case you don`t have SQLite connection permanently open it`s worhless - your caches are already gone.
 || or create some sort of multilevel-index for ranges of 2-3 thousand items
- also insert data in table already sorted by "barkod"

- for writeable DB - use journal (not in-memory - see PRAGMA / WAL_MODE, maybe consider SYNC).. but those cheap SD cards can lie sometimes :D

Navigation

[0] Message Index

[#] Next page

Go to full version