Jump to content

Looking for option to speed up my usage of SQLite


Recommended Posts

Currently, my vision software will do a compression (subtract to background image then count zero) and write to a table in SQLite file. Since we want to speed up the process, I write entire image in a temporary table. Then at the end, I read the table, do compression and write to the actual table, then drop the temporary table. This takes a lot of time too even I use the Journal mode = memory. I think the issue is I put the process code in 4 separated modules:

  1. Select the temp table -> output array
  2. Compress  the output array of step 1
  3. Insert the compress data from step 2 to actual table
  4. Drop the temp table

I am looking for an option to mix these steps together to speed up the speed for example select and delete the row in temp table at the same time then at the end I can drop the table faster. Currently, it takes significant time to drop the table. Our raw data can be up to 3GB. But I don't know how to combine the query. I also read that SQlite does not support this. So I also looking for an advice on how to make this process more efficient. I thought about using the queue too but I need input before I change it.

Thank you in advance.

Link to post
Share on other sites

Save the images as files and just index them in the database and any meta data you need.

As a rule of thumb, you only save data that is searchable in databases. Images are not searchable data, only their names are.

Doing it this way also enables you to process multiple images simultaneously, in parallel, and just update the meta data in the DB as you process them.

Link to post
Share on other sites

The reason I have these images save to a database is that at the end of the test, I need to collect this file as test data for post analyzing. I am thinking about your suggestion that temporary save the original images then compress them and save to database at the end but also the cost to save these images to file and read it back later. I hope to improve the speed from 100FPS to 150FPS or more. With your experience, do you think I can achieve the speed? The software is run on NI Compact Vision Controller (CVS).

Edited by Thang Nguyen
Link to post
Share on other sites

I note that you haven't given any performance numbers.  If I were looking at slow code with four steps, I would have learned how long each step roughly took within a few minutes**.  It is not uncommon in such cases for one step to be the bottleneck, taking significantly longer than all the other cases put together.  In that case, it does not help to try and do steps in parallel, and you should instead try and improve the performance of that one step.

I will withhold any other suggestions, as I think you are possibly just digging a hole for yourself by layering on "performance improvements" without measuring performance.

**Using the "Quick Timer" from "Cyclic Table Probes".

Link to post
Share on other sites
10 hours ago, Thang Nguyen said:

The reason I have these images save to a database is that at the end of the test, I need to collect this file as test data for post analyzing. I am thinking about your suggestion that temporary save the original images then compress them and save to database at the end but also the cost to save these images to file and read it back later. I hope to improve the speed from 100FPS to 150FPS or more. With your experience, do you think I can achieve the speed? The software is run on NI Compact Vision Controller (CVS).

No idea.

If you save files external to the DB then it mainly depends on the disk speed and the resolution/format you are saving it with. You've only given a rough resulting file size and a framerate. No indication of how many seconds worth that is in order to calculate the MB/frame/sec. Streaming to disk is orders of magnitude faster than to a DB though and the faster the disk, the faster you can stream.

If you just want the convenience of a single file for offline post processing then you save them to a directory and zip it all up at the end along with the DB metadata. If you want to be fancy, then you can add them individually as they are created in the background while the next recording is acquired (assuming you have enough oomph in the processor)

Link to post
Share on other sites

I would like to provide more information regarding my data:

The image array size is 300,000 pixel * 8

One test has about 5528 images + particle measurement array in each them.

Read Temp Table Time: 12.810000 sec

Compress Image Time: 54.924000 sec 

Write Actual Table Time: 1.619000 sec (not sure why it is very fast here)

Drop Temp Table Time: 265.688000 sec

Originally, I have another index table which is foreign key reference to the image table. I also have to create temp table to reference to temp image table. The time above is for image table and index table with it.

 

 

Link to post
Share on other sites

Best to state performance numbers in per operation.  I assume these are for 5528 images, so the "Drop Table" is 50ms each time?   Try eliminating the Drop Table and instead just delete the row.  If that works then your remaining dominant step is the 10ms/image for Compression.

I think your initial mistake was to go, "Since we want to speed up the process, <we do extra steps and make things extra complicated and in parallel modules in hopes that will be faster>."  Better to have said, "Since we want to speed up the process, we will make a simple-as-possible prototype VI that we can benchmark."  That prototype would be a simple loop that gets data, compresses, and saves to db.

Link to post
Share on other sites

David,

Currently, we already have that simple loop which can run with 10msec per image or 100 FPS. We are looking for option to upgrade to 150 FPS. The camera can up to this speed but the bottle neck is the compression step. That why I would like to do the compress at the end of each step. With your experience, do you think if I save each images separately into files will be faster than write into database? I can read, compress and write to SQLite database at the end of the test.

Thank you very much!

 

Link to post
Share on other sites

Saving your images directly in a binary file would probably be the fastest way to save.  Not the best format for long-term storage, but if you only keeping them temporarily so they can be read and compressed later then that doesn't matter.

I would first consider your Compression step; can you make that any faster?  You only need it 33% faster to get 150 FPS.  Is this your own developed compression algorithm?  How much CPU does it use when it is running?  If only one CPU then there are parallelization options (such as compressing multiple images in parallel).

BTW: do you really have 8-byte pixels?

Edited by drjdpowell
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 kosist90
      Dear Community,
      let me present our new ANV Database Toolkit, which has been recently released at vipm.io.
      Short introduction to the toolkit is posted by this link, and it also describes steps which should be done in order to use this toolkit.
      ANV Database Toolkit helps developers design LabVIEW API for querying various databases (MS SQL, MySQL, SQLite, Access). It allows to create VIs which can be used as API with the help of graphical user interface. When using these VIs, toolkit handles connection with the database, thus relieving developers of this burden in their applications.
      It has the following features:
      Simplifies handling of databases in LabVIEW projects Allows to graphically create API VIs for Databases Supports Read, Write, Update and Delete queries Supports various database types (MS SQL, MySQL, SQLite, Access) Overall idea is that developer could create set of high-level API VIs for queries using graphical user interface, without actual writing of SQL queries. Those API VIs are used in the application, and handle database communication in the background. Moreover, SQL query could be applied to any of the supported database types, it is a matter of database type selection. Change of target database does not require changes in API VI which executes the query.
      After installation of the toolkit, sample project is available, which shows possibilities of the toolkit in terms of execution different types of queries.
      Note, that in order to install the toolkit, VI Package Manager must be launched with Administrator privileges.
      This toolkit is paid, and price is disclosed based on price quotation. But anyway, there are 30 days of trial period during which you could tryout the toolkit, and decide whether it is helpful (and hope that it will be) for your needs.
      In case of any feedback, ideas or issues please do not hesitate to contact me directly here, or at vipm.io, or at e-mail info@anv-tech.com.
       
        
    • By drjdpowell
      View File SQLite Library
      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.
      Hosted on the NI LabVIEW Tools Network.
      JDP Science Tools group on NI.com.
      ***Requires VIPM 2017 or later for install.***
      Submitter drjdpowell Submitted 06/19/2012 Category Database & File IO LabVIEW Version 2013 License Type BSD (Most common)  
    • 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.
      Hosted on the NI LabVIEW Tools Network.
      JDP Science Tools group on NI.com.
      ***Requires VIPM 2017 or later for install.***
    • 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)

×
×
  • Create New...

Important Information

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