Re: [sqlite] [EXTERNAL] Implementing a statement cache

[Hick Gunter]

Any statement that has been stepped but not to completion will hold open the 
transaction on a connection. This may interfere with your expectations. 
Clearing bindings as a precaution will prevent inadvertent re-use of old 
bindings. The statement may have to be reset first, see documentation. 
Statements prepared with the V2 (or later) interface will recompile if 
necessary.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von carsten.muencheberg
Gesendet: Montag, 16. Dezember 2019 22:38
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] Implementing a statement cache

Hi,
I am working on a generic cache for prepared statements and would like to make 
sure that I am not overlooking anything important.

The cache is a simple map from an SQL string to a statement pointer.

1. When to call sqlite3_reset()? It looks like the safest and easiest approach 
is to call sqlite3_reset() immediately after retrieving a statement from the 
cache. Is there any disadvantage in regards to concurrency or performance in 
keeping dozens or hundreds of statements alive in a non reset state e.g. SELECT 
statements which have not stepped over all rows?

2. When to call sqlite3_clear_bindings()? If I understand correctly new values 
can be bound without clearing old ones first, but calling
sqlite3_clear_bindings() can be a safeguard against accidentally executing a 
statement with old values?

3. When to clear the cache? I read that in some cases statements are 
automatically recompiled when a different value is bound to a parameter inside 
the WHERE clause. What about SQLITE_ENABLE_STAT4 and ANALYZE, would it make 
sense to clear the cache afterwards or can we trust SQLite to maintain existing 
statements under all circumstances?

4. Other ideas, comments?

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


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Implementing a statement cache

[carsten.muencheberg]

Hi,
I am working on a generic cache for prepared statements and would like 
to make sure that I am not overlooking anything important.


The cache is a simple map from an SQL string to a statement pointer.

1. When to call sqlite3_reset()? It looks like the safest and easiest 
approach is to call sqlite3_reset() immediately after retrieving a 
statement from the cache. Is there any disadvantage in regards to 
concurrency or performance in keeping dozens or hundreds of statements 
alive in a non reset state e.g. SELECT statements which have not stepped 
over all rows?


2. When to call sqlite3_clear_bindings()? If I understand correctly new 
values can be bound without clearing old ones first, but calling 
sqlite3_clear_bindings() can be a safeguard against accidentally 
executing a statement with old values?


3. When to clear the cache? I read that in some cases statements are 
automatically recompiled when a different value is bound to a parameter 
inside the WHERE clause. What about SQLITE_ENABLE_STAT4 and ANALYZE, 
would it make sense to clear the cache afterwards or can we trust SQLite 
to maintain existing statements under all circumstances?


4. Other ideas, comments?

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

Re: [sqlite] sqlite3_interrupt and explicit transactions

[Keith Medcalf]

On Monday, 16 December, 2019 12:17, Jesse Rittner  
wrote:

> I have a few questions about how sqlite3_interrupt interacts with
> explicit transaction operations. The docs say that "If the interrupted 
> SQL operation is an INSERT, UPDATE, or DELETE that is inside an 
> explicit transaction, then the entire transaction will be rolled back 
> automatically."

Less than 3 minutes of testing and code analysis reveals:

> 1. Can sqlite3_interrupt interrupt a call to BEGIN or its variants (BEGIN
> IMMEDIATE and BEGIN EXCLUSIVE)? If so, is the transaction automatically
> "rolled back" in this case?

No.  Setting transaction mode and autocommit mode is not an interruptible 
operation.  See * below.

>2. What about an interrupt during an explicit call to COMMIT or ROLLBACK?

No.  Re-enabling autocommit is not an interruptible operation.  See * below.

>3. What is the behavior with regard to savepoints? Will the outermost
>transaction get rolled back if an operation gets interrupted?

Yes.

You can always determine whether a transaction is in process on a database 
connection by calling sqlite3_get_autocommit.

sqlite3_interrupt sets an isInterrupted flag on a connection.

When sqlite3_exec is called on a non-running statement (ie, for the first time) 
AND isInterrupted is set AND there are no running statements the isInterrupted 
flag is reset.  The statement will then commence running if isInterrupted is 
clear, and not run if isInterrupted is set.

When a VDBE program is executing it checks after certain opcodes if the 
isInterrupted flag is set and if it is the executing statement (and its 
containing transaction) are aborted.

*Note that in order for an interrupt to have any effect, the VDBE program must 
be running.  If the VDBE program is "paused" (as in the statement has returned 
a row and is waiting for the next sqlite3_exec) it will not be interrupted 
until you call sqlite3_exec on that statement the next time.  The isInterupted 
flag will remain set until all "busy" statements on the connection have been 
interrupted.  That means that if you start a statement executing and do not 
step it to completion (or reset it) the isInterrupted will remain in effect 
until you do, effectively precluding your ability to execute any statements 
whatsoever on that connection until the connection is no longer busy, as the 
purpose of the sqlite3_interrupt is to cancel pending operations on a 
connection and return it to a non-busy state.

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.



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

Re: [sqlite] sqlite3_interrupt and explicit transactions

[Simon Slavin]

On 16 Dec 2019, at 7:16pm, Jesse Rittner  wrote:

> 1. Can sqlite3_interrupt interrupt a call to BEGIN or its variants (BEGIN
> IMMEDIATE and BEGIN EXCLUSIVE)? If so, is the transaction automatically
> "rolled back" in this case?
> 2. What about an interrupt during an explicit call to COMMIT or ROLLBACK?
> 3. What is the behavior with regard to savepoints? 

1. If BEGIN is interrupted, no transaction is started.
2. This results in the equivalent of ROLLBACK
3. This results in the equivalent of ROLLBACK.  Savepoints are lost.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3_interrupt and explicit transactions

[Jesse Rittner]

I have a few questions about how sqlite3_interrupt interacts with explicit
transaction operations. The docs say that "If the interrupted SQL operation
is an INSERT, UPDATE, or DELETE that is inside an explicit transaction, then
the entire transaction will be rolled back automatically."
1. Can sqlite3_interrupt interrupt a call to BEGIN or its variants (BEGIN
IMMEDIATE and BEGIN EXCLUSIVE)? If so, is the transaction automatically
"rolled back" in this case?
2. What about an interrupt during an explicit call to COMMIT or ROLLBACK?
3. What is the behavior with regard to savepoints? Will the outermost
transaction get rolled back if an operation gets interrupted?



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Difference between hex notation and string notation

[Keith Medcalf]

You will also note that the bytes in the blob must be the bytes in the 
underlying database text encoding in order for a cast to text to produce 
expected output (assuming that expected means valid text):

sqlite> pragma encoding;
UTF-8
sqlite> select x'414243';
ABC
sqlite> pragma encoding('utf-16le');
sqlite> select x'004100420043';
???
sqlite> select x'410042004300';
ABC
sqlite> pragma encoding('utf-16be');
sqlite> select x'004100420043';
ABC

So in order for "blobs" to be freely coerceable to text, you need to know the 
database encoding, and if you get it wrong, your blob cannot be corerced to 
text.  This is why a blob is blob and text is text.  As long as the 
input/output text format and the database encoding are the same, there is 
effectively no difference *in the raw bytes*.  However, as soon as this is not 
the case, conversions must be performed and a blob may not contain a valid byte 
sequence in the underlying encoding.
 
-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users  On
>Behalf Of Hick Gunter
>Sent: Monday, 16 December, 2019 00:42
>To: 'SQLite mailing list' 
>Subject: Re: [sqlite] [EXTERNAL] Difference between hex notation and
>string notation
>
>The X'' notation returns a blob. LIKE works with strings. Comparing a
>string to a blob of the same content always returns false.
>
>-Ursprüngliche Nachricht-
>Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
>Im Auftrag von Sascha Ziemann
>Gesendet: Freitag, 13. Dezember 2019 10:16
>An: sqlite-users@mailinglists.sqlite.org
>Betreff: [EXTERNAL] [sqlite] Difference between hex notation and string
>notation
>
>I have a problem to find rows in a database when I write in hex notation:
>
>CREATE TABLE LOG (MSG VARCHAR(6291456) NOT NULL); INSERT INTO LOG VALUES
>(X'666163696c6974793d6461656d6f6e3b636f6d706f6e656e743d6e616d65643b746578
>743d7368757474696e6720646f776e');
>INSERT INTO LOG VALUES ('facility=daemon;component=named;text=shutting
>down');
>SELECT ROWID,MSG FROM LOG; --
>returns both rows
>SELECT ROWID,MSG FROM LOG WHERE MSG LIKE '%down';  --
>returns just the second
>SELECT ROWID,MSG FROM LOG WHERE CAST(MSG AS VARCHAR) LIKE '%down'; --
>returns both rows
>
>This looks like a bug to me.
>
>Regards
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>___
> Gunter Hick | Software Engineer | Scientific Games International GmbH |
>Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 |
>(O) +43 1 80100 - 0
>
>May be privileged. May be confidential. Please delete if not the
>addressee.
>___
>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] Performance vs. memory trade-off question

[Nelson, Erik - 2]

Dominique Devienne wrote on Monday, December 16, 2019 7:46 AM
>On Sat, Dec 14, 2019 at 2:27 PM Richard Hipp  wrote:

>> QUESTION:  Should this feature be default-on or default-off?
>>
>> What's more important to you?  0.25% fewer CPU cycles or about 72KB
>> less heap space used per database connection?

>Backward compatibility. I.e. if I change nothing in my build, and upgrade
>the amalgamation,
default-off at compile-time.

For my own clarification... if this is default-on, and I upgrade the 
amalgamation, the performance will be improved *and* the connection heap space 
will be reduced.  Is that correct?

Erik
 

--
This message, and any attachments, is for the intended recipient(s) only, may 
contain information that is privileged, confidential and/or proprietary and 
subject to important terms and conditions available at 
http://www.bankofamerica.com/emaildisclaimer.   If you are not the intended 
recipient, please delete this message.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance vs. memory trade-off question

[Jose Isaias Cabrera]

Give me speed anytime and twice on Sundays...


From: sqlite-users  on behalf of 
Eric Grange 
Sent: Saturday, December 14, 2019 04:11 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Performance vs. memory trade-off question

While I am using SQLIite mostly in x86-64 with plentiful RAM, I suspect the
reduced lookaside size will help CPU L1/L2 caches, which is definitely
never plentiful. I would not be surprised if it resulted in a speedup in
practical high-load multi-threaded scenarios.

Also future SQLite code changes may be able to further build upon a new
lookaside configuration by tweaking the structure sizes to more tightly
match with the slot sizes f.i., and reduce the performance hit even in
single-threaded cases.

So "on by default" for me.

Eric

Le sam. 14 déc. 2019 à 14:27, Richard Hipp  a écrit :

> A new feature on a branch has the following disadvantages:
>
> (1)  It uses about 0.25% more CPU cycles.  (Each release of SQLite is
> normally about 0.5% faster, so enabling this feature is sort of like
> going back by one-half of a release cycle.)
>
> (2)  The code space (the size of the library) is between 400 and 500
> bytes larger (depending on compiler and optimization settings).
>
> The this one advantage:
>
> (3)  Each database connection uses about 72 KB less heap space.
>
> QUESTION:  Should this feature be default-on or default-off?
>
> What's more important to you?  0.25% fewer CPU cycles or about 72KB
> less heap space used per database connection?
>
> The feature can be activated or deactivated at start-time, but you
> take the disadvantages (the performance hit and slightly larger
> library size) regardless, unless you disable the feature at
> compile-time.  If the feature is compile-time disabled, then the
> corresponding code is omitted and and it cannot be turned on at
> start-time.
>
> If you have opinions, you can reply to this mailing list, or directly to
> me.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] Performance vs. memory trade-off question

[Dominique Devienne]

On Sat, Dec 14, 2019 at 2:27 PM Richard Hipp  wrote:

> QUESTION:  Should this feature be default-on or default-off?
>
> What's more important to you?  0.25% fewer CPU cycles or about 72KB
> less heap space used per database connection?
>

Backward compatibility. I.e. if I change nothing in my build, and upgrade
the amalgamation,
then I get the behavior closest to the one before this change. Which if
default-off at compile-time.

Embedded (or many connection) scenarios wanting this can always turn it on
explicitly. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A crash bug in sqlite

[Richard Hipp]

The bug does not exist in any released version of SQLite.  It only
appears in unreleased development versions.

On 12/15/19, Chris Brody  wrote:
>> Yes.  I discovered the same thing independently.  The previous fix was
>> subtly wrong.  Please try the latest trunk version.
>
> Will there be a patch release, or should we just wait for the next minor?
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


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

[sqlite] Bug report

[Mark Benningfield]

In version 3.30.1 ( check-in  [9b14eb77] ), file "sqlite3ext.h"

618  #define sqlite3_stmt_isexplain sqlite3_api->isexplain
619  #define sqlite3_value_frombind sqlite3_api->frombind

should be

618  #define sqlite3_stmt_isexplain sqlite3_api->stmt_isexplain
619  #define sqlite3_value_frombind sqlite3_api->value_frombind

Errors:
"isexplain" is not a member of struct sqlite3_api
"frombind" is not a member of struct sqlite3_api



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL Murder Mystery

[Stefan Evert]

> On 16 Dec 2019, at 04:59, Simon Slavin  wrote:
> 
> A little light relief:
> 
> 
> 
> Chosen SQL variation is SQLite !


Nice, but the crime is so easy to solve …

SELECT CAST(x'4A6572656D7920426F77657273' AS TEXT);

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