Re: [sqlite] Question about C# with SQLite

[dandl]

Did you mean: SQLite.Intero.dll or SQLite.Interop.dll?

Did you put it in the right place?

Also consider the question of 32 vs 64 bit.

Did you try Stack Overflow? There are lots of hits over there.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org



> -Original Message-
> From: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users-
> boun...@mailinglists.sqlite.org] On Behalf Of J Decker
> Sent: Tuesday, 5 July 2016 12:57 PM
> To: SQLite mailing list 
> Subject: Re: [sqlite] Question about C# with SQLite
> 
> if it was built with debug mode; probably the debug runtime doesn't exist
> there.  Otherwise it's because the visual studio runtime required isn't
> available.
> 
> On Mon, Jul 4, 2016 at 6:52 AM, Shouwei Li  wrote:
> 
> > Hi there,
> >
> > I have a project developed with .net 2015 and C#. I use SQLite as the
> > server-less database. It works very well in my workstation. But it can
> > not run on other PC. The error indicates:
> >
> > Unable to load DLL "SQLite.Intero.dll": The specified module could not
> > be found.
> >
> > I already attach this dll with my program.
> >
> > I want to ask is there a manual talk about how to deploy a program to
> > customer when we use the SQLite database.
> >
> > Thanks for your reading.
> >
> > --
> > Best Regards!
> > Shouwei Li
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about C# with SQLite

[J Decker]

if it was built with debug mode; probably the debug runtime doesn't exist
there.  Otherwise it's because the visual studio runtime required isn't
available.

On Mon, Jul 4, 2016 at 6:52 AM, Shouwei Li  wrote:

> Hi there,
>
> I have a project developed with .net 2015 and C#. I use SQLite as the
> server-less database. It works very well in my workstation. But it can not
> run on other PC. The error indicates:
>
> Unable to load DLL "SQLite.Intero.dll": The specified module could not be
> found.
>
> I already attach this dll with my program.
>
> I want to ask is there a manual talk about how to deploy a program to
> customer when we use the SQLite database.
>
> Thanks for your reading.
>
> --
> Best Regards!
> Shouwei Li
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Question about C# with SQLite

[Shouwei Li]

Hi there,

I have a project developed with .net 2015 and C#. I use SQLite as the
server-less database. It works very well in my workstation. But it can not
run on other PC. The error indicates:

Unable to load DLL "SQLite.Intero.dll": The specified module could not be
found.

I already attach this dll with my program.

I want to ask is there a manual talk about how to deploy a program to
customer when we use the SQLite database.

Thanks for your reading.

-- 
Best Regards!
Shouwei Li
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Different error messages for the same syntax error

[R Smith]

Please ignore the previous reply,

I see your concern is not the error message but indeed the difference in 
messages for what seems to be a similar error (knowingly so).



On 2016/07/04 8:21 PM, gwenn wrote:

Hello,

SQLite version 3.13.0 2016-05-18 10:57:30
sqlite> create table test (name text default '');
sqlite> insert into test values ();
Error: near ")": syntax error
sqlite> insert into test values (''), ();
Error: no tables specified

Maybe the parser rule is too permissive:
values(A) ::= values(X) COMMA LP exprlist(Y) RP.
versus
values(A) ::= values(X) COMMA LP nexprlist(Y) RP.

Thanks and regards.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Different error messages for the same syntax error

[R Smith]

On 2016/07/04 8:21 PM, gwenn wrote:

Hello,

SQLite version 3.13.0 2016-05-18 10:57:30
sqlite> create table test (name text default '');
sqlite> insert into test values ();
Error: near ")": syntax error
sqlite> insert into test values (''), ();
Error: no tables specified


The documentation is clear on this matter I think - It says that if a 
field-list is not specified, the inserted VALUES must be exactly the 
number of fields in the table. If the prototype field-list is specified, 
you may omit one or more of the fields (but not all of them) and the 
omitted fields will gain their specified default values or NULL where no 
default is specified. Inserting such a null into a NOT NULL column will 
be a constraint violation.


The above Insert statements do not conform to this documented 
requirement. To comply, you /must/ specify one of:

INSERT INTO test (name) VALUES ('')
or
INSERT INTO test VALUES ('')
or
INSERT INTO test DEFAULTS

- All of which should work.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Different error messages for the same syntax error

[Stephen Chrzanowski]

insert into table test defaults;


On Mon, Jul 4, 2016 at 2:21 PM, gwenn  wrote:

> Hello,
>
> SQLite version 3.13.0 2016-05-18 10:57:30
> sqlite> create table test (name text default '');
> sqlite> insert into test values ();
> Error: near ")": syntax error
> sqlite> insert into test values (''), ();
> Error: no tables specified
>
> Maybe the parser rule is too permissive:
> values(A) ::= values(X) COMMA LP exprlist(Y) RP.
> versus
> values(A) ::= values(X) COMMA LP nexprlist(Y) RP.
>
> Thanks and regards.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Different error messages for the same syntax error

[gwenn]

Hello,

SQLite version 3.13.0 2016-05-18 10:57:30
sqlite> create table test (name text default '');
sqlite> insert into test values ();
Error: near ")": syntax error
sqlite> insert into test values (''), ();
Error: no tables specified

Maybe the parser rule is too permissive:
values(A) ::= values(X) COMMA LP exprlist(Y) RP.
versus
values(A) ::= values(X) COMMA LP nexprlist(Y) RP.

Thanks and regards.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trigger slowness even when it's not fired

[Dominique Devienne]

On Mon, Jul 4, 2016 at 1:18 PM, R Smith  wrote:

> On 2016/07/04 1:13 PM, Dominique Devienne wrote:
>
>> On Mon, Jul 4, 2016 at 12:08 PM, Hick Gunter  wrote:
>>
>>> A simple UPDATE  set = where  will
>>> translate into about 40 instructions; adding your trigger adds an
>>> additional estimated 400 instructions of trigger program.
>>>
>> But the question is whether these extra VDBE instructions can cause
>> slowdowns, even when not executed at runtime? (i.e. branch using them is
>> not taken).
>
> The slowness is not due to the Trigger code being skipped or executed...
> The slowness is resulting from the trigger code having to be compiled into
> the statement. Whether or not it gets executed might affect another layer
> of slowdown, but not in the OP's case.


Ah ah, I see it now, thanks! --DD

PS: I never worry about SQLite's parser performance (because I'm binding in
part),
but in this case it's akin to compiling a dozen different statements,
with the associated name lookups and execution plan building,
so it could add up if done in a tight loop indeed. Great insights. Thanks
again.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] builtin functions and strings with embedded nul characters

[Simon Slavin]

On 4 Jul 2016, at 12:07pm, R Smith  wrote:

> I prefer how Lazarus/Delphi does it (wrt the Pascal variant options as 
> opposed to C++) where a string is a record with first the encoding, the 
> length and then the actual bytes given.

What Ryan said.

Note that sqlite3_bind_text() has a parameter for the length of the text field. 
 And that calls to sqlite3_column_text() may be accompanied by 
sqlite3_column_bytes() to find the number of bytes returned.  So in both cases 
you can ignore the length of the string as calculated by C and use your own 
number.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] retrieve by name or index

[R Smith]

On 2016/07/04 12:22 PM, Jim Wang wrote:

hi,all
 There is a table include id,string1,string2 and so on.
 So I want to get the value of string1 from a record, there are two methods:
 1.  get by the name of the string2
 2.  get by the index of the string2, the index is 2
which one is faster?


This is not making sense to me, but I realise there might be a bit of a 
language barrier, so I will try to guess...


Are you asking whether using the ID (which I assume to be Integer) as a 
look-up target to finding one of the strings is faster than using an 
Index on one of the strings?
If this is the question, then yes, but only slightly faster. More fast 
even if the ID is also the INTEGER PRIMARY KEY.


Using the Text in String2 in an Index and then looking up that Index 
will be a little slower, and more and more slower the more bigger those 
strings become.


Looking up an Index will be really fast (almost comparable with INT ID 
look-ups) on strings that look like this:

'ABC'
'JJ1'
'ASX'
'GN001'
etc. and other type short codes.

Looking up long strings that contain sentences or phrases or perhaps 
whole paragraphs will be much slower.


I hope that answers the question - good luck!
Ryan


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trigger slowness even when it's not fired

[R Smith]

On 2016/07/04 1:13 PM, Dominique Devienne wrote:

On Mon, Jul 4, 2016 at 12:08 PM, Hick Gunter  wrote:



A simple UPDATE  set = where  will
translate into about 40 instructions; adding your trigger adds an
additional estimated 400 instructions of trigger program.


But the question is whether these extra VDBE instructions can cause
slowdowns, even when not executed at runtime? (i.e. branch using them is
not taken).



The slowness is not due to the Trigger code being skipped or executed... 
The slowness is resulting from the trigger code having to be compiled 
into the statement. Whether or not it gets executed might affect another 
layer of slowdown, but not in the OP's case.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] retrieve by name or index

[Simon Slavin]

On 4 Jul 2016, at 11:22am, Jim Wang <2004wqg2...@163.com> wrote:

>There is a table include id,string1,string2 and so on.
>So I want to get the value of string1 from a record, there are two methods:
>1.  get by the name of the string2
>2.  get by the index of the string2, the index is 2
>   which one is faster?

My guess is method 2, but not by much.  Which one is actually faster depends on 
your exact setup so your only way to find out is to try it.  But you won't see 
much difference so it may be that your time can be better spent doing something 
else.

Don't forget that your main guide to writing software is to make it easy to 
read.  Only if it's too slow to use should you start worrying about the fastest 
methods.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trigger slowness even when it's not fired

[Dominique Devienne]

On Mon, Jul 4, 2016 at 12:08 PM, Hick Gunter  wrote:

> Creating a trigger causes SQLite to insert a row into the sqlite_master
> table which contains the text you supply for the trigger. No code is
> generated at this time. You can verify this by creating a trigger that
> references undefined fields. It willl succeed. But entering a statement
> that references the trigger will fail with an undefined field error.
>

Any way to validate "statically" all triggers within a schema/db-file in
one go? Using a pragma perhaps?
Having to come up with statements to validate/compile all possible triggers
seems error prone, no?


> When preparing a statement, SQLite will check for any triggers defined on
> the referenced tables, prepare the appropriate "trigger programs" and
> insert calls to these in the original "statement program".
>

Thanks for the reminder. (which is new to me, since not used SQLite
triggers yet).


> A simple UPDATE  set = where  will
> translate into about 40 instructions; adding your trigger adds an
> additional estimated 400 instructions of trigger program.
>

But the question is whether these extra VDBE instructions can cause
slowdowns, even when not executed at runtime? (i.e. branch using them is
not taken).

Basically, triggers are compiled into the statements that MAY cause them to
> fire;


Never realized that all statements are bigger that way, but it makes sense
in a server-less SQL engine like SQL, once you read it once. Thanks again
for that insight.

the WHEN clause is evaluated within the context of the trigger, even if the
> trigger does not fire at all.


What do you mean exactly by "within the context of the trigger"?
What other context is there (that the one from the outer statement)?
And can that context difference (?) expand different performance
characteristics?

Thanks, --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] builtin functions and strings with embedded nul characters

[R Smith]

On 2016/07/04 10:22 AM, Rob Golsteijn wrote:

@Clemens,



It is indeed documented that the behaviour is undefined when using a bind_text 
variant. I missed that part of documentation.


Hi Rob,
The behaviour is undefined in ALL instances where you pass null 
characters through C strings because of a C string peculiarity, not 
because of a shortcoming of SQLite.


I think you are missing an important bit in all of this - the strings in 
C is the problem, they think a Null character indicates termination. It 
has nothing to do with how SQL stores data - SQLite will store it with 
all bytes intact, but you typically retrieve or set it via some C calls 
using a C api.. and this is where the problem is. So whenever you want 
to push strings into the DB or get them out, and they do contain char(0) 
characters, then you need to read them into/from bytestreams, arrays, 
blobs, hex-encoded strings, or some or other method that will not be 
passing through a standard C string, because at that moment, and unless 
you force the length, the string will become shortened to the first zero 
byte found.


Thus, it is not the implementation that needs changing, but the usage.

I had this problem in a similar situation where I tried to store MBCS 
strings with 16-bit chars and 32-bit (4-byte) character strings. Apart 
from the enormous waste in 99% of characters, the trailing bytes were 
all Zero bytes (so character 'A' would be represented by 0x65 00 00 00) 
and if you try to store 'ABC' like that into the DB and then read it 
with just a C string, you end up with just A - but the DB still contains 
the full 'ABC', it's only your own string that doesn't know it. More 
wicked still, SQLite likely pushes the entire string to the memory 
location internally, so it is really there, but whatever next function 
operates on that string will only regard everything up to that first 
Zero byte thanks to C, and SQLite cannot help for that.


If SQLite could fix this - it wouldn't be documented as undefined, it 
would have been fixed.


As far as documenting the above... Any C developer reading this would 
probably giggle and think "Thanks captain obvious!", because this is 
really first-week stuff in a C-Programming-101 course. However, coming 
from other compiling platforms, this may not be very obvious. I prefer 
how Lazarus/Delphi does it (wrt the Pascal variant options as opposed to 
C++) where a string is a record with first the encoding, the length and 
then the actual bytes given. You never have to walk the memory to figure 
out the length and never care about null characters, it's all mapped in 
one place - but it does add overhead for small-ish strings and they have 
that stupid convention where the first character index is at 1 and not 0 
- yuck, so pro's and con's for all.


Cheers,
Ryan


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] builtin functions and strings with embedded nul characters

[Clemens Ladisch]

Rob Golsteijn wrote:
> It is indeed documented that the behaviour is undefined when using a 
> bind_text variant.

No.  The documentation says:
| The result of expressions involving strings with embedded NULs is undefined.

Using a bind_text variant is just one of the ways to construct a string with 
embedded NULs.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] retrieve by name or index

[Jim Wang]

hi,all
There is a table include id,string1,string2 and so on.
So I want to get the value of string1 from a record, there are two methods:
1.  get by the name of the string2
2.  get by the index of the string2, the index is 2
   which one is faster?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trigger slowness even when it's not fired

[Hick Gunter]

Creating a trigger causes SQLite to insert a row into the sqlite_master table 
which contains the text you supply for the trigger. No code is generated at 
this time. You can verify this by creating a trigger that references undefined 
fields. It willl succeed. But entering a statement that references the trigger 
will fail with an undefined field error.

When preparing a statement, SQLite will check for any triggers defined on the 
referenced tables, prepare the appropriate "trigger programs" and insert calls 
to these in the original "statement program".

A simple UPDATE  set = where  will 
translate into about 40 instructions; adding your trigger adds an additional 
estimated 400 instructions of trigger program.

Basically, triggers are compiled into the statements that MAY cause them to 
fire; the WHEN clause is evaluated within the context of the trigger, even if 
the trigger does not fire at all.

-Ursprüngliche Nachricht-
Von: sqlite-users-boun...@mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Hamish 
Symington
Gesendet: Montag, 04. Juli 2016 11:29
An: SQLite mailing list 
Betreff: Re: [sqlite] Trigger slowness even when it's not fired

Hi there,

> How many times are you preparing the update statement? Maybe you are just 
> measuring the effort required to prepare 55000 UPDATE statements.
OK, this sounds plausible. But given the trigger doesn’t even fire, why does 
the statement need to be prepared? Is it not prepared just before running?

> What is the purpose of counting all the rows of several tables before firing 
> the trigger? If you are attempting to avoid running UPDATE on an empty table, 
> then you are failing. The trigger will fire as soon as any one of the 
> mentioned tables has at least one row, without guaranteeing that the other 
> tables are not empty.
In the specific test scenario, all those tables are empty. It’s simply a way of 
ensuring that this trigger doesn’t fire for this test. I’ve just tried it with 
the following code, and it’s still slow, until I remove the update statements, 
when it’s faster.

Hamish



// When ContactCalc's phone, email or website changes, update the phone, email 
and website fields in // QuoteCalc, SaleCalc, PurchaseCalc and TxnCalc.
// Also update GroupHead phone, email and web fields for QuoteCalc, SaleCalc, 
PurchaseCalc, TxnCalc CREATE TRIGGER ContactCalcUpdate8 AFTER UPDATE OF 
ContactCalc_Phone, ContactCalc_Email, ContactCalc_Website ON ContactCalc WHEN (
Old.ContactCalc_Phone != New.ContactCalc_Phone
OR
Old.ContactCalc_Email != New.ContactCalc_Email
OR
Old.ContactCalc_Website != New.ContactCalc_Website
)

AND 1=0

BEGIN

INSERT INTO TriggerLog( TriggerLog_Name ) VALUES ('ContactCalcUpdate8');

… as before ...


>
> -Ursprüngliche Nachricht-
> Von: sqlite-users-boun...@mailinglists.sqlite.org
> [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von
> Hamish Symington
> Gesendet: Montag, 04. Juli 2016 10:27
> An: sqlite-users@mailinglists.sqlite.org
> Betreff: [sqlite] Trigger slowness even when it's not fired
>
> Hello,
>
> I have a curious situation involving a trigger, which I’m at a loss to 
> explain. I’m wondering if someone who knows more about the insides of SQLite 
> can tell me more about why it’s happening. I’m running SQLite 3.8.7.
>
> The trigger code is at the bottom of this email. It’s a straightforward AFTER 
> UPDATE trigger, firing when any of three fields is updated and when the old 
> value is not the same as the new value for any of those fields. There’s also 
> a test I’ve put in there to make sure that some tables which I’m looking to 
> update are not empty.
>
> The table TriggerLog is a table I’ve added for testing. If the trigger fires, 
> a row is inserted into the log. I have tested that this works when the 
> trigger fires.
>
> There are then a number of UPDATE statements to set flags on other tables. In 
> the circumstances I’m running this in at the moment, there are no rows in 
> QuoteCalc, SaleCalc, TxnCalc etc., so the trigger’s code won’t actually fire. 
> I have verified this by inspecting TriggerLog after the text; there are no 
> rows reporting that ContactCalcUpdate8 has fired.
>
> During the test, a row on ContactCalc has one of its ContactCalc_Phone, 
> ContactCalc_Email or ContactCalc_Website fields updated. This occurs on 
> various rows of ContactCalc approximately 5,000 times. This stage of the test 
> takes approximately 22 seconds.
>
> If I remove all of the UPDATE rows from this trigger, and make no other 
> changes, the test takes approximately 12 seconds.
>
> I don’t understand why removing code from the body of a trigger which doesn’t 
> fire makes things faster.
>
> I have also tried replacing the WHERE clauses of all of the UPDATE statements 
> with WHERE 1=0 to eliminate the possibility of the IN statement being the 
> 

Re: [sqlite] Trigger slowness even when it's not fired

[Hamish Symington]

Hi there,

> How many times are you preparing the update statement? Maybe you are just 
> measuring the effort required to prepare 55000 UPDATE statements.
OK, this sounds plausible. But given the trigger doesn’t even fire, why does 
the statement need to be prepared? Is it not prepared just before running? 

> What is the purpose of counting all the rows of several tables before firing 
> the trigger? If you are attempting to avoid running UPDATE on an empty table, 
> then you are failing. The trigger will fire as soon as any one of the 
> mentioned tables has at least one row, without guaranteeing that the other 
> tables are not empty.
In the specific test scenario, all those tables are empty. It’s simply a way of 
ensuring that this trigger doesn’t fire for this test. I’ve just tried it with 
the following code, and it’s still slow, until I remove the update statements, 
when it’s faster. 

Hamish



// When ContactCalc's phone, email or website changes, update the phone, email 
and website fields in 
// QuoteCalc, SaleCalc, PurchaseCalc and TxnCalc.
// Also update GroupHead phone, email and web fields for QuoteCalc, SaleCalc, 
PurchaseCalc, TxnCalc
CREATE TRIGGER ContactCalcUpdate8 AFTER UPDATE
OF ContactCalc_Phone,
ContactCalc_Email,
ContactCalc_Website
ON ContactCalc WHEN 
(
Old.ContactCalc_Phone != New.ContactCalc_Phone
OR 
Old.ContactCalc_Email != New.ContactCalc_Email
OR
Old.ContactCalc_Website != New.ContactCalc_Website
)

AND 1=0

BEGIN

INSERT INTO TriggerLog( TriggerLog_Name ) VALUES ('ContactCalcUpdate8');

… as before ...


> 
> -Ursprüngliche Nachricht-
> Von: sqlite-users-boun...@mailinglists.sqlite.org 
> [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Hamish 
> Symington
> Gesendet: Montag, 04. Juli 2016 10:27
> An: sqlite-users@mailinglists.sqlite.org
> Betreff: [sqlite] Trigger slowness even when it's not fired
> 
> Hello,
> 
> I have a curious situation involving a trigger, which I’m at a loss to 
> explain. I’m wondering if someone who knows more about the insides of SQLite 
> can tell me more about why it’s happening. I’m running SQLite 3.8.7.
> 
> The trigger code is at the bottom of this email. It’s a straightforward AFTER 
> UPDATE trigger, firing when any of three fields is updated and when the old 
> value is not the same as the new value for any of those fields. There’s also 
> a test I’ve put in there to make sure that some tables which I’m looking to 
> update are not empty.
> 
> The table TriggerLog is a table I’ve added for testing. If the trigger fires, 
> a row is inserted into the log. I have tested that this works when the 
> trigger fires.
> 
> There are then a number of UPDATE statements to set flags on other tables. In 
> the circumstances I’m running this in at the moment, there are no rows in 
> QuoteCalc, SaleCalc, TxnCalc etc., so the trigger’s code won’t actually fire. 
> I have verified this by inspecting TriggerLog after the text; there are no 
> rows reporting that ContactCalcUpdate8 has fired.
> 
> During the test, a row on ContactCalc has one of its ContactCalc_Phone, 
> ContactCalc_Email or ContactCalc_Website fields updated. This occurs on 
> various rows of ContactCalc approximately 5,000 times. This stage of the test 
> takes approximately 22 seconds.
> 
> If I remove all of the UPDATE rows from this trigger, and make no other 
> changes, the test takes approximately 12 seconds.
> 
> I don’t understand why removing code from the body of a trigger which doesn’t 
> fire makes things faster.
> 
> I have also tried replacing the WHERE clauses of all of the UPDATE statements 
> with WHERE 1=0 to eliminate the possibility of the IN statement being the 
> culprit; it’s still slow.
> 
> I would very much like to understand what’s going on here; perhaps someone 
> can enlighten me.
> 
> Thanks,
> 
> Hamish
> 
> 
> 
> 
> // When ContactCalc's phone, email or website changes, update the phone, 
> email and website fields in
> // QuoteCalc, SaleCalc, PurchaseCalc and TxnCalc.
> // Also update GroupHead phone, email and web fields for QuoteCalc, SaleCalc, 
> PurchaseCalc, TxnCalc
> CREATE TRIGGER ContactCalcUpdate8 AFTER UPDATE
> OF ContactCalc_Phone,
> ContactCalc_Email,
> ContactCalc_Website
> ON ContactCalc WHEN
> (
>Old.ContactCalc_Phone != New.ContactCalc_Phone
>OR
>Old.ContactCalc_Email != New.ContactCalc_Email
>OR
>Old.ContactCalc_Website != New.ContactCalc_Website
> )
> 
> AND
> (
>(
>SELECT COUNT(QuoteCalc_QuoteUUID) FROM QuoteCalc
>) > 0
>OR
>(
>SELECT COUNT(SaleCalc_SaleUUID) FROM SaleCalc
>) > 0
>OR
>(
>SELECT COUNT(PurchaseCalc_PurchaseUUID) FROM PurchaseCalc
>) > 0
>OR
>(
>SELECT COUNT(TxnCalc_TxnUUID) FROM TxnCalc
>) > 0
> )
> 
> BEGIN
> 
>INSERT INTO 

Re: [sqlite] Trigger slowness even when it's not fired

[Dominique Devienne]

On Mon, Jul 4, 2016 at 10:26 AM, Hamish Symington <
ham...@lightbluesoftware.com> wrote:

> [...] If I remove all of the UPDATE rows from this trigger, and make no
> other changes, the test takes approximately 12 seconds.
> I don’t understand why removing code from the body of a trigger which
> doesn’t fire makes things faster.
> [...] I would very much like to understand what’s going on here; perhaps
> someone can enlighten me.
>

The only thing I can think are your "select count(col) from calc" in the
WHEN clause of the trigger.

If we assume the number of rows in those tables are different in your two
cases / runs,
then the time to full-scan those (or a covering index) will
increase/decrease,
w/o the body of the trigger firing (because it didn't pass the WHEN clause).

Nothing suggests from your post they'd be different, but that's the only
thing I see that could make such a difference. FWIW. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trigger slowness even when it's not fired

[Hick Gunter]

How many times are you preparing the update statement? Maybe you are just 
measuring the effort required to prepare 55000 UPDATE statements.

What is the purpose of counting all the rows of several tables before firing 
the trigger? If you are attempting to avoid running UPDATE on an empty table, 
then you are failing. The trigger will fire as soon as any one of the mentioned 
tables has at least one row, without guaranteeing that the other tables are not 
empty.

-Ursprüngliche Nachricht-
Von: sqlite-users-boun...@mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Hamish 
Symington
Gesendet: Montag, 04. Juli 2016 10:27
An: sqlite-users@mailinglists.sqlite.org
Betreff: [sqlite] Trigger slowness even when it's not fired

Hello,

I have a curious situation involving a trigger, which I’m at a loss to explain. 
I’m wondering if someone who knows more about the insides of SQLite can tell me 
more about why it’s happening. I’m running SQLite 3.8.7.

The trigger code is at the bottom of this email. It’s a straightforward AFTER 
UPDATE trigger, firing when any of three fields is updated and when the old 
value is not the same as the new value for any of those fields. There’s also a 
test I’ve put in there to make sure that some tables which I’m looking to 
update are not empty.

The table TriggerLog is a table I’ve added for testing. If the trigger fires, a 
row is inserted into the log. I have tested that this works when the trigger 
fires.

There are then a number of UPDATE statements to set flags on other tables. In 
the circumstances I’m running this in at the moment, there are no rows in 
QuoteCalc, SaleCalc, TxnCalc etc., so the trigger’s code won’t actually fire. I 
have verified this by inspecting TriggerLog after the text; there are no rows 
reporting that ContactCalcUpdate8 has fired.

During the test, a row on ContactCalc has one of its ContactCalc_Phone, 
ContactCalc_Email or ContactCalc_Website fields updated. This occurs on various 
rows of ContactCalc approximately 5,000 times. This stage of the test takes 
approximately 22 seconds.

If I remove all of the UPDATE rows from this trigger, and make no other 
changes, the test takes approximately 12 seconds.

I don’t understand why removing code from the body of a trigger which doesn’t 
fire makes things faster.

I have also tried replacing the WHERE clauses of all of the UPDATE statements 
with WHERE 1=0 to eliminate the possibility of the IN statement being the 
culprit; it’s still slow.

I would very much like to understand what’s going on here; perhaps someone can 
enlighten me.

Thanks,

Hamish




// When ContactCalc's phone, email or website changes, update the phone, email 
and website fields in
// QuoteCalc, SaleCalc, PurchaseCalc and TxnCalc.
// Also update GroupHead phone, email and web fields for QuoteCalc, SaleCalc, 
PurchaseCalc, TxnCalc
CREATE TRIGGER ContactCalcUpdate8 AFTER UPDATE
OF ContactCalc_Phone,
ContactCalc_Email,
ContactCalc_Website
ON ContactCalc WHEN
(
Old.ContactCalc_Phone != New.ContactCalc_Phone
OR
Old.ContactCalc_Email != New.ContactCalc_Email
OR
Old.ContactCalc_Website != New.ContactCalc_Website
)

AND
(
(
SELECT COUNT(QuoteCalc_QuoteUUID) FROM QuoteCalc
) > 0
OR
(
SELECT COUNT(SaleCalc_SaleUUID) FROM SaleCalc
) > 0
OR
(
SELECT COUNT(PurchaseCalc_PurchaseUUID) FROM PurchaseCalc
) > 0
OR
(
SELECT COUNT(TxnCalc_TxnUUID) FROM TxnCalc
) > 0
)

BEGIN

INSERT INTO TriggerLog( TriggerLog_Name ) VALUES ('ContactCalcUpdate8');


UPDATE QuoteCalc
SET QuoteCalc_UpdateContactFlag = 1
WHERE QuoteCalc_ContactUUID = New.ContactCalc_ContactUUID;


UPDATE SaleCalc
SET SaleCalc_UpdateBillingContactFlag = 1
WHERE SaleCalc_SaleUUID IN
(
SELECT Sale_UUID
FROM Sale
WHERE Sale_BillingContactUUID = New.ContactCalc_ContactUUID
);

UPDATE SaleCalc
SET SaleCalc_UpdateShippingContactFlag = 1
WHERE SaleCalc_SaleUUID IN
(
SELECT Sale_UUID
FROM Sale
WHERE Sale_ShippingContactUUID = New.ContactCalc_ContactUUID
);


UPDATE TxnCalc
SET TxnCalc_UpdateContactFlag = 1
WHERE TxnCalc_TxnUUID IN
(
SELECT TxnCalc_TxnUUID
FROM TxnCalc
WHERE TxnCalc_ContactUUID = New.ContactCalc_ContactUUID
);


UPDATE PurchaseCalc
SET PurchaseCalc_UpdateContactFlag = 1
WHERE PurchaseCalc_PurchaseUUID IN
(
SELECT PurchaseCalc_PurchaseUUID
FROM PurchaseCalc
WHERE PurchaseCalc_ContactUUID = New.ContactCalc_ContactUUID
);


UPDATE ContactCalc
SET 

Re: [sqlite] Trigger slowness even when it's not fired

[Hamish Symington]

Hi there,

> 
> It's known that triggers aren't SQLite most remarkable strength, even if I 
> never encountered a situation where their relative slowness significantly 
> exceeds their usefulness.
> In your situation I believe your coumpond triggering condition needs 
> parenthesis. Your indentation of AND is meaningless to the parser.

You’re quite correct; that was a slip in my copying out the code into this 
email. The parentheses as you suggest already exist in my trigger. Very many 
apologies for missing that.

Hamish


> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trigger slowness even when it's not fired

[Jean-Christophe Deschamps]

At 10:26 04/07/2016, you wrote:


Hello,

I have a curious situation involving a trigger, which I’m at a loss 
to explain. I’m wondering if someone who knows more about the 
insides of SQLite can tell me more about why it’s happening. I’m 
running SQLite 3.8.7.


The trigger code is at the bottom of this email. It’s a 
straightforward AFTER UPDATE trigger, firing when any of three fields 
is updated and when the old value is not the same as the new value for 
any of those fields. There’s also a test I’ve put in there to make 
sure that some tables which I’m looking to update are not empty.


The table TriggerLog is a table I’ve added for testing. If the 
trigger fires, a row is inserted into the log. I have tested that this 
works when the trigger fires.


There are then a number of UPDATE statements to set flags on other 
tables. In the circumstances I’m running this in at the moment, 
there are no rows in QuoteCalc, SaleCalc, TxnCalc etc., so the 
trigger’s code won’t actually fire. I have verified this by 
inspecting TriggerLog after the text; there are no rows reporting that 
ContactCalcUpdate8 has fired.


During the test, a row on ContactCalc has one of its 
ContactCalc_Phone, ContactCalc_Email or ContactCalc_Website fields 
updated. This occurs on various rows of ContactCalc approximately 
5,000 times. This stage of the test takes approximately 22 seconds.


If I remove all of the UPDATE rows from this trigger, and make no 
other changes, the test takes approximately 12 seconds.


I don’t understand why removing code from the body of a trigger 
which doesn’t fire makes things faster.


I have also tried replacing the WHERE clauses of all of the UPDATE 
statements with WHERE 1=0 to eliminate the possibility of the IN 
statement being the culprit; it’s still slow.


I would very much like to understand what’s going on here; perhaps 
someone can enlighten me.


Thanks,

Hamish




// When ContactCalc's phone, email or website changes, update the 
phone, email and website fields in

// QuoteCalc, SaleCalc, PurchaseCalc and TxnCalc.
// Also update GroupHead phone, email and web fields for QuoteCalc, 
SaleCalc, PurchaseCalc, TxnCalc

CREATE TRIGGER ContactCalcUpdate8 AFTER UPDATE
OF ContactCalc_Phone,
ContactCalc_Email,
ContactCalc_Website
ON ContactCalc WHEN

Old.ContactCalc_Phone != New.ContactCalc_Phone
OR
Old.ContactCalc_Email != New.ContactCalc_Email
OR
Old.ContactCalc_Website != New.ContactCalc_Website


AND

(
SELECT COUNT(QuoteCalc_QuoteUUID) FROM QuoteCalc
) > 0
OR
(
SELECT COUNT(SaleCalc_SaleUUID) FROM SaleCalc
) > 0
OR
(
SELECT COUNT(PurchaseCalc_PurchaseUUID) FROM PurchaseCalc
) > 0
OR
(
SELECT COUNT(TxnCalc_TxnUUID) FROM TxnCalc
) > 0


BEGIN

INSERT INTO TriggerLog( TriggerLog_Name ) VALUES 
('ContactCalcUpdate8');



UPDATE QuoteCalc
SET QuoteCalc_UpdateContactFlag = 1
WHERE QuoteCalc_ContactUUID = New.ContactCalc_ContactUUID;


UPDATE SaleCalc
SET SaleCalc_UpdateBillingContactFlag = 1
WHERE SaleCalc_SaleUUID IN
(
SELECT Sale_UUID
FROM Sale
WHERE Sale_BillingContactUUID = New.ContactCalc_ContactUUID
);

UPDATE SaleCalc
SET SaleCalc_UpdateShippingContactFlag = 1
WHERE SaleCalc_SaleUUID IN
(
SELECT Sale_UUID
FROM Sale
WHERE Sale_ShippingContactUUID = New.ContactCalc_ContactUUID
);


UPDATE TxnCalc
SET TxnCalc_UpdateContactFlag = 1
WHERE TxnCalc_TxnUUID IN
(
SELECT TxnCalc_TxnUUID
FROM TxnCalc
WHERE TxnCalc_ContactUUID = New.ContactCalc_ContactUUID
);


UPDATE PurchaseCalc
SET PurchaseCalc_UpdateContactFlag = 1
WHERE PurchaseCalc_PurchaseUUID IN
(
SELECT PurchaseCalc_PurchaseUUID
FROM PurchaseCalc
WHERE PurchaseCalc_ContactUUID = New.ContactCalc_ContactUUID
);


UPDATE ContactCalc
SET ContactCalc_UpdateGroupHeadFlag = 1
WHERE ContactCalc_ContactUUID IN
(
SELECT Contact_UUID
FROM Contact
WHERE Contact_LinkedContactUUID = New.ContactCalc_ContactUUID
);



UPDATE QuoteCalc
SET QuoteCalc_UpdateGroupHeadFlag = 1
WHERE QuoteCalc_QuoteUUID IN
(
SELECT Quote_UUID
FROM Quote,
Contact
WHERE Quote_ContactUUID = Contact_UUID
AND Contact_LinkedContactUUID = New.ContactCalc_ContactUUID
);


UPDATE SaleCalc
SET SaleCalc_UpdateBillingGroupHeadFlag = 1
WHERE SaleCalc_SaleUUID IN
(
SELECT Sale_UUID
FROM Sale,
Contact
WHERE Sale_BillingContactUUID = Contact_UUID
AND Contact_LinkedContactUUID = New.ContactCalc_ContactUUID

);



UPDATE SaleCalc
SET SaleCalc_UpdateShippingGroupHeadFlag = 1
WHERE SaleCalc_SaleUUID IN
(
SELECT Sale_UUID
FROM Sale,
Contact
WHERE Sale_ShippingContactUUID = 

[sqlite] Trigger slowness even when it's not fired

[Hamish Symington]

Hello,

I have a curious situation involving a trigger, which I’m at a loss to explain. 
I’m wondering if someone who knows more about the insides of SQLite can tell me 
more about why it’s happening. I’m running SQLite 3.8.7.

The trigger code is at the bottom of this email. It’s a straightforward AFTER 
UPDATE trigger, firing when any of three fields is updated and when the old 
value is not the same as the new value for any of those fields. There’s also a 
test I’ve put in there to make sure that some tables which I’m looking to 
update are not empty.

The table TriggerLog is a table I’ve added for testing. If the trigger fires, a 
row is inserted into the log. I have tested that this works when the trigger 
fires. 

There are then a number of UPDATE statements to set flags on other tables. In 
the circumstances I’m running this in at the moment, there are no rows in 
QuoteCalc, SaleCalc, TxnCalc etc., so the trigger’s code won’t actually fire. I 
have verified this by inspecting TriggerLog after the text; there are no rows 
reporting that ContactCalcUpdate8 has fired. 

During the test, a row on ContactCalc has one of its ContactCalc_Phone, 
ContactCalc_Email or ContactCalc_Website fields updated. This occurs on various 
rows of ContactCalc approximately 5,000 times. This stage of the test takes 
approximately 22 seconds.

If I remove all of the UPDATE rows from this trigger, and make no other 
changes, the test takes approximately 12 seconds. 

I don’t understand why removing code from the body of a trigger which doesn’t 
fire makes things faster. 

I have also tried replacing the WHERE clauses of all of the UPDATE statements 
with WHERE 1=0 to eliminate the possibility of the IN statement being the 
culprit; it’s still slow. 

I would very much like to understand what’s going on here; perhaps someone can 
enlighten me. 

Thanks,

Hamish




// When ContactCalc's phone, email or website changes, update the phone, email 
and website fields in 
// QuoteCalc, SaleCalc, PurchaseCalc and TxnCalc.
// Also update GroupHead phone, email and web fields for QuoteCalc, SaleCalc, 
PurchaseCalc, TxnCalc
CREATE TRIGGER ContactCalcUpdate8 AFTER UPDATE
OF ContactCalc_Phone,
ContactCalc_Email,
ContactCalc_Website
ON ContactCalc WHEN 
(
Old.ContactCalc_Phone != New.ContactCalc_Phone
OR 
Old.ContactCalc_Email != New.ContactCalc_Email
OR
Old.ContactCalc_Website != New.ContactCalc_Website
)

AND
(
(
SELECT COUNT(QuoteCalc_QuoteUUID) FROM QuoteCalc
) > 0
OR 
(
SELECT COUNT(SaleCalc_SaleUUID) FROM SaleCalc
) > 0
OR 
(
SELECT COUNT(PurchaseCalc_PurchaseUUID) FROM PurchaseCalc
) > 0
OR 
(
SELECT COUNT(TxnCalc_TxnUUID) FROM TxnCalc
) > 0
)

BEGIN

INSERT INTO TriggerLog( TriggerLog_Name ) VALUES ('ContactCalcUpdate8');


UPDATE QuoteCalc
SET QuoteCalc_UpdateContactFlag = 1
WHERE QuoteCalc_ContactUUID = New.ContactCalc_ContactUUID;


UPDATE SaleCalc
SET SaleCalc_UpdateBillingContactFlag = 1
WHERE SaleCalc_SaleUUID IN
(
SELECT Sale_UUID
FROM Sale
WHERE Sale_BillingContactUUID = New.ContactCalc_ContactUUID
);

UPDATE SaleCalc
SET SaleCalc_UpdateShippingContactFlag = 1
WHERE SaleCalc_SaleUUID IN
(
SELECT Sale_UUID
FROM Sale
WHERE Sale_ShippingContactUUID = New.ContactCalc_ContactUUID
);


UPDATE TxnCalc
SET TxnCalc_UpdateContactFlag = 1
WHERE TxnCalc_TxnUUID IN
(
SELECT TxnCalc_TxnUUID
FROM TxnCalc
WHERE TxnCalc_ContactUUID = New.ContactCalc_ContactUUID
);


UPDATE PurchaseCalc
SET PurchaseCalc_UpdateContactFlag = 1
WHERE PurchaseCalc_PurchaseUUID IN
(
SELECT PurchaseCalc_PurchaseUUID
FROM PurchaseCalc
WHERE PurchaseCalc_ContactUUID = New.ContactCalc_ContactUUID
);


UPDATE ContactCalc
SET ContactCalc_UpdateGroupHeadFlag = 1
WHERE ContactCalc_ContactUUID IN
(
SELECT Contact_UUID
FROM Contact
WHERE Contact_LinkedContactUUID = New.ContactCalc_ContactUUID
);



UPDATE QuoteCalc
SET QuoteCalc_UpdateGroupHeadFlag = 1
WHERE QuoteCalc_QuoteUUID IN
(
SELECT Quote_UUID
FROM Quote,
Contact
WHERE Quote_ContactUUID = Contact_UUID
AND Contact_LinkedContactUUID = New.ContactCalc_ContactUUID
);


UPDATE SaleCalc
SET SaleCalc_UpdateBillingGroupHeadFlag = 1
WHERE SaleCalc_SaleUUID IN

Re: [sqlite] builtin functions and strings with embedded nul characters

[Rob Golsteijn]

@Clemens,



It is indeed documented that the behaviour is undefined when using a bind_text 
variant. I missed that part of documentation.



On the other, as shown in my test queries, it is possible to construct strings 
with embedded nuls in sql itself, not using the C api (so technically, not 
allowing nuls could be a limitation of the C API). And as also remarked before 
by Simon an (embedded) nul is special in C-like languages. That made me wonder 
if sqlite would see an embedded nul as a "normal" character. Therefor my tests 
to see how sqlite functions operate on strings containing such nuls. From my 
experiments I could not include whether nul was "normal" or "special".

The way I constructed TEXT values with embedded nuls is just a minor variation 
on how we add (unix) newlines in sql: 'line1' || x'0a' || 'line2". So, if nuls 
were a normal character this way to construct such a string with embedded nuls 
would not be very strange.



I understand that changing the implementation would be a risk for backward 
compatibility and that most developers (including my company) do not want to 
used embedded nuls.

My post was mainly intended to SHOW that string functions behave inconsistent 
when they contain embedded nuls, and WARN developers for this. Therefor, I 
asked to document if embedded nuls in string are allowed, e.g. on the 
www.sqlite.org/lang_corefunc.html.



@Simon,

Our original bug was caused by specifying an incorrect length for our C string 
using the C API.

After we found our bug I did my tests, shown in my original post, using the 
SQlite command line tool.






Rob Golsteijn wrote:
> Due to a bug in our own code we inserted a string with embedded nul
> character in the database.

 says:
| The result of expressions involving strings with embedded NULs is
| undefined.

> I investigated how the builtin functions handle strings with embedded
> nul characters.

Everything you found falls under the label "undefined".

--

> Below my test queries (sqlite version 3.11.1), executed on TEXT data and BLOB 
> data.


Nice set of tests.  Did you execute them in the SQLite command-line tool or 
your own program ?


Simon.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users