[CR] SQLite Library

[drjdpowell]

I don't think you can match the raw pull-from-file performance of something like TDMS (unfragmented, of course).   SQLite advantages is its high capability, and if you aren't using those advantages then then it will not compare well.  

[Mads]

Sure. I did not expect the same speed, I was just hoping that I would still be able to do such a retrieval fast enough for it to still feel near instantaneous to the users. That would allow for it to be maybe 10x slower than the chunk-based binary file format.

Your library is the only one I have found that also runs on RT, and it is also, I see now, the fastest one, just 20x slower than my current solution. I might just need to adjust to that though, to enjoy the many other benefits 🙂

Edited February 14, 2020 by Mads

[shoneill]

On 2/12/2020 at 2:53 PM, Mads said:

To give a simple example; if I have a table of 350 000 rows containing a time stamp (real) and 3 columns of  measurements (real),  and I select a time (sub)period from this table; it takes up to 0,6 seconds to retrieve the measurements on my test machine (using either a single SQL Execute 2D dbl, or the Select many rows template). For comparison the current binary file solution can locate and read the same sub-periods with a worst case of 0,03 seconds. 20x times faster.  This difference would not be an issue if the accumulated time per batch of request was still sub-second, but the end users might combine several requests, which means the total waiting time goes from less than a second ("instantaneous") to 5-30 seconds...

I'll just drop a link to a post on the dark side. I made that post after doing benchmark comparisons between TDMS and SQLite (sound familiar?).

It might be important to take into account that "first read" performance and "typical" performance can be orders of magnitude different. And the differences my be a function of the file type.

OTOH, as mentioned in the post, the same mechanism can be (ab)used to create a 64-bit file cache when using 32-bit LabVIEW, so that's nice.

Edited February 18, 2020 by shoneill

[Mads]

1 hour ago, shoneill said:

I'll just drop a link to a post on the dark side. I made that post after doing benchmark comparisons between TDMS and SQLite (sound familiar?).

It might be important to take into account that "first read" performance and "typical" performance can be orders of magnitude different. And the differences my be a function of the file type.

OTOH, as mentioned in the post, the same mechanism can be (ab)used to create a 64-bit file cache when using 32-bit LabVIEW, so that's nice.

Caching did cross my mind during the testing earlier, so I tried running the tests on "fresh" data to get the worst case scenarios. It can mess thing up quite easily though I agree. Caching does improve both alternatives in this test case, and logically mostly for the non-sql solution, but on my machine not nearly as much as you observed during the mentioned test.

PS: A similarly interesting performance nugget that can be nice to know about is the effect of branch prediction😃

 

[shoneill]

22 hours ago, Mads said:

PS: A similarly interesting performance nugget that can be nice to know about is the effect of branch prediction😃

I did NOT know that branch prediction could have such an effect. Nice post. Thanks.

That article brings some old memories to light. I remember during the old "Prime number" coding challenge on the NI site there were times where adding a dummy case to my case structures during processing produced measurable changes in execution speed. I couldn't explain it, but I accepted the improved execution times and carried on with my business.  Seeing how the execution was more or less deterministic, could it be that I was moving between "good" and "bad" patterns for the case structure. IIRC, the results were tested on a Pentium 4, with a 31-stage pipeline.  May be completely unrelated, but that was always a point which stuck with me that I couldn't really explain.

Edited February 19, 2020 by shoneill

[ShaunR]

On 2/12/2020 at 1:53 PM, Mads said:

I was thinking of replacing a proprietary chunk based time series log file format that we use with SQLite, using this library. From a test I did some time ago I thought read performance would not be an issue (unlike for TDMS for example, which performs really badly due to the fragmentation you get when logging large non-continuous time series ), but now that I redid the tests I see that the read times get quite high (writes are less of an issue in this case as we normally just write smaller batches of data once a minute). I expected the database to be much slower, but the difference is of a magnitude that could be troublesome (no longer a real-time response).

