Jump to content
News about the LabVIEW Wiki! Read more... ×
drjdpowell

SQLite for OpenG

Recommended Posts

I tested the difference between the former insert template and the current one with BEGIN and COMMIT. It is quite obvious and about factor 300 to 400 faster. Thank you for the fantastic job implementing this!

BTW: saw that the project is hosted under bitbucket - excellent! in the readme there are two small faults: LabVIEW Versions: -> 2013 or later and Use VI Package Manager (2017 or later) according to the support page on lava

Share this post


Link to post
Share on other sites

Hi,

Thanks for a wonderfull sqlite api drjdpowell.

Is there away to read out how much memory is used by sqlite with this library or a general status of sqlite?

 

Thanks

Share this post


Link to post
Share on other sites

I want to optimize the upper part on the screenshot with the lower (to avoid too many transactions).

It seems that I am not aware of the correct handling at the moment. Thank you in advance for some instructions:

1. place the BEGIN and COMMIT outside. ok

2. the command in 2 and 3 is something like SELECT * FROM stueli WHERE d = 'xy'. Do I have to do that by bind or just as prepare statement on the picture?

3. is the usage of the 'SQL Connection in' and therefore no 'SQL statement in' correct? Or how to use them?

4. here the 'insert many row template' is used without the BEGIN and COMMIT. So it is the same question as in 3: how to handle the connection.

there is also a vi snippet attachet if this would be easier to explain something as one can draw in it directly with LV

thanks a lot

 

 

2019-01-05 08_01_41-expand2.vi Block Diagram on MultiTool.lvproj_My Computer.png

draftvi.png

Share this post


Link to post
Share on other sites
3 hours ago, mischl said:

I want to optimize the upper part on the screenshot with the lower (to avoid too many transactions).

My general advice for refactoring code is to do it step-by-step. Don't try to fix everything at once, as it will likely break your code (as it did in your case).

Note:

  • I wasn't able to load your VI snippet. I'm not sure why though (either I'm to stupid to do it or Lava sanitizes images)
  • My suggestions below are based on your original VI

Step 1 - Improve readability

Arrays and for loops

The outer for loop iterates over multiple arrays at the same time. This means that the loop will end after processing each element of the smallest array. I suggest you use one array for iteration and use the Array Index function to access elements from the other arrays. Here is an example:

657223384_AccessingElementsinaFor-Loop_vi.thumb.png.55fe24cd86c35bfcc367a2673c1de36c.png

You should play around with it to see the different outcomes. Note that indexing an element from an array of smaller size returns a default value (i.e. zero for numbers, an empty string for Strings and false for Boolean).

Shift registers

The outer for loop uses a shift register for the SQLite instance as well as the error cluster. The inner for loop, however, doesn't. This can be problematic for two reasons:

1) The SQLite instance in the inner for loop gets reset after each iteration.

2) An error in the inner for loop is reset after each iteration and only the last error is returned.

The solution depends on what you want to do. If you want to abort as soon as possible on any error, I suggest not using a shift register but making the for loop conditional instead (exit on error). Otherwise use shift registers on both loops.

Step 2 - Optimize your code

Use transaction outside of for loops

Transactions are very useful for improving performance if used correctly. In your second example, you already added the BEGIN and COMMIT Transaction using "MySavepoint". This is generally a good idea.

3 hours ago, mischl said:

1. place the BEGIN and COMMIT outside. ok 

Disable transactions on Execute SQL

The Execute SQL function has an optional input on the top which you can set to false in order to disable transactions. This will already improve performance considerably.

Answering questions

3 hours ago, mischl said:

2. the command in 2 and 3 is something like SELECT * FROM stueli WHERE d = 'xy'. Do I have to do that by bind or just as prepare statement on the picture?

SQL Prepare and SQL Finalize should be placed outside the for loop and you need to inject values for your parameters at some point (unless your SQL Statement is static, which it isn't according to your info above).

The "INSERT many rows Template code.vi" provided by the library (see Code Templates palette) visualizes that very well:

2002117129_INSERTManyRowsTemplate_vi.thumb.png.d2564885d0cde9253d04192b44088526.png

I highly suggest not using "SELECT *" but explicitly selecting particular columns instead. That way your SQL Statement becomes predictable.

3 hours ago, mischl said:

4. here the 'insert many row template' is used without the BEGIN and COMMIT. So it is the same question as in 3: how to handle the connection.

Here is an example that may or may not work (I can't test it for obvious reasons):

353253096_SELECTusingbindingparameters.thumb.png.1e4dea5001dc6f6bd687a2db46dc4556.png

I suggest making a VI for each statement (without transactions of course). That way you can test things in isolation and be certain that it works.

3 hours ago, mischl said:

3. is the usage of the 'SQL Connection in' and therefore no 'SQL statement in' correct? Or how to use them?

I'm not sure what you mean by that. Can you explain what you mean in more detail?

 

  • Like 1

Share this post


Link to post
Share on other sites

thank you for the detailed feedback!

Quote

The outer for loop iterates over multiple arrays at the same time. This means that the loop will end after processing each element of the smallest array.

all the arrays has exactly the same size

Quote

Shift registers

thanks for that!

Quote

The Execute SQL function has an optional input on the top which you can set to false in order to disable transactions.

yes, but that is set already to false by default. isn't it?

Quote

SQL Prepare and SQL Finalize should be placed outside the for loop and you need to inject values for your parameters at some point (unless your SQL Statement is static, which it isn't according to your info above).

correct, it isn't static and therefore I will bind them according your hint.

 

3. is the usage of the 'SQL Connection in' and therefore no 'SQL statement in' correct? Or how to use them?

in the example, the 'SQL statement' ends after the reset at 2. at 3 and 4 further ''SQL statement' are in used and only one can be finalized. but there are three now. how to handle that?

the 'Prepare' at 3 needs a 'SQL Connection', it is the one chained from 2, or?

 

sorry, it seems that I forgot to attach the snippet. here is it. no problem if there are broken wires, but it might be easier to wire an example with the correct usage of 'SQL Connection in' and 'SQL statement in' than to explain them by text.

 

draftvi.png

Share this post


Link to post
Share on other sites
18 hours ago, mischl said:

yes, but that is set already to false by default. isn't it?

You are right, my bad.

18 hours ago, mischl said:

is the usage of the 'SQL Connection in' and therefore no 'SQL statement in' correct? Or how to use them?

Yes and no.

The SQL Prepare function creates the SQL Statement. It allows you to run the same SQL query multiple times, but more efficient. I've attached an example in my last post, using your SQL query:

SELECT using binding parameters.png

SQL Prepare (second VI from left) receives an SQL Connection and the SQL query ("SELECT * FROM sueli WHERE d = ?"). Note, that it says "d = ?". The "?" will later be replaced in the loop by the actual value.

In the for loop, the first node does exactly that. It replaces "?" by an element from the list. Note that you can have multiple values, using multiple "?" in your query.

SQLite First Step (the next VI) executes the query once and you can read the results using the node that follows (Get Column String).

Before running the same query again, you must reset the SQL Statement. Note, that everything takes place within the loop.

When the loop ends, you need to finalize the SQL Statement or it stays in memory.

18 hours ago, mischl said:

in the example, the 'SQL statement' ends after the reset at 2. at 3 and 4 further ''SQL statement' are in used and only one can be finalized. but there are three now. how to handle that?

You must finalize each SQL Statement separately. In your example, everything is inside the loop, so you gain nothing by using prepared statements.

SQLite Prepare and SQLite Finalize must be placed outside the loop. I suggest you split those SQL Statements into separate VIs or it will be very confusing.

18 hours ago, mischl said:

the 'Prepare' at 3 needs a 'SQL Connection', it is the one chained from 2, or?

Yes, they all share the same connection.

18 hours ago, mischl said:

sorry, it seems that I forgot to attach the snippet. here is it. no problem if there are broken wires, but it might be easier to wire an example with the correct usage of 'SQL Connection in' and 'SQL statement in' than to explain them by text.

Actually, the snippet was attached, but Lava removes the source code of snippets right now (download the image and drag it into LabVIEW).

Can you copy the code into a new VI and post it here?

Share this post


Link to post
Share on other sites

If you are set on doing multiple queries in the same loop, here is another example that might help understand how it should work. The VI is executable, using dummy data.

2118115753_UsingMultiplePreparedStatementsInOneLoop_vi.thumb.png.d6253581d8650836a2227ad28a642bda.png

I've also attached the VI for LV2014, so you can play around with it.

 

Using Multiple Prepared Statements In One Loop.vi

Share this post


Link to post
Share on other sites

Thank you @LogMAN for all your explanations and your efforts to explain it in depth - very appreciated!

I am going to edit it according the description and I am very confident that this optimizes the code by factors.

Now as the new work year has started it will last some days until there is enough time to do it. I will keep you informed.

 

Vielen Dank nochmals und Grüsse aus der Schweiz

Share this post


Link to post
Share on other sites

thanks to the help of @LogMAN I was able to update the code into a transaction and it works like a charm and much faster, thank you again!

I was facing only one problem: '?-x' in a statement is processed as text and the question mark will not be exchanged with bind, use concat instead: ? || '-x'

 

Share this post


Link to post
Share on other sites
On 1/11/2019 at 8:30 AM, mischl said:

I was facing only one problem: '?-x' in a statement is processed as text and the question mark will not be exchanged with bind, use concat instead: ? || '-x'

Parameter bindings require you to follow specific syntax. It is explained here: https://www.sqlite.org/c3ref/bind_blob.html

Quote

In the SQL statement text input to sqlite3_prepare_v2() and its variants, literals may be replaced by a parameter that matches one of following templates:

  • ?
  • ?NNN
  • :VVV
  • @VVV
  • $VVV

In the templates above, NNN represents an integer literal, and VVV represents an alphanumeric identifier. The values of these parameters (also called "host parameter names" or "SQL parameters") can be set using the sqlite3_bind_*() routines defined here.

'?-x' gets interpreted as parameter, which doesn't work. Two solutions come to mind:

1) Use the concatenation function like you suggested. This makes sense if the suffix is static for all parameters.

2) Do the concatenation before binding the parameter (i.e. in LV). If your suffix can change, make it part of the parameter.

Edited by LogMAN

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

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