Jump to content

Recommended Posts

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.  

Share this post


Link to post
Share on other sites

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 by Mads

Share this post


Link to post
Share on other sites
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 by shoneill

Share this post


Link to post
Share on other sites
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😃

 

Share this post


Link to post
Share on other sites
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 by shoneill

Share this post


Link to post
Share on other sites
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.

29d4f6bc231881d6cd008f71f8094818_f130.png.eea1e24f3e6ec2ac5589de63c40efa6a.png

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

Edited by ShaunR

Share this post


Link to post
Share on other sites

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.

Bugfix SQLite.png

 

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

 

Have a good day :)

 

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
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...

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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".

1296681478_ModifiedExecuteSQL.png.9135239a7014fff66a4723320a886b93.png

Execute SQL (Array Variant Cluster results mod).vi

Share this post


Link to post
Share on other sites
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 by kosist90

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Similar Content

    • By Thang Nguyen
      Hi,
      I would like to know if I can write stream data into two different tables with SQLite at the same time or not? Currently my system has one camera. I store the capture images and particle measurement in one table. I have just add one more camera to the system and would like to do the same thing and write to a second table in a parallel process.  I wonder if this is possible or not.
      I use SQLite library.
      Thanks in advance.
       
    • By McQuillan
      Hi Everyone,
      I (re)watched James Powell's talk at GDevCon#2 about Application Design Around SQLite. I really like this idea as I have an application with lots of data (from serial devices and software configuration) that's all needed in several areas of the application (and external applications) and his talk was a 'light-bulb' moment where I thought I could have a centralized SQLite database that all the modules could access to select / update data.
      He said the database could be the 'model' in the model-view-controller design pattern because the database is very fast. So you can collect data in one actor and publish it directly to the DB, and have another actor read the data directly from the DB, with a benefit of having another application being able to view the data.
      Link to James' talk: https://www.youtube.com/watch?v=i4_l-UuWtPY&t=1241s)
       
      I created a basic proof of concept which launches N-processes to generate-data (publish to database) and others to act as a UI (read data from database and update configuration settings in the DB (like set-point)). However after launching a couple of processes I ran into  'Database is locked (error 5) ', and I realized 2 things, SQLite databases aren't magically able to have n-concurrent readers/writers , and I'm not using them right...(I hope).
      I've created a schematic (attached) to show what I did in the PoC (that was getting 'Database is locked (error 5)' errors).
      I'm a solo-developer (and SQLite first-timer*) and would really appreciate it if someone could look over the schematic and give me guidance on how it should be done. There's a lot more to the actual application, but I think once I understand the limitations of the DB I'll be able to work with it.
      *I've done SQL training courses.
      In the actual application, the UI and business logic are on two completely separate branches (I only connected them to a single actor for the PoC) 
      Some general questions / thoughts I had:
      Is the SQLite based application design something worth perusing / is it a sensible design choice? Instead of creating lots of tables (when I launch the actors) should I instead make separate databases? - to reduce the number of requests per DB? (I shouldn't think so... but worth asking) When generating data, I'm using UPDATE to change a single row in a table (current value), I'm then reading that single row in other areas of code. (Then if logging is needed, I create a trigger to copy the data to a separate table) Would it be better if I INSERT data and have the other modules read the max RowId for the current value and periodically delete rows? The more clones I had, the slower the UI seemed to update (should have been 10 times/second, but reduced to updating every 3 seconds). I was under the impression that you can do thousands of transactions per second, so I think I'm querying the DB inefficiently. The two main reasons why I like the database approach are:
      External applications will need to 'tap-into' the data, if they could get to it via an SQL query - that would be ideal. Data-logging is a big part of the application. Any advice you can give would be much appreciated.
      Cheers,
      Tom
      (I'm using quite a few reuse libraries so I can't easily share the code, however, if it would be beneficial, I could re-work the PoC to just use 'Core-LabVIEW' and James Powell's SQLite API)

    • By Munch
      Good Afternoon,
      I have been having an issue trying to resolve an error all afternoon, and hope that you might be able to help.
      I am trying to execute the follow SQL "
      SELECT m.name as tableName,
             p.name as columnName
      FROM sqlite_master m
      left outer join pragma_table_info((m.name)) p
           on m.name <> p.name
           where columnName = 'UploadedFlag'
      order by tableName, columnName
      " That returns a list of the tables that Contain the 'UploadedFlag' Column.   I have testing the SQL on both HeidiSQL and SQLite Expert Personal applications and run and return what I expect, however when I run it through LabVIEW I get a 402860 Error with the description " SQLITE_ERROR(1): near "(": syntax error " 
      If anyone could point me in the direction of the syntax error then I would be very greatful.
      Regards
      Mark
    • By drjdpowell
      For comment, here is a beta version of the next SQLite Library release (1.11).   It has a significant new feature of a "Parameter(s)" input to the "Execute SQL" functions.  This can be a single parameter or a cluster of multiple parameters.  Uses Variant functions and will be not as performance as a more explicit preparing and binding of a Statement object, but should be easier to code.

       
      drjdpowell_lib_sqlite_labview-1.11.0.86.vip
    • By drjdpowell
      Introductory video now available on YouTube: Intro to SQLite in LabVIEW
       
      SQLite3 is a very light-weight, server-less, database-in-a-file library. See www.SQLite.org. This package is a wrapper of the SQLite3 C library and follows it closely.

      There are basically two use modes: (1) calling "Execute SQL" on a Connection to run SQL scripts (and optionally return 2D arrays of strings from an SQL statement that returns results); and (2) "Preparing" a single SQL statement and executing it step-by-step explicitly. The advantage of the later is the ability to "Bind" parameters to the statement, and get the column data back in the desired datatype. The "Bind" and "Get Column" VIs are set as properties of the "SQL Statement" object, for convenience in working with large numbers of them.

      See the original conversation on this here.
       
      Now hosted on the NI LabVIEW Tools Network.
       
      ***Requires VIPM 2017 or later for install.***
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.