To give a simple example; if I have a table of 350 000 rows containing a time stamp (real) and 3 columns of  measurements (real),  and I select a time (sub)period from this table; it takes up to 0,6 seconds to retrieve the measurements on my test machine (using either a single SQL Execute 2D dbl, or the Select many rows template). For comparison the current binary file solution can locate and read the same sub-periods with a worst case of 0,03 seconds. 20x times faster.  This difference would not be an issue if the accumulated time per batch of request was still sub-second, but the end users might combine several requests, which means the total waiting time goes from less than a second ("instantaneous") to 5-30 seconds...

Perhaps I was using @ShaunR 's library back when I was able to get this done "instantaneously", but that seems to no longer exist (?), and in this case I need a solution that will run on Linux RT too...Should it be possible to read something like this any faster, and/or do anyone have any suggestions on how to improve the performance? The data will have to be on disk, the speedup cannot be  based on it being in cache / memory...

You have to be logged in to see the SQLite software.

The main performance criteria for SQLite is the number of rows and/or columns returned/inserted, although there have been significant improvements in recent versions (~15% over 5 years). If you look at the performance graphs, you will see that 0.6 seconds equates to about 450k rows (with 2 columns). The performance test in the Sqlite API for LabVIEW library is based on 10k rows so that you can get repeatable figures and that typically yields 10s of milliseconds. Less than that, and LabVIEW timings become dominant for the purpose of that test.

If you are dumping entire tables and performance is a requirement, then a relational database is the wrong tool.

Edited February 19, 2020 by ShaunR

[Olivier Jourdan]

Hi there,

I'm experiencing a weird behavior with foreign key constraint.

I'm inserting a row in a table with a bad "link" (id in parent table doesn't exist). I'm expecting an error to be thrown by EXEC but not error and the "bad" row is inserted !?

If I execute the same SQL query in SQLiteExpert an error is thrown as expected...

 

Note 1: I have UNIQUE constraint in my table that correctly throw error when value inserted is not  unique

Note 2: I found the message below related to error propagation, but in my case return code is equal to 0 

On 4/30/2017 at 4:01 PM, drjdpowell said:

Hi Max,

The actual bug, I think, was that “Step.vi” was ignoring errors outside of 1..99.  It has a case structure that only passes those codes to the error-handling subVI (written, originally, because 100 and 101 are not errors).  I have changed it so that the extended error codes are now passed (see version 1.7.3 just added to the LAVA CR).  I tested it by making a foreign key error and it now works.

 

If someone has an idea of what I'm doing wrong, I'd be pleased to hear from him.

 

Have a good day

 

 

[LogMAN]

Did you enable foreign key constraints?

You need to set

PRAGMA foreign_keys = ON

Otherwise foreign key constrains are ignored.

https://www.sqlite.org/pragma.html#pr>agma_foreign_keys

 

[Olivier Jourdan]

Oups sorry fr the radio silence, I didn't receive notification of your post...

I'll give a try to that and keep you posted.

[Olivier Jourdan]

On 2/20/2020 at 12:27 PM, LogMAN said:

Did you enable foreign key constraints?

You need to set

PRAGMA foreign_keys = ON

Otherwise foreign key constrains are ignored.

https://www.sqlite.org/pragma.html#pr>agma_foreign_keys

 

You were right, thank you for your help.

[AlexElb]

Hey there,

first of all, this is my first post on lavag. I've already learned a lot here and would like to say hello and thank you all for all the information and great toolkits on lavag.

I am just playing around with sqlite for two days after I watched this and that video. Very impressive.

I have a questions about using timestamps. I would like to store timestamps in UTC but display them in LV in the different users timezones. Please excuse my question for its triviality and probably its more a general problem than a LV one. I hope it is okay to ask it here.

My insights until now:

• using "Bind Timestamp(Real) you simple use "to dbl", so it will need only 8 bytes: minimal storage used, but still 3 decimal places - perfect.
• LabVIEW stores the time in UTC format, but displays the time in Local Time format (link). It can be converted manually in LV.7
• If the timestamp was stored as a DBL, "to timestamp" is used.
• The more the database can do, the less I have to do in LV

Until here everything looks okay, but then I got confused: According to https://www.sqlite.org/datatype3.html sqlite will store it as a REAL as Julian day numbers. How can the LV UTC format starting 01-01-1904 00:00:00 work together with the Julian day format?

 I tried the following and insert a standard lv current timestamt to the second colum.

DROP TABLE IF EXISTS times;
CREATE TABLE times(
  dbtimeutc REAL, 
  lvcurrenttime REAL);
CREATE TRIGGER IF NOT EXISTS insert_Timer AFTER  INSERT ON times  -- Julian REAL format
     BEGIN
      UPDATE times SET dbtimeutc = julianday('now')  WHERE rowid = new.rowid;
     END;

After a select, first column shows me a date in 1904, second is correct.

I try to ask it easy:

• What do I have to do for storing a UTC Datetime as a REAL (as Julian Day) using LVs Get Datetime In Seconds Funtion? Or is there not any extra work needed?
• What do I have to do for reading that value out to LV in UTC, in Local Time, or any other timezone? It should be possible in sqlite without havin to convert it manually in LV, shouldn't it?
• How to create such a timestamp in sqlite? Because of course I want all timestamps in my database to be in the same format.
• How to view that value as a human readable Datestring within SQLite Expert Personal? (only nice to have)

Thanks in advance for any hints.

[drjdpowell]

First thing is SQLite has no timestamp data type.  You are responsible for deciding what to use as a time representation.  SQLite includes some functions that help you convert to a few possibilities, including Julian day, but I, in this library, choose to use the standard LabVIEW dbl-timestamp.  The "get column as timestamp" function will attempt to convert to a time based on the actual datatype:

Text: iso8601

Integer: Unix time seconds

Float: LabVIEW dbl-timestamp

Binary: LabVIEW full timestamp

The first two match possibly forms supported by the time functions in SQLite, but the later two are LabVIEW ones.

You are free to pick any format, and can easily write your own subVIs to convert.  You can even make a child class of "SQL Statement" that adds "Bind Timestamp (Julian Day)" and "Get Column (Julian Day)" if you like.

[kosist90]

Is there a way to read from database array of clusters? It is possible to read single row as cluster, but I'm curious whether it is possible to read all rows to clusters array?.. Thanks a lot in advance!

[drjdpowell]

Do you not want to prepare a statement and build your clusters in a loop?   Eventually, a VIM could do this in a single VI, but the library is currently in LabVIEW 2013 so no VIMs.

[kosist90]

On 6/26/2020 at 1:28 PM, drjdpowell said:

Do you not want to prepare a statement and build your clusters in a loop?   Eventually, a VIM could do this in a single VI, but the library is currently in LabVIEW 2013 so no VIMs.

Thank you for the answer! Actually, I'd like to prepare some "universal" VI, which could be reused to read different cluster types, for different tables. My colleague has implemented code which does something similar as reading of variant is done (Execute Query polymorphic VI), but anyway it has limitation of column numbers, and it uses some Vis from your toolkit which have "private" scope. So actually then we can not distribute it, because it is not part of toolkit's package... As we use LabVIEW 2015, VIM is also not a solution for us... The only workaround now seems to be to convert 2D strings array to cluster somehow - but that is also not the best solution...

[drjdpowell]

Have a look at the latest version 1.11 release.   It adds a "Parameters" input to "Execute", which accepts a cluster of parameters to Bind.  The tool you want would use similar Variant techniques.   Note, however, that using Variants adds possibly not-insignificant overhead to each row returned, as compared to getting a cluster using the lower-level API, so I would not recommend it where performance really matters.

[kosist90]

Actually, we have implemented it in the following way - I attach snippet, and VI itself. VI is broken, b/c it is not part of the library...

And the issue is that we are kind of able to use this solution, but then we need to manage somehow the fact, that it should be part of SQLite library. And then, we will not be able to update it from VIPM, b/c we will loose the changes.

Could you please suggest, whether such solution could be the part of the original toolkit? If so, my colleague could open pull request, or something...

The original VI (which returns just single cluster) was modified in a way, that now multiple rows are read, and built into array. And then, this array is possible to convert to array of clusters using "Variant to Data".

Execute SQL (Array Variant Cluster results mod).vi

[drjdpowell]

Seems reasonable; added issue.   https://bitbucket.org/drjdpowell/sqlite-labview/issues/6/add-a-execute-sql-1d-cluster-results

Unfortunately, I've just released a new version, but it will go into next version.

[drjdpowell]

@kosist90

Here is a beta version, that you can test if you like:

drjdpowell_lib_sqlite_labview-1.12.0.89.vip

It's not implemented exactly the same way as your example, but it should work in the same places.  See the example "SQLite Parameters in Execute".

[kosist90]

On 7/10/2020 at 4:56 PM, drjdpowell said:

@kosist90

Here is a beta version, that you can test if you like:

drjdpowell_lib_sqlite_labview-1.12.0.89.vip 4.36 MB · 0 downloads

It's not implemented exactly the same way as your example, but it should work in the same places.  See the example "SQLite Parameters in Execute".

Thanks a lot! We're going to try it, but it seems that this is what we need, thank you!

Upd: I've installed and tested it. It works fine, thank you - data is converted to array of clusters, so now it is possible to read all table's rows and convert it to array of clusters.

Edited July 13, 2020 by kosist90

[Olivier Jourdan]

Hey there,
I've been successfully and happily SQLite Library for months to save application configuration. But these days I'm facing some issues when my last app is running on different computers. Sometimes, it seems that the configuration is not completely or correctly read.
When I'm applying configuration, I read DB multiple times in parallel at different locations. As far as I know, SQLite manages that properly. But as far as the issue occurs randomly, I have to suspect parallel executions issue.

Note that, in addition to usual tables, I'm using the Attribute feature of the SQLite Library with which I had an issue with parallel execution.

Does anybody already add this kind of issue, or am I misusing SQLite or the SQLite library?

Thanks in advance for your help.

Olivier

[Olivier Jourdan]

I made some tests with attributes.

When I read attributes in parallel, I have the following error that pops-up randomly:

Quote

 

Error 402880 occurred at SQLite.Attrib.lvlib:Connection (with Attributes).lvclass:Get Attribute (DBL).vi:Bind Text
SQL--> "SELECT Value FROM Attributes WHERE Name=?"
MAIN db name-->"C:\Users\team\Desktop\test.bench"

SQLITE_MISUSE(21): not an error

Complete Call Chain:
SQLite.Attrib.lvlib:Connection (with Attributes).lvclass:Get Attribute (DBL).vi
Config Db.lvlib:Get Cabinet Watchdog Low Level.vi
Test Db Parallel Access On Attributes.vi

Possible reason(s):

SQLite returned SQLITE_MISUSE(21).  May indicate that a method has been used out of order, such as trying to "Bind" to a statement after calling "Step" and before "Reset". See www.sqlite.org/rescode.html#misuse

 

It seems that statement used to read the attribute could be "broken."
I continue my investigation and keep you posted.

[Olivier Jourdan]

I think I got the thing.
Get Attribute function is not reentrant, a good thing in our case, but Get Attribute is a polymorphic VI.
If I read 3 Dbl attributes in parallel, everything is fine. If I try to get a 4th string attribute, I get the error.
As far as I know, I can't see another thing to do but implement a semaphore to prevent this error.

 

[EDIT]Same thing should apply for Set Attribute[/EDIT]

Edited August 11, 2020 by Olivier Jourdan
add info

[Olivier Jourdan]

I implemented the fix for get attribute polymophic VIs. It works on my test.

If it can help, I attached the fix (just replace the Lookup folder in ..\vi.lib\drjdpowell\SQLite Library).

Fix is made with LV2019.

Lookup.zip

[drjdpowell]

2 hours ago, Olivier Jourdan said:

I think I got the thing.
Get Attribute function is not reentrant, a good thing in our case, but Get Attribute is a polymorphic VI.
If I read 3 Dbl attributes in parallel, everything is fine. If I try to get a 4th string attribute, I get the error.
As far as I know, I can't see another thing to do but implement a semaphore to prevent this error.

Thanks Oliver for pointing out this problem.  I actually don't myself use a single Connection in parallel (if in parallel, I use multiple Connections), but the library should support this.  The problem comes not from the single Connection, but from the preprepared SQL Statements for attribute access.  They are preprepared for speed, but this means one needs to serialise use of the Statement.  

I've been burned by Semaphores in the past and won't use them, but I'll use some other method.

 

Issue: https://bitbucket.org/drjdpowell/sqlite-labview/issues/10/attribute-parallel-access-bug

Edited August 11, 2020 by drjdpowell