Recent

Author Topic: Suggestions for making SQLite database operations faster  (Read 10322 times)

ertank

  • Full Member
  • ***
  • Posts: 240
Suggestions for making SQLite database operations faster
« on: April 12, 2016, 11:18:42 pm »
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  [Select]
  1. 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  [Select]
  1. CREATE TABLE URUN(
  2.  Barkod Char(30) NOT NULL PRIMARY KEY,
  3.  UrunKodu Char(50),
  4.  UrunAciklamasi Char(60),
  5.  UrunGrubu Char(30),
  6.  Renk Char(20),
  7.  Beden Char(20),
  8.  Cup Char(20),
  9.  OlcuBirimi Char(10),
  10.  SeriNo Char(1),
  11.  Lot Char(1),
  12.  SKT Char(1));
  13.  
  14. create unique index Idx_URUN_Barkod on URUN(Barkod desc);
  15.  

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  [Select]
  1. CREATE TABLE TERMINAL_SAYIM(
  2.  KayitId Integer NOT NULL PRIMARY KEY AUTOINCREMENT,
  3.  AdresKodu Char(30) not null,
  4.  BelgeNo Char(30) not null,
  5.  BelgeTarihi DateTime not null,
  6.  LokasyonKodu Char(30) not null,
  7.  BolgeKodu Char(30) not null,
  8.  GozKodu Char(30) not null,
  9.  SSCC Char(30) not null,
  10.  SSCCKapandi Char(1),
  11.  Barkod Char(30) not null,
  12.  Miktar Integer not null check(miktar > 0),
  13.  OlcuBirimi Char(10),
  14.  Kontrolsuz Char(1) not null,
  15.  TekParca Char(1) not null,
  16.  TekParcaIndex Integer not null,
  17.  TerminalId Char(30),
  18.  KullaniciKodu Char(30),
  19.  OkutmaTarihSaati DateTime NOT NULL,
  20.  SeriNo Char(25),
  21.  Lot Char(25),
  22.  SKT Char(10),
  23.  IslemTuru Char(2),
  24.  Eslesti Char(1),
  25.  Nakledildi Char(1)
  26. );
  27.  
  28. create index Idx_TERMINAL_SAYIM_AdresKodu    on TERMINAL_SAYIM(AdresKodu asc);
  29. create index Idx_TERMINAL_SAYIM_BelgeNo      on TERMINAL_SAYIM(BelgeNo asc);
  30. create index Idx_TERMINAL_SAYIM_LokasyonKodu on TERMINAL_SAYIM(LokasyonKodu asc);
  31. create index Idx_TERMINAL_SAYIM_BolgeKodu    on TERMINAL_SAYIM(BolgeKodu asc);
  32. create index Idx_TERMINAL_SAYIM_GozKodu      on TERMINAL_SAYIM(GozKodu asc);
  33. create index Idx_TERMINAL_SAYIM_SSCC         on TERMINAL_SAYIM(SSCC asc);
  34.  

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

  • Jr. Member
  • **
  • Posts: 61
Re: Suggestions for making SQLite database operations faster
« Reply #1 on: April 13, 2016, 01:20:56 am »
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:
I thought Delphi was expensive until I learned the price of ExtJS

sky_khan

  • Guest
Re: Suggestions for making SQLite database operations faster
« Reply #2 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

ertank

  • Full Member
  • ***
  • Posts: 240
Re: Suggestions for making SQLite database operations faster
« Reply #3 on: April 13, 2016, 09:05:24 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

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

vincococka

  • Full Member
  • ***
  • Posts: 101
Re: Suggestions for making SQLite database operations faster
« Reply #4 on: April 28, 2016, 12:29:49 am »
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
---
  Guide me God and I`ll find you

Graeme

  • Hero Member
  • *****
  • Posts: 1430
    • Graeme on the web
Re: Suggestions for making SQLite database operations faster
« Reply #5 on: April 28, 2016, 01:48:12 am »
I would very much appreciate "safe" suggestions to make it faster

Simply throw it harder.  ;)
--
fpGUI Toolkit - a cross-platform GUI toolkit using Free Pascal
http://fpgui.sourceforge.net/

ertank

  • Full Member
  • ***
  • Posts: 240
Re: Suggestions for making SQLite database operations faster
« Reply #6 on: May 03, 2016, 10:37:20 pm »
- for writeable DB - use journal (not in-memory - see PRAGMA / WAL_MODE, maybe consider SYNC).. but those cheap SD cards can lie sometimes :D

Would you give me some SQL code samples about WAL_MODE, please?

ertank

  • Full Member
  • ***
  • Posts: 240
Re: Suggestions for making SQLite database operations faster
« Reply #7 on: May 06, 2016, 10:04:59 am »
My real-life tests with device itself and suggestion of vincococka, I end up with following setup:
PRAGMA page_size=4096  // advice ofvincococka
PRAGMA journal_mode=MEMORY // anything other than this writes to microSD card and that slows things.
PRAGMA temp_store=2 // makes things slightly faster
PRAGMA locking_mode=EXCLUSIVE  // I have read-only databases exclusive lock is faster on select statements

Also, one of my read-only databases, I am creating my biggest table using "WITHOUT ROWID" statement. I am told that this will not add sqlite specific rowid column in that table. It will be smaller in size, and faster select statements in the end.

Since above settings is kind of a risk, I put an IdleTimer on my form and if device is idle for 3 seconds *and* there is a need for a commit, it commits.

One more thing I will do is to place a dedicated TSQLQuery on my form and prepare insert statements & use parameters to make the insert. I am not 100% positive, but that I read several posts about that speeds up things. This is something I still need to test.

I believe, this is as far as I can get from my application since it is not simply making inserts, but also several selects and checks before this inserts.
« Last Edit: May 06, 2016, 10:08:07 am by ertank »

vincococka

  • Full Member
  • ***
  • Posts: 101
Re: Suggestions for making SQLite database operations faster
« Reply #8 on: May 10, 2016, 09:49:37 pm »
It seems to me that what you need is some sort of work "journal" and make things async.
Consider changing design. You should commit after every "logical block" (better yet atomic operation) and not wait 3 seconds. Crazy things can happen in embedded world :).

One idea for that read database : open database handle to that DB on application start, copy data from read DB to temporary table which will reside in memory during runtime. Maybe it can speed things up a little bit. But please check application memory usage if you have lot of data. WinCE got some limits on running process memory.
« Last Edit: May 10, 2016, 09:54:17 pm by vincococka »
---
  Guide me God and I`ll find you

ertank

  • Full Member
  • ***
  • Posts: 240
Re: Suggestions for making SQLite database operations faster
« Reply #9 on: May 10, 2016, 10:37:30 pm »
Hi Vincococka,

I didn't put in my post everything I tested. It would be too long. I have tried different journal modes and async, etc. Thing is, if something is written as a file to OS disk structure, that slows things down. Be that WAL mode or synchronous=off. They all write something to disk even during transaction. That's why I am keeping everything that I can in memory. Unfortunately, microSD cards are not that much of a performance devices.

3 seconds is something I came up. One of my colleague suggested 700 milliseconds idle time. We will see during real-life tests about optimum idle time, I believe.

Moreover, I have the feeling that I will be checking device remaining battery percentage and stop operations until a replace/recharge to prevent one more possible problem.

Regarding read database, Sometimes it goes up to 1.3GB in size (4 million records), so keeping it's contents in virtual table is not possible for me.

Btw, what I would like to learn is it slowing things down to use power of 4096 for page_size for my write database without loosing performance. Since database is on a microSD card, and I recall your suggestion about using page_size 4096.

I would like to use higher for my write database since that database holds 750 records on average. I can make a test and find myself a higher page_size and that database file doesn't need to be increased in size (at least most of the time).