Re: [sqlite] Atomic DELETE index optimisation?

On Tue, Dec 19, 2017 at 6:05 PM, Simon Slavin wrote: > On 19 Dec 2017, at 4:15pm, Dinu wrote: > > 3) "Deleted" bit field - presumably the "soft delete" as you call it; > If you do try this, the 'bit' column should be declared as INTEGER and the >

[sqlite] Tcl script to get temperature statistics

I promised to share my fruits with Tcl. I took some time (very busy with a lot of Tcl things), but here is one example. I had some problems with the temperature of my CPU. So I wrote a systemd service (with Tcl) to log the temperature every minute to a SQLite database. And wrote the following

Re: [sqlite] Can we create a table where the value of one particular column <> another column?

Nice solution! CREATE TABLE edges(parent references nodes, child references nodes check (parent<>child)); seems to be an equivalent but shorter statement. On 20 December 2017 at 07:49, Simon Slavin wrote: > > > Yes ! > > CREATE TABLE edges( > parent INTEGER

Re: [sqlite] Can we create a table where the value of one particular column <> another column?

Now I using the following - CREATE TABLE nodes(id integer primary key, description text); CREATE TABLE edges(parent references nodes not null, child references nodes not null check (parent<>child), primary key(parent, child)); This seems to prevent the insertion of duplicate and parent=child

[sqlite] SELECT query #first run# is very slow VOL2

hi all, I have exactly the same problem with topic : http://sqlite.1065341.n5.nabble.com/SELECT-query-first-run-is-VERY-slow-td33100i20.html -- The dbase sitting on linux server, Im accessing the dbase with PDO object. -connected -prepare the statement -execute here makes =>1min lag, *only the

Re: [sqlite] Macro expansion of B0 on Linux PPC system

The problem appears to come from the inclusion of editline/readline.h, which ultimately includes termios.h. I'm not sure who should "fix" this, but below is a simple reproducer demonstrating the error. In Spack, we have a patch to sqlite's shell.c that applies the undef trick that I use in the

Re: [sqlite] Macro expansion of B0 on Linux PPC system

On 19/12/2017 13:55, Richard Hipp wrote: On 12/18/17, Lee, Greg wrote: I am still seeing the problem on Power 8 and others report the problem persists on Power 9. Please see the spack github issue. I also attached a configure/make output if that helps. So, what you are

Re: [sqlite] [EXTERNAL] How to detect cycles in a hierarchical table?

The most useles answer would be: Yes; run a recursive CTE query and if it does not terminate, then there are loops ;) -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Shane Dev Gesendet: Mittwoch, 20. Dezember 2017 22:32

Re: [sqlite] How to detect cycles in a hierarchical table?

Hi David, Yes, parent and child are integers. I hadn't thought of building a string path, very clever. After a little testing, I have not found a case where the queries fail. On 20 December 2017 at 23:18, David Raymond wrote: > Well, if your parent and child are going

Re: [sqlite] Kind of function out of common table expression

Yannick Duchêne wrote: >I wonder is there is a way with SQLite3, to reuse a often used and >moderately long common table expression without the need to copy/paste >it in every query text it is used in. If you do not need dynamic parameters, use a view. Otherwise, you have to write out the

Re: [sqlite] What happens if an in memory database runs out of memory

Just seen this Each temporary table and index is given its own page cache which can store a maximum number of database pages determined by the SQLITE_DEFAULT_TEMP_CACHE_SIZE compile-time parameter. (The default value is 500 pages.) The maximum number of database pages in the page cache is the

Re: [sqlite] [EXTERNAL] performance impact of index creation order

You are not showing the definition of data. Some table constraints (e.g. UNIQUE or PRIMARY KEY) are implemented via an index, so creating a superflous index that duplicates an automatically created index only serves to waste space in the file and time to maintain. It is possible that creating

Re: [sqlite] [EXTERNAL] performance impact of index creation order

Hick Gunter Sent: Wednesday, December 20, 2017 10:51 AM >You are not showing the definition of data. Some table constraints (e.g. >UNIQUE or PRIMARY KEY) are implemented via an index, so creating a superflous >index that duplicates an automatically created index only serves to waste >space in

[sqlite] create index implies analyze?

If I am finished making inserts into a table, does the act of creating an index also populate the index? That is, do I need to separately run 'analyze'? My impression was that there was no need to call analyze unless rows had been inserted after index creation.

[sqlite] performance impact of index creation order

Assuming that table 'data' is completely constructed, does index creation order have any performance ramifications? For example, would it be reasonable to assume that the order of these two statements has no performance impact? (all fields are integer in this case) create unique index

Re: [sqlite] SELECT query #first run# is very slow VOL2

On 19 Dec 2017, at 8:37pm, zakari wrote: > pasting some logs, Im declaring again this happening only the first time, > afterwards working without problem. > 2017-12-17 15:16:23 - execute > 2017-12-17 15:17:20 - executed > > 2017-12-19 14:53:35 - execute > 2017-12-19

Re: [sqlite] [EXTERNAL] create index implies analyze?

CREATE INDEX will populate the index with references to all of the rows in the table. Bulk loads may run considerably faster if no indices are present at load time (not yet created or dropped beforehand), but created right after the data has been inserted. If you run INSERT or UPDATE statements

Re: [sqlite] performance impact of index creation order

On 20 Dec 2017, at 3:38pm, Nelson, Erik - 2 wrote: > Assuming that table 'data' is completely constructed, does index creation > order have any performance ramifications? > > For example, would it be reasonable to assume that the order of these two >

Re: [sqlite] reate index implies analyze?

Great explanation, thanks -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Hick Gunter Sent: Wednesday, December 20, 2017 10:41 AM To: 'SQLite mailing list' Subject: Re: [sqlite] [EXTERNAL]

Re: [sqlite] create index implies analyze?

Analyze has to be run separately. Create Index doesn't gather any stats while it's running and doesn't update any of the sqlite_stat1-4 tables. Create Index is all you need to run for the index to be created, populated, and used. Analyze gives the query planner a more accurate view of how

Re: [sqlite] create index implies analyze?

On 2017/12/20 7:27 PM, Simon Slavin wrote: On 20 Dec 2017, at 5:19pm, David Raymond wrote: I have often thought that a "create analyzed index" statement would be nice addition though to do the two at the same time. Perhaps not as useful as you might think, because

Re: [sqlite] Atomic DELETE index optimisation?

On 2017/12/20 11:01 AM, Dominique Devienne wrote: "extremely efficient" is a bit exaggerated IMHO. More space efficient definitely, see serial types 8 and 8 in [1], so only the record's header section need to be consulted, w/o the need to decode the whole record or decode the varint [2], thus

Re: [sqlite] create index implies analyze?

On 20 Dec 2017, at 5:19pm, David Raymond wrote: > I have often thought that a "create analyzed index" statement would be nice > addition though to do the two at the same time. Perhaps not as useful as you might think, because most people create indexes while their

Re: [sqlite] [EXTERNAL] performance impact of index creation order

Order of those two create index statements isn't going to matter, neither is going to speed up the execution of the other. If it was a database in WAL mode with other people reading from the database while you're making the indexes, then start with the one that would be used more, as people

[sqlite] How to detect cycles in a hierarchical table?

Hello, I have an edges table - sqlite> .sch edges CREATE TABLE edges(parent, child); sqlite> select * from edges; parent child 1 2 1 3 2 4 3 1 4 5 5 2 Here we have two cycles - 1) 1 => 3 => 1 (length 1) 2) 2 => 4 => 5 => 2 (length 3) Cycles cause

[sqlite] Kind of function out of common table expression

Hi there, I wonder is there is a way with SQLite3, to reuse a often used and moderately long common table expression without the need to copy/paste it in every query text it is used in. I know there is a way to add a function to SQLite connexions using DB APIs, like that of Python. Still, as

Re: [sqlite] Kind of function out of common table expression

Yannick Duchêne Sent: Wednesday, December 20, 2017 5:23 PM >I wonder is there is a way with SQLite3, to reuse a often used and >moderately long common table expression without the need to copy/paste it in >every query text it is used in. I use a C preprocessor for this and pass the queries

Re: [sqlite] How to detect cycles in a hierarchical table?

Well, if your parent and child are going to be integers, then you can do some magic with strings. (This is with the assumption that an edge can't go from and to the same node) Here's something to get the non-looping paths: with recursive x (parent, path, child) as ( select parent, cast(parent

Re: [sqlite] Macro expansion of B0 on Linux PPC system

On 5/3/17, Lee, Greg wrote: > When I try to build sqlite 3.18.0 on a Linux PPC system, I get a > compile-time error. Can you please try again using the latest trunk version of SQLite and let me know if you are still encountering problems. You can download a tarball of the

Re: [sqlite] Macro expansion of B0 on Linux PPC system

The tarball you pointed me to appears to build OK on Linux PPC. Thanks. From: drhsql...@gmail.com on behalf of Richard Hipp Sent: Wednesday, December 20, 2017 3:49:45 PM To: SQLite mailing list Cc: Lee, Greg Subject: Re:

Re: [sqlite] Kind of function out of common table expression

Indeed, a preprocessor may be an option, since all the queries are stored in text files (which are loaded by an application or directly used with copy/paste). If that's better or not than a function added per‑connection, it’s a matter of taste. I will have to decide. -- Sent from:

Re: [sqlite] create index implies analyze?

On 12/20/17, R Smith wrote: > I've never known Analyze to consume significant time ANALYZE runs in O(N) steps, where N is the number of rows in the table. It does a single scan through each index being analyzed, from beginning to end. CREATE INDEX, on the other hand,