[CR] SQLite API

[ShaunR]

I tried to replicate your result for x32 but couldn't..

Mine is still linear.

Its fairly consistent.

Here's up to 5 million.

[subhasisbehera]

Thanks Shaun and Matt. It is working perfectly now.

[Matt W]

Found a bug while optimizing mine that affects yours as well.

If you insert a blank string it'll probably be bound as a NULL.

It can be fixed if the empty string passed to sqlite3_bind_text is null terminated (I'm not sure if this is a bug with sqlite or a undocumented feature to allow writing nulls with string binding).

Inserting as a string instead of a byte array also fixes it since LabVIEW will null terminate all strings passed to the CLN.

Edited November 25, 2010 by Matt W

[ShaunR]

Found a bug will optimizing mine that affects yours as well.

If you insert a blank string it'll probably be bound as a NULL.

It can be fixed if the empty string passed to sqlite3_bind_text is null terminated (I'm not sure if this is a bug with sqlite or a undocumented feature to allow writing nulls with string binding).

Inserting as a string instead of a byte array also fixes it since LabVIEW will null terminate all strings passed to the CLN.

I get error code 19 with your 1st snippet

Abort due to contraint violation

SQLite_Error.vi:2>SQLite_Bind Execute.vi>SQLite_Insert Table.vi:2>Untitled 1

If the field is not declared with NOT NULL it succeeds.

[Matt W]

I get error code 19 with your 1st snippet

Abort due to contraint violation

SQLite_Error.vi:2>SQLite_Bind Execute.vi>SQLite_Insert Table.vi:2>Untitled 1

If the field is not declared with NOT NULL it succeeds.

Exactly, without the fix the empty string is bound as a NULL (unless the pointer passed to the dll happens to point to a \00), which fails the constraint. With the fix it get's bound as an empty string and passes the constraint.

I figured out the 2x size 32 bit example slowed down. I had left the speed test front panel open, without the front panel being open it doesn't slow down.

[ShaunR]

Exactly, without the fix the empty string is bound as a NULL (unless the pointer passed to the dll happens to point to a \00), which fails the constraint. With the fix it get's bound as an empty string and passes the constraint.

I figured out the 2x size 32 bit example slowed down. I had left the speed test front panel open, without the front panel being open it doesn't slow down.

I thought about this a while back.

Labview has no concept of "NULL" string. We can neither create it nor check for it. If we put a string control / constant down, we can only have an empty string (and any of our VIs that accept a string will have a string control).

So the choice becomes do we allow a write to a NOT NULL field to always succeed (which is what will happen with your suggestion) or do we define an empty string in LV as being the equivalent to a NULL string. I think the latter is more useful.

I figured out the 2x size 32 bit example slowed down. I had left the speed test front panel open, without the front panel being open it doesn't slow down.

Good job I cannot take back the rep point eh?

Edited November 25, 2010 by ShaunR

[Matt W]

I thought about this a while back.

Labview has no concept of "NULL" string. We can neither create it nor check for it. If we put a string control / constant down, we can only have an empty string (and any of our VIs that accept a string will have a string control).

So the choice becomes do we allow a write to a NOT NULL field to always succeed (which is what will happen with your suggestion) or do we define an empty string in LV as being the equivalent to a NULL string. I think the latter is more useful.

[/size][/color]

Good job I cannot take back the rep point eh?

I have a variant interface for differentiating nulls and empty strings when needed (which I need in my case). But with a pure string interface, making an empty string null is more useful.

[edit] this seems to be wrong

If you want an empty string to be written as NULL then you need to make sure the value passed doesn't point to null (just replace the 0 in my fix with any other number).

[/edit]

It seems an empty arrays are pointer to 0 which is why SQLite makes it null, not that it doesn't point to a /00 character.So you don't need to fix your current version to keep empty string as null.

Edited November 25, 2010 by Matt W

[ShaunR]

I have a variant interface for differentiating nulls and empty strings when needed (which I need in my case). But with a pure string interface, making an empty string null is more useful.

[edit] this seems to be wrong

If you want an empty string to be written as NULL then you need to make sure the value passed doesn't point to null (just replace the 0 in my fix with any other number).

[/edit]

It seems an empty arrays are pointer to 0 which is why SQLite makes it null, not that it doesn't point to a /00 character.So you don't need to fix your current version to keep empty string as null.

An empty array is slightly different.

Since I use self indexing for loops, the bind never gets executed. However. I've just noticed that the classic problem also occurs. The sql ref and the DB ref are passed as "0" to the Bind Clear meaning they never get freed. Whilst his is obviously undesirable, I would have expected SQLite to return a "Misuse" error. It doesn't Instead error 1097 occurs which isn't good (possible crash under the right conditions). I will put some defensive code around this

So inadvertently you have uncovered a bug, although not the original one

Edited November 25, 2010 by ShaunR

[Matt W]

An empty array is slightly different.

Since I use self indexing for loops, the bind never gets executed. However. I've just noticed that the classic problem also occurs. The sql ref and the DB ref are passed as "0" to the Bind Clear meaning they never get freed. Whilst his is obviously undesirable, I would have expected SQLite to return a "Misuse" error. It doesn't Instead error 1097 occurs which isn't good (possible crash under the right conditions). I will put some defensive code around this

So inadvertently you have uncovered a bug, although not the original one

Just replace the terminals with shift registers (generally a good rule with for loops anyway).

But I was talking about the byte array from the string that gets passed to the bind string (it seems that empty arrays are passed as pointers to 0 in CLNs).

Is there a reason you're clearing bindings before finalizing the statement. It's my understanding the clear just sets all parameters to null, and since you're closing it doesn't matter what the bindings are.

[ShaunR]

Is there a reason you're clearing bindings before finalizing the statement. It's my understanding the clear just sets all parameters to null, and since you're closing it doesn't matter what the bindings are.

Yes and no

I like the encapsulation so that If I decide to expose the "Bind Execute" into the Low Level palette, then the user would not have to worry about it. I'm not sure (now) if bindings are persistent or not across opening and closing the DB (I originally thought it was persistent) as it is not stated anywhere and when I checked I couldn't discern any difference in performance. So just erring on the side of caution really.

When I run out of things to do, I might look at it again (if I remember ).

Have you noticed any improvement in performance between this version (1.2.1) and version 1.1?

Edited November 26, 2010 by ShaunR

[Matt W]

Yes and no

I like the encapsulation so that If I decide to expose the "Bind Execute" into the Low Level palette, then the user would not have to worry about it. I'm not sure (now) if bindings are persistent or not across opening and closing the DB (I originally thought it was persistent) as it is not stated anywhere and when I checked I couldn't discern any difference in performance. So just erring on the side of caution really.

When I run out of things to do, I might look at it again (if I remember ).

Have you noticed any improvement in performance between this version (1.2.1) and version 1.1?

Finalizing should destroy all the bindings (as well as the statement). And since you have to finalize before closing they shouldn't be persistent.

Oh I think you missed an error wire in the win32 part of fetch record.

Speed wise using the current version of yours and the current version of mine (which I've made a a couple speed improvements to since the last time I compared) are very close. Your winning in inserts and If you modify fetch all so you only check the column count once per statement instead of per row you should win dumps as well. For me to to be equal speed wise, as far as I can tell, I'd need to break features that I use.

On XP LV2010 Both with sync off

10000 Records

Yours

Insert 57.36 Dump 59.39

Mine

Insert 59.21 Dump 57.1

100000 Records

Yours

Insert 587.89 Dump 583.53

Mine

Insert 609.81 Dump 582.05

[Matt W]

Seems that I do better on my Win7 machine at home.

On Win7 LV2010 32bit

10000 Records

Yours

Insert 40.95 Dump 37.14

Mine

Insert 35.57 Dump 36.87

100000 Records

Yours

Insert 405.19 Dump 371.07

Mine

Insert 351.46 Dump 376.54

On Win7 LV2010 64bit

10000 Records

Yours

Insert 39.11 Dump 41.59

Mine

Insert 29.88 Dump 33.94

100000 Records

Yours

Insert 377.77 Dump 471.24

Mine

Insert 289.93 Dump 397.06

[ShaunR]

Excellent. So my "improvements" are indeed improvements.

I think we are getting to the stage where implementation is becoming the major difference. Obviously my version uses a much deeper nesting since I prefer a modular decomposition (and have abstracted further) as opposed to (say) putting many DLL calls in one VI - which would save the sub-vi overheads. But for the sake of performance vs maintenance that is acceptable (to me at least). I would also expect you to be squeezing a little more by using the 2010 in-lining feature (if you are not, then you should be) which is unavailable in 2009. But I take note of your suggestion to promote the get_column_count which (in theory) should skim a ms or two of the time (does't seem to make any noticeable difference on my machine though).

[Matt W]

Excellent. So my "improvements" are indeed improvements.

I think we are getting to the stage where implementation is becoming the major difference. Obviously my version uses a much deeper nesting since I prefer a modular decomposition (and have abstracted further) as opposed to (say) putting many DLL calls in one VI - which would save the sub-vi overheads. But for the sake of performance vs maintenance that is acceptable (to me at least). I would also expect you to be squeezing a little more by using the 2010 in-lining feature (if you are not, then you should be) which is unavailable in 2009. But I take note of your suggestion to promote the get_column_count which (in theory) should skim a ms or two of the time (does't seem to make any noticeable difference on my machine though).

Mine is heavily dependent on inlining for speed, you're probably much faster in LV2009 due to that. If I needed LV2009 support I'd probably switch to subroutines. VI nesting wise we're pretty similar (if we ignore the higher level abstractions on yours), I have only three VIs where I have more than one DLL call (one to copy a query string to a pointer, the other is handle strings with /00, the last is to read a column based on it's type). But I avoid the speed lose due to inlining. I added a repeated query interface to mine (I use it to run stuff like mass inserts) and In the benchmark I gain about 10% on the insert due to inlining (even though it's only called once). LabVIEW cuts of all the dead call for storing the results of the repeated query (since I don't wiring the result data on the insert). If I wire the results to something I loose my 10% gain. The dead code elimination and inlining makes writing a performant interface much easier since I don't always to write multiple version of the code for different requirements (which also simplifies the interface).

If I modify your fetch all on LV2010 64 on win7 I gain about 13 ms on a select with 100000 records.

A few more suggestions on your version.

Drop the high priority execution setting, typically disk IO is considered low priority (starving data collection would be bad).

You should support for sqlite3_errmsg in your error handler, since that'll give a much explanation of the error (stuff like table names or which command is misspelled).

You'll probably want update to use binding, since currently you can't update to a blob value.

[ShaunR]

If I modify your fetch all on LV2010 64 on win7 I gain about 13 ms on a select with 100000 records.

Thats about 3% which is hardly worth the effort (although I'm not sure what you mean by modifying.....in-lining?) If I in-line the VIs I get exactly the same performance for insertions as you. But slightly slower on select (only gaining about 5ms in the 10,000 test)

A few more suggestions on your version.

Drop the high priority execution setting, typically disk IO is considered low priority (starving data collection would be bad).

That won't happen. I use a high priority but in a different execution system and therefore force LV to run the queries in a different thread from the users application (assuming the user isn't using the same execution system of course). It basically forces a high priority thread rather than VIs which should mean it gets a higher priority on the scheduler. On my machine I always run with the maximum number of threads (~200) since a lot of my systems use asynchronous tasks at various priorities. This is the way things like VISA work Although I did notice it is set to "Standard" and should be set to "Other 1" (not quite sure how that got changed).

You should support for sqlite3_errmsg in your error handler, since that'll give a much explanation of the error (stuff like table names or which command is misspelled).

I did look at it. But found that I needed to check the error every dll call and extract the string if need be. So I went for passing the error code up the chain and converting it at the end.

You'll probably want update to use binding, since currently you can't update to a blob value.

Indeed. Missed that one.

[Matt W]

Thats about 3% which is hardly worth the effort (although I'm not sure what you mean by modifying.....in-lining?) If I in-line the VIs I get exactly the same performance for insertions as you. But slightly slower on select (only gaining about 5ms in the 10,000 test)

I meant move the column count out of the while loop.

That won't happen. I use a high priority but in a different execution system and therefore force LV to run the queries in a different thread from the users application (assuming the user isn't using the same execution system of course). It basically forces a high priority thread rather than VIs which should mean it gets a higher priority on the scheduler. On my machine I always run with the maximum number of threads (~200) since a lot of my systems use asynchronous tasks at various priorities. This is the way things like VISA work Although I did notice it is set to "Standard" and should be set to "Other 1" (not quite sure how that got changed).

You might want to put a note in the documentation on which execution system you're using. I've just been using the same as callers in mine. I haven't really experimented with tuning priorities in LabVIEW.

I did look at it. But found that I needed to check the error every dll call and extract the string if need be. So I went for passing the error code up the chain and converting it at the end.

I didn't find it that to be much of a problem (you would just need to add it to your SQLite_Error VI, and maybe call it a bit more often). It helps a lot with debugging, of course I'm working with raw SQL strings far more often than you are.

[ShaunR]

I meant move the column count out of the while loop.

IC. Yes I tried that and didn't notice much of a change, but then again 3% on 40ms is only about 1ms which is in the noise level. I would have expected more, But it seems DLL call overhead is virtually non existent when set to a subroutine.

You might want to put a note in the documentation on which execution system you're using. I've just been using the same as callers in mine. I haven't really experimented with tuning priorities in LabVIEW.

IF I ever write a manual. I will

If you don't use execution systems and/or priorities, then you are limited to 4 threads (+1 for the UI). I don't think most people worry about it (LV is very good t making things appear to be very multi-threaded), but with very IO oriented asynchronous designs,it improves performance immensely (if used correctly). ThreadConfg.vi is my favourite VI

I didn't find it that to be much of a problem (you would just need to add it to your SQLite_Error VI, and maybe call it a bit more often). It helps a lot with debugging, of course I'm working with raw SQL strings far more often than you are.

I'll keep it in mind for now.

[Gepponline]

a little suggestion.

I think it could be usefull to add an Order By array input in the select Vi.

[ShaunR]

a little suggestion.

I think it could be usefull to add an Order By array input in the select Vi.

You can use the "Order BY" in the where clause.

e.g

Note that you have to have a where statement of some description so you can use RowID>0 to return all rows in a table if you have no "Where" constraint.

[subhasisbehera]

Hi,

I am trying to use the sqlite functions in an executable. DO I need to add something in the installer for this to work successfully?

Please guide me.

Thanks,

Subhasis

[subhasisbehera]

It is looking for labview.exe which it can not find, and so the SQLiteRread Blob could not load.

[ShaunR]

Hi,

I am trying to use the sqlite functions in an executable. DO I need to add something in the installer for this to work successfully?

Please guide me.

Thanks,

Subhasis

It is looking for labview.exe which it can not find, and so the SQLiteRread Blob could not load.

Hmmm. This could potentially be a nasty one

Open the SQLite project and Find "SQLite Read Blob.vi"

Open the "MoveBlock" call library node and change it from "Labview.exe" to "lvrt"

Click "OK"

If it starts to search for the dll, Help it by browsing to your "/program files / national instruments / shared / Labview Run-Time / [your version] directory and selecting "lvrt.dll".

Save the vi and the project.

You should now be able to open your project and build the app. It may ask you again to find the lvrt.dll. If it does, so the same "browse" again as the above.

I can put in a switch to detect the change between run-time and dev, but if its not automagically picking up the run-time dll, this will cause a huge headache in multiple version systems.

I'll look more closely into this, but the above should enable you to build your app until I find a better solution.

Edited November 30, 2010 by ShaunR

[ShaunR]

OK.

Definitive answer. Change the "SQLite Read Blob.vi" moveblock call to "LabVIEW" (note the case...it matters!) instead of "labview.exe" or "lvrt"

Let me know if it fixes your issue and I'll add it to the API library.

Edited November 30, 2010 by ShaunR

[Gepponline]

OK.

Definitive answer. Change the "SQLite Read Blob.vi" moveblock call to "LabVIEW" (note the case...it matters!) instead of "labview.exe" or "lvrt"

Let me know if it fixes your issue and I'll add it to the API library.

For me It's Ok

[subhasisbehera]

Hi ShaunR,

Yes that worked fine.

Thanks,

Subhasis