Increase SQLite Write Speed

[JScherer]

I have a VI that's writing data to disk from an FPGA. Unfortunatly, My SQLite writer is having difficulty keeping up with the write speed and I would like to know if there is anything I can do to keep up. Currently, I'm able to write 10,000 rows in 60ms. I would love to get down to 30ms or faster if possible.

To write the data, I have the following steps:

• Read FPGA data as a 2D array in a producer loop, pass as variant to consumer loop process state (time start, t=0)
• Convert 2D array to 1D array of clusters (to better represent "real data,"), run a fast check and pass as variant to logging state (5ms, t=5ms)
• Log 1D array of clusters (as=10,000) with drjdpowell's SQLite library (60ms, t=65ms )

Please take a look at this simplified VI to see if there's anything I can do.

[Artem.SPb]

You can try to form one request into one large string:

INSERT INTO table1 (column1,column2 ,..)
VALUES
(value1,value2 ,...),
(value1,value2 ,...),
...
(value1,value2 ,...);

Or you need a faster HDD 🙂

How long do you plan to write this data? If not for a long time, then perhaps you can accumulate data in memory, and writing them down after reading.

[JScherer]

Unfortunately, we're taking data over a few days so we can't just buffer to memory.

I tried the bulk data you mentioned and it seems my performance decreased from 60 to 100ms.

Updated VI below

[JScherer]

Also, In terms of getting a faster hard drive, we're using an SSD and writing at about 500kb (currently over 60ms) for 8.3Mb/s As I understood it, SSD's can write in the 100's of megabytes.

[Artem.SPb]

Measure the write speed of a binary file.
And a workaround for solving the problem is to write a binary file, and after the experiment, transfer it to the database.

[IvanLis · Accepted solution]

PRAGMA synchronous:

0 | OFF

1 | NORMAL

2 | FULL

PRAGMA temp_store:

0 | DEFAULT

1 | FILE

2 | MEMORY

Use:

PRAGMA synchronous = 0
PRAGMA temp_store = 2

But be careful, this is a quick but not safe way to speed up.

[JScherer]

---

@IvanLis wrote:

PRAGMA synchronous:

0 | OFF

1 | NORMAL

2 | FULL

PRAGMA temp_store:

0 | DEFAULT

1 | FILE

2 | MEMORY

Use:

PRAGMA synchronous = 0
PRAGMA temp_store = 2

But be careful, this is a quick but not safe way to speed up.

---

Thanks!

With these changes I went down from an average of 60ms to 35ms write speed.

FYI: Trying the binary write, got me 12ms, so if this continues to be an issue, I can do the raw file with a separate conversion utility.

[FireFist-Redhawk]

You seem to have gotten the speed that you want, but I'll say what I first thought of anyway.

Your VI that writes a row of data, call it asynchronously and enable re-entrant execution so multiple rows can be written simultaneously.

[JScherer]

---

@FireFist-Redhawk wrote:

You seem to have gotten the speed that you want, but I'll say what I first thought of anyway.

Your VI that writes a row of data, call it asynchronously and enable re-entrant execution so multiple rows can be written simultaneously.

---

I'll have to test that out if I run into the issue again. I'm concerned about having multiple writers to the same file though, but I suppose with unique transaction IDs for each Async call would mitigate.

[IvanLis]

---

@JScherer wrote:

---

FYI: Trying the binary write, got me 12ms, so if this continues to be an issue, I can do the raw file with a separate conversion utility.

---

I would recommend using Bynary TDMS.
The access speed is actually the same, but it is much easier to organize data writing and reading.