# How can I make faster sqlite db

**URL:** <https://discourse.nodered.org/t/how-can-i-make-faster-sqlite-db/6159>\
**Category:** General\
**Created:** [27 December 2018 11:00 UTC](https://discourse.nodered.org/t/how-can-i-make-faster-sqlite-db/6159 "2018-12-27T11:00:05Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![wtrk](https://avatars.discourse-cdn.com/v4/letter/w/97f17d/32.png) [@wtrk](https://discourse.nodered.org/u/wtrk)\
**Post date:** [27 December 2018 11:00 UTC](https://discourse.nodered.org/t/how-can-i-make-faster-sqlite-db/6159/1 "2018-12-27T11:00:05Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [27 December 2018 11:29 UTC](https://discourse.nodered.org/t/how-can-i-make-faster-sqlite-db/6159/2 "2018-12-27T11:29:09Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![ukmoose](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ukmoose/32/13_2.png) [@ukmoose](https://discourse.nodered.org/u/ukmoose)\
**Post date:** [27 December 2018 11:29 UTC](https://discourse.nodered.org/t/how-can-i-make-faster-sqlite-db/6159/3 "2018-12-27T11:29:48Z")

</div>

Have you checked the logs?

---

<div class="post-metadata">

**Author:** ![dceejay](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dceejay/32/38_2.png) [@dceejay](https://discourse.nodered.org/u/dceejay)\
**Post date:** [27 December 2018 11:34 UTC](https://discourse.nodered.org/t/how-can-i-make-faster-sqlite-db/6159/4 "2018-12-27T11:34:16Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![wtrk](https://avatars.discourse-cdn.com/v4/letter/w/97f17d/32.png) [@wtrk](https://discourse.nodered.org/u/wtrk)\
**Post date:** [27 December 2018 12:23 UTC](https://discourse.nodered.org/t/how-can-i-make-faster-sqlite-db/6159/5 "2018-12-27T12:23:01Z")

</div>

> [@zenofmud](#):
>
> 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?

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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/8/88e7005f74b108f7c61341d96bcbd22a7202dcb9.png)

---

<div class="post-metadata">

**Author:** ![wtrk](https://avatars.discourse-cdn.com/v4/letter/w/97f17d/32.png) [@wtrk](https://discourse.nodered.org/u/wtrk)\
**Post date:** [27 December 2018 12:23 UTC](https://discourse.nodered.org/t/how-can-i-make-faster-sqlite-db/6159/6 "2018-12-27T12:23:27Z")

</div>

Where is the log and where is lopcation of logs ?

---

<div class="post-metadata">

**Author:** ![ukmoose](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ukmoose/32/13_2.png) [@ukmoose](https://discourse.nodered.org/u/ukmoose)\
**Post date:** [27 December 2018 13:08 UTC](https://discourse.nodered.org/t/how-can-i-make-faster-sqlite-db/6159/7 "2018-12-27T13:08:01Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [27 December 2018 13:41 UTC](https://discourse.nodered.org/t/how-can-i-make-faster-sqlite-db/6159/8 "2018-12-27T13:41:32Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [27 December 2018 14:06 UTC](https://discourse.nodered.org/t/how-can-i-make-faster-sqlite-db/6159/9 "2018-12-27T14:06:43Z")

</div>

> [@wtrk](#):
>
> "SELECT zaman,durum FROM lazer1 WHERE zaman \>= "+baslangic+" AND zaman \< "+bitis

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.

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [27 December 2018 14:07 UTC](https://discourse.nodered.org/t/how-can-i-make-faster-sqlite-db/6159/10 "2018-12-27T14:07:10Z")

</div>

> [@Colin](#):
>
> Have you got an index on the zaman column

Just what I was about to ask 🙂

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [27 December 2018 14:10 UTC](https://discourse.nodered.org/t/how-can-i-make-faster-sqlite-db/6159/11 "2018-12-27T14:10:09Z")

</div>

> [@wtrk](#):
>
> The pi is pi 3 16gb

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+](https://www.raspberrypi.org/products/raspberry-pi-3-model-b-plus/), 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"