How can I make faster sqlite db

General
1

Hey guys. We done our project and using sqlite db. But it is very slow to getting data from DB. just 17mb data takes more than 2 minutes. Is that normal or any configuration for sqlite to be faster ?

Thanks for oyour advance.

2

How many rows?
How big is each row?
What it the SQL statement getting the data?
Which Pi are you using?
What version of rasbpian are you using?
What else is running on the pi?

3

Have you checked the logs?

4

On this topic, I have started to think about maybe migrating this node to use the better-sqlite3 library as it claims to be much faster. But I have yet to try it. So if someone wanted to step in and do so then raise a pull request that would be a great help.

1 Like
5

Almost 68k rows
the size of db is 17 mbyte

sql statement is :

sql="SELECT zaman,durum FROM lazer1 WHERE zaman >= "+baslangic+" AND zaman < "+bitis;
msg.topic=sql;

The pi is pi 3 16gb

Linux version 4.14.71-v7+ (dc4@dc4-XPS13-9333) (gcc version 4.9.3 (crosstool-NG crosstool-ng-1.22.0-88-g8460611)) #1145 SMP Fri Sep 21 15:38:35 BST 2018

I only use node-red not more..

Untitled
Untitled631Ă—879 55 KB

6

Where is the log and where is lopcation of logs ?

7

That will totally depend on where and how you run Node-RED. But the docs on the website should give you a clue

8

So it takes 2 minutes to process 68,000 rows. So that’s 120 sec/ 68000 = 0.001764705882353 seconds per row

That seems pretty fast to me

p.s. where did you come up with the pi having 16 GB?

9

Have you got an index on the zaman column? If not then it will have to read every single record to find the ones that fit the WHERE clause.

10

Just what I was about to ask :slight_smile:

11

You may have 16GB of storage but not of memory (RAM) - SQLite is largely memory based.

The Raspberry Pi 3 Model B is the earliest model of the third-generation Raspberry Pi. It replaced the Raspberry Pi 2 Model B in February 2016. See also the Raspberry Pi 3 Model B+, the latest product in the Raspberry Pi 3 range.

  • Quad Core 1.2GHz Broadcom BCM2837 64bit CPU
  • 1GB RAM
  • BCM43438 wireless LAN and Bluetooth Low Energy (BLE) on board
  • 100 Base Ethernet
  • 40-pin extended GPIO
  • 4 USB 2 ports
  • 4 Pole stereo output and composite video port
  • Full size HDMI
  • CSI camera port for connecting a Raspberry Pi camera
  • DSI display port for connecting a Raspberry Pi touchscreen display
  • Micro SD port for loading your operating system and storing data
  • Upgraded switched Micro USB power source up to 2.5A

The 3B+ has "1GB LPDDR2 SDRAM"