Jump to content
ShaunR

[CR] SQLite API

Recommended Posts

This is wonderful. I've begun implementing it in a current project and need to ask about the Query Transaction function.

post-7534-090777600 1286359910_thumb.png

I was excited to read this comment because one of our current systems takes forever to write to our SQL Server DB. (~30 seconds. I imagine it's executing one SQL INSERT for each step it's reporting.) So, I thought I'd test this in SQLite:

post-7534-007625300 1286360667_thumb.png

As I played with it I came up with this to see the effect of larger SQL strings:

post-7534-066496400 1286360759_thumb.png

I've tried each of the locking modes (immediate, deferred and exclusive) but it's taking almost three seconds to complete only 5000 SQL INSERT statements. This is not as fast as the documentation suggests.

What am I doing wrong?

Share this post


Link to post
Share on other sites

This is wonderful. I've begun implementing it in a current project and need to ask about the Query Transaction function.

I was excited to read this comment because one of our current systems takes forever to write to our SQL Server DB. (~30 seconds. I imagine it's executing one SQL INSERT for each step it's reporting.) So, I thought I'd test this in SQLite:

As I played with it I came up with this to see the effect of larger SQL strings:

I've tried each of the locking modes (immediate, deferred and exclusive) but it's taking almost three seconds to complete only 5000 SQL INSERT statements. This is not as fast as the documentation suggests.

What am I doing wrong?

Its not the number of statements. Its the number of "TRANSACTIONS".

When you use the "INSERT" VI in a for loop, each iteration is 1 transaction. Additionally, the insert vi opens the file before the transaction and closes it afterwards for a slightly safer implementation. The insert is for convenience and a bit more safety.

If you want blistering speed. You need to use the "transaction query.vi". this will wrap your Insert statements in the "BEGIN...END" and execute all the statements as 1 transaction.

With the test below. I achieved 10,000 inserts in 609 ms.

  • Like 2

Share this post


Link to post
Share on other sites

Its not the number of statements. Its the number of "TRANSACTIONS".

I understood that part (my snippet uses the transaction query VI), it turns out that my problem was that I was querying the database for all records at the end of all the INSERTs. Is there any way to speed up a SELECT?

Share this post


Link to post
Share on other sites

I understood that part (my snippet uses the transaction query VI), it turns out that my problem was that I was querying the database for all records at the end of all the INSERTs. Is there any way to speed up a SELECT?

Possibly. This release basically does a "GetRow" and has to iterate for each row. There is another method, but it isn't implemented yet. (Still trying to figure out how to do it....if it is possible in LV).

Do you have a real-time target that uses ETS?

Edited by ShaunR

Share this post


Link to post
Share on other sites

Do you have a real-time target that uses ETS?

No.

Share this post


Link to post
Share on other sites

I wish I had an excuse to play around with this, it looks sweet. :thumbup1:

Share this post


Link to post
Share on other sites

I wish I had an excuse to play around with this, it looks sweet. :thumbup1:

I concur. Next time I ask myself whether or not to choose a database implementation or simple binary files to store data, I'll try to find some time to try these first...

Share this post


Link to post
Share on other sites

I wish I had an excuse to play around with this, it looks sweet. :thumbup1:

You need an excuse? I thought it was our nature to play with this sort of stuff biggrin.gif

I concur. Next time I ask myself whether or not to choose a database implementation or simple binary files to store data, I'll try to find some time to try these first...

Keep it in your palette and I'm sure you'll think of something. wink.gif

Share this post


Link to post
Share on other sites

You need an excuse? I thought it was our nature to play with this sort of stuff biggrin.gif

Haha, I've resisted the urge to install LV at home so far. Some might say that's a bad thing, but I still need that mental division :P

Share this post


Link to post
Share on other sites

Some might say that's a bad thing,

Others might say you have a life wink.gif

  • Like 1

Share this post


Link to post
Share on other sites

Hi!

i'm porting this toolkit in Ubuntu Linux with version 8.5 of Labview.

Some polimorphic vi needs to be modifyied to work correctly such as SQLLite_Delete column.vi and SQLLite_Rename column.vi it said that "you cannot use "SQLLite_Rename column.vi" recursively, so i deleted them :)

After that you place a conditional disabled structure for Unix but to make it work i had to change that condition from OS==Unix to TARGET_Type==Unix.

I try changing it in OS==Linux as i see in the conditional Disable Symbols, but it didn't work the first time it said that OS was undefined. now i start labview again and it seems to work...what is better? OS or Target_Type?

Instead, TARGET_BITNESS is an unexisting variable.

  • Like 2

Share this post


Link to post
Share on other sites

Hi!

i'm porting this toolkit in Ubuntu Linux with version 8.5 of Labview.

Swwet.

I did include aLinux x32 library but didn't advertise the fact because it was untested.

Some polimorphic vi needs to be modifyied to work correctly such as SQLLite_Delete column.vi and SQLLite_Rename column.vi it said that "you cannot use "SQLLite_Rename column.vi" recursively, so i deleted them :)

I'm surprised by this. What said it? Those two function are actually a trick. They just use SQL to copy the data to another table, delete then recreate the original and copy all the data back. SQLite doesn't have delete or rename columns function, but it is needed. To get this error, one of the other VIs would have to include it. Do you have an example use that shows this error?

After that you place a conditional disabled structure for Unix but to make it work i had to change that condition from OS==Unix to TARGET_Type==Unix.

I try changing it in OS==Linux as i see in the conditional Disable Symbols, but it didn't work the first time it said that OS was undefined. now i start labview again and it seems to work...what is better? OS or Target_Type?

Instead, TARGET_BITNESS is an unexisting variable.

OS and bitness are only available from within a Project. But Target Type is available all the time. Did you open them from within a project? Or just stand-alone VIs? Try adding them to a project.

The problem is that linux, like windows, also has x32 and x64. We need the bitness to choose the appropriately compiled DLL.

The choice of whether to use OS or Target Type was arbitrary. They both support the same things (more or less). I leaned towards OS because that is the one that NI use in their example.

Share this post


Link to post
Share on other sites

I'm surprised by this. What said it? Those two function are actually a trick. They just use SQL to copy the data to another table, delete then recreate the original and copy all the data back. SQLite doesn't have delete or rename columns function, but it is needed. To get this error, one of the other VIs would have to include it. Do you have an example use that shows this error?

I Didn't make an example i only created a new blank vi and place a Table.vi inside.

Could it be a converting from LV9 to LV 8.5 problem? To make this i had to convert to 8.6 first and then with LV 8.6 to 8.5...maybe something get lost inthis step.

OS and bitness are only available from within a Project. But Target Type is available all the time. Did you open them from within a project? Or just stand-alone VIs? Try adding them to a project.

The problem is that linux, like windows, also has x32 and x64. We need the bitness to choose the appropriately compiled DLL.

The choice of whether to use OS or Target Type was arbitrary. They both support the same things (more or less). I leaned towards OS because that is the one that NI use in their example.

I created a New project but the OS variable was not "Unix" but "Linux" and the bitness variable did not exists.

Now i'm trying reconverting Vis from LV9 to LV8.5 and using a project, i'll let you know if i find something new

Share this post


Link to post
Share on other sites

Ok, i find that After converting Vi's to 8.6, Labview autodisconnect rename column and Delete column to prevent recursion.

There is a SQLite_Query.VI that is recursive and cause problem with versions 8.6. SQLlite_query3.vi cause the problem.

Share this post


Link to post
Share on other sites

I Didn't make an example i only created a new blank vi and place a Table.vi inside.

Could it be a converting from LV9 to LV 8.5 problem? To make this i had to convert to 8.6 first and then with LV 8.6 to 8.5...maybe something get lost inthis step.

I created a New project but the OS variable was not "Unix" but "Linux" and the bitness variable did not exists.

Now i'm trying reconverting Vis from LV9 to LV8.5 and using a project, i'll let you know if i find something new

I believe TARGET_BITNESS was first introduced in LV 8.6. Do you have any difficulties in LV 9?

Share this post


Link to post
Share on other sites

I believe TARGET_BITNESS was first introduced in LV 8.6. Do you have any difficulties in LV 9?

No I don't

...even if i cannot find any TARGET_BITNESS variable in the project...where is it placed?

Oh, QUery3.vi, rename column and dlete column report the same recursion problem.

Edited by Gepponline

Share this post


Link to post
Share on other sites

No I don't

...even if i cannot find any TARGET_BITNESS variable in the project...where is it placed?

Oh, QUery3.vi, rename column and dlete column report the same recursion problem.

It's an in-built definition the same as "TARGET_TYPE".

I'm a bit stumped as to why LV9.0 on Linux cannot support recursive VI's when Windows x64 and x32 don't seem to have an issue.blink.gif You could remove them from the polymorphic table VI and add them separately to your palette since it seems it's the polymorphic instance that is causing the problem.

Unfortunately, Linux documentation a is bit lacking. I have several VMWare Linux images, but was unable to find a downloadable trial version of LV9.0 for Linux, otherwise I would have tried it. The NI website only has windows and Mac. If you go to their Linux download section you only get a windows EXE installer although it says its for all OSs ph34r.gif.

Share this post


Link to post
Share on other sites

maybe there is a a little misunderstanding.

i don't have LV 9.0 for linux, i have LV 8.5 for linux. That's why i have to convert VI's forst to LV 8.6 in windows and then to 8.5 for linux.

Share this post


Link to post
Share on other sites

maybe there is a a little misunderstanding.

i don't have LV 9.0 for linux, i have LV 8.5 for linux. That's why i have to convert VI's forst to LV 8.6 in windows and then to 8.5 for linux.

IC.

Well that explains the recursion issue you are seeing. Prior to LV9.0 recursion was only supported by opening a ref and using a call vi node. But its nice to know with a bit of hacking it can work on older LV versions......even Linux biggrin.gif. I presume (since you haven't mentioned any so far) that there are no issues in your windows LV 9?

Share this post


Link to post
Share on other sites

IC.

Well that explains the recursion issue you are seeing. Prior to LV9.0 recursion was only supported by opening a ref and using a call vi node. But its nice to know with a bit of hacking it can work on older LV versions......even Linux biggrin.gif. I presume (since you haven't mentioned any so far) that there are no issues in your windows LV 9?

I confirm, no issues with LV9. And now that i have deleted recursive VI, no issues even with LV 8.5. Maybe i'll convert the recursive VIs as you said with call vi nodes to have all the tool working, but for the moment i only need to make some simple query and i don't need nothing more ;)

Share this post


Link to post
Share on other sites

Fun stuff, thanks Shaun!

It's worth noting that attempting to write a raw "\00" causes a glitch in the low level prepare (that calls sqlite3_prepare_v2) vi. I'm guessing it's due to:

If the nByte argument is less than zero, then zSql is read up to the first zero terminator. If nByte is non-negative, then it is the maximum number of bytes read from zSql. When nByte is non-negative, the zSql string ends at either the first '\000' or '\u0000' character or the nByte-th byte, whichever comes first. If the caller knows that the supplied string is nul-terminated, then there is a small performance advantage to be gained by passing an nByte parameter that is equal to the number of bytes in the input string including the nul-terminator bytes.

The statement size is wired as a -1 by default. I'm tinkering with different sizes now, but would take any ideas out there.

Thanks,

Joe Z.

Edited by jzoller

Share this post


Link to post
Share on other sites

I confirm, no issues with LV9. And now that i have deleted recursive VI, no issues even with LV 8.5. Maybe i'll convert the recursive VIs as you said with call vi nodes to have all the tool working, but for the moment i only need to make some simple query and i don't need nothing more ;)

You probably only need to take them out of the polymorphic VI. Rename column calls delete column and they are both in the table polymorphic vi.

Fun stuff, thanks Shaun!

It's worth noting that attempting to write a raw "\00" causes a glitch in the low level prepare (sqlite3_prepare_v2) vi. I'm guessing it's due to:

The statement size is wired as a -1 by default. I'm tinkering with different sizes now, but would take any ideas out there.

Thanks,

Joe Z.

What sort of glitch?

-1 is much safer since if you supply a normal Labview string, you would have to add 1, however, if you supplied a null terminated string, you mustn't add the 1.The logic involved in detecting null chars outweighs any performance improvements you may get. If its better performance you are after, you'll have to wait for Version 1.1 wink.gif

However. If you are trying to insert a null char into the database. You will have problems since c strings are null terminated, So when you pass the string (even with the length) Labview needs to convert it from a Labview string (which has a length that may have leading nulls) to a C string and this conversion will invariably truncate your string.

Usually the way forward in the latter case is to use a "Blob" field but I haven't implemented that in this release.

Share this post


Link to post
Share on other sites
Guest
This topic is now closed to further replies.

×
×
  • Create New...

Important Information

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