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] Odd question

On Mon Dec 18, 2017 at 04:48:27PM +, Nelson, Erik - 2 wrote: > Nomad Sent: Sunday, December 17, 2017 4:11 PM > >On Sat Dec 16, 2017 at 07:53:22PM +, Nelson, Erik - 2 wrote: > > >> Select 1 as value from (insert into table1 values(a, b, c)) I've > >> tried a number of options but haven't

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

On May 3, 2017, at 4:48 PM, Lee, Greg wrote: > > u64 B0, B1, B2, B3, B4; That’s the first actual use of u64 in that module. Are you sure your platform’s 64-bit integer types are being detected properly? Maybe post the output of a ./configure run, or the resulting config.h

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

On 19 December 2017 at 11:23, Simon Slavin wrote: > > > On 19 Dec 2017, at 2:55am, 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

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

On 19 Dec 2017, at 2:55am, 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

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

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. -Greg From: drhsql...@gmail.com on behalf of

Re: [sqlite] Atomic DELETE index optimisation?

On 2017/12/18 12:59 AM, Dinu wrote: Thanks Richard, as mentioned earlier, any structure change is unacceptable due to concurrent reader clients. Within a transaction it matters none to those concurrent readers - that's the entire point of the WAL + Transaction. As long as you do all the

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

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 saying, then, is that "B0" is a reserved

Re: [sqlite] Odd question

Keith Medcalf Sent: Monday, December 18, 2017 2:31 PM ...snipped a lot... >>Right, all of the statements are step'd regardless of the result of >>sqlite3_column_count(). SQLITE_DONE is returned from the first >>step() for insert queries. >>In pseudocode, it's >>prepare("insert...") //okay

Re: [sqlite] Odd question

On Monday, 18 December, 2017 11:35 >Keith Medcalf Sent: Monday, December 18, 2017 1:07 PM >To: SQLite mailing list >Subject: Re: [sqlite] Odd question >>>I investigated a further while exploring some of the list >>>suggestions. The app halts with an error

Re: [sqlite] Odd question

Keith Medcalf Sent: Monday, December 18, 2017 1:07 PM To: SQLite mailing list Subject: Re: [sqlite] Odd question >>I investigated a further while exploring some of the list >>suggestions. The app halts with an error unless >>sqlite3_column_count() > 0.

Re: [sqlite] Odd question

Kees Nuyt Sent: Monday, December 18, 2017 12:51 PM On Sat, 16 Dec 2017 21:43:44 +, "Nelson, Erik - 2" wrote: >> I'm using an application that I can't change. >> I can give it multiple queries to run but the >> application assumes that each query will

Re: [sqlite] Odd question

Keith Medcalf Sent: Sunday, December 17, 2017 4:30 PM >How do you know the insert is not working? Have you verified that the data is >not being inserted or are you simply relying on a popup provided by the >application? If the later, you should be aware that the application can >pop-up

Re: [sqlite] Odd question

On Sat, 16 Dec 2017 21:43:44 +, "Nelson, Erik - 2" wrote: > I'm using an application that I can't change. > I can give it multiple queries to run but the > application assumes that each query will produce > at least one row and causes an error if that's >

Re: [sqlite] Odd question

Peter Da Silva Sent: Monday, December 18, 2017 12:24 PM >What I don’t understand is this app that’s making SQLite calls, so it’s using >the SQLite library, and it’s expecting a result from updates and inserts? Yes, exactly. The app designers assumed that rows are returned from every

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

>To get clarity, are you saying the 33% speedup is the gain of the >non-Indexed vs. Indexed table, or due to setting that cache size on the >already in-memory DB? (The latter would be worrying). Ryan, It seemed unaffected by cache size. The slight gain in speed (~0.2 secs) for the 2 million

Re: [sqlite] Odd question

What I don’t understand is this app that’s making SQLite calls, so it’s using the SQLite library, and it’s expecting a result from updates and inserts? That seems like a bug or design flaw in the application. Possibly it’s looking for the number of rows effected result and not finding it for

Re: [sqlite] Odd question

Nomad Sent: Sunday, December 17, 2017 4:11 PM >On Sat Dec 16, 2017 at 07:53:22PM +, Nelson, Erik - 2 wrote: >> For unfortunate reasons, I need a query that does an insert and also >> returns at least one row... for example, something along the lines of >> Select 1 as value from (insert into

Re: [sqlite] Odd question

Nelson, Erik - 2 Sent: Saturday, December 16, 2017 5:27 PM >Petern wrote on Saturday, December 16, 2017 4:53 PM >>Re: Nelson "odd". This will make the desired (?) side effect happen: >>.load eval.so >>SELECT coalesce(eval('INSERT INTO table1 VALUES(a, b, c)'), 1) AS value; >>If INSERT

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

A big cache on an in-memory DB is a bit like insisting to sit inside a row-boat while on a big ship. It has zero effect in helping you float better - it's probably slightly worse even, considering the cache computation cycles could have been avoided. To get clarity, are you saying the 33%

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

Sorry cache_size should be -8,000,000. It didn't make any difference to the results or conclusion though. -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

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

On 18 Dec 2017, at 2:48pm, curmudgeon wrote: > You're definitely right about me wasting my time Simon. I loaded my entire > database (1 GB) into memory and set cache_size = -8192 for an 8 GB cache > size (using win64). I then ran my test (inserting the results of a

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

You're definitely right about me wasting my time Simon. I loaded my entire database (1 GB) into memory and set cache_size = -8192 for an 8 GB cache size (using win64). I then ran my test (inserting the results of a query, returning 111 million bigints, into a non-indexed single column table) and

Re: [sqlite] [EXTERNAL] Re: Atomic DELETE index optimisation?

On Mon, 18 Dec 2017 07:21:50 -0700 (MST) Dinu escribió: > Hick Gunter wrote > > If you are running an SQLite version 3.11.0 or newer, only pages that are > > changed for the first time in the transaction are copied to the WAL file, > > thus extending it. > > > > While

Re: [sqlite] [EXTERNAL] Re: Atomic DELETE index optimisation?

Hick Gunter wrote > If you are running an SQLite version 3.11.0 or newer, only pages that are > changed for the first time in the transaction are copied to the WAL file, > thus extending it. > > While progressing the transaction, the rate of "newly changed" to "changed > again" pages will shift

Re: [sqlite] [EXTERNAL] Re: Atomic DELETE index optimisation?

I guess you might be runing into the effect described here http://sqlite.org/wal.html : " Very large write transactions. A checkpoint can only complete when no other transactions are running, which means the WAL file cannot be reset in the middle of a write transaction. So a large change to a

Re: [sqlite] Atomic DELETE index optimisation?

Simon Slavin-3 wrote > DELETE FROM MyTable We have tried it and DELETE FROM table (without any WHERE) behaves exactly the same! I reiterate there are no FKs or triggers defined. So this is no fix... -- Sent from: http://sqlite.1065341.n5.nabble.com/

[sqlite] Notification mechanism when truncate a mapped file

I am trying to make some change to the source code of sqlite as I want to map a file using unixMapfile(). But I guess there will be a SIGBUS crash when one process truncate the file and do not notify other processes who want to access the file later. So I am wondering if there is any appropriate

Re: [sqlite] Atomic DELETE index optimisation?

Simon Slavin-3 wrote > Once you’ve bust the cache things slow down. I do realize that. However, not illinearly. Once I bust the cache, throughput should drop X times and stay there. Here, the speed decreases with the progress. Let me put it this way: say the cache size was 0. I would expect the

Re: [sqlite] Atomic DELETE index optimisation?

On 18 Dec 2017, at 12:28pm, Dinu wrote: > Actually I realized that the DB page size is 1K. Is this bad? I tried to run > the pragma query with 1M pages, to amount to the same 1G; there seems to be > a dramatic improvement in throughput at the beginning of the query, The

Re: [sqlite] Atomic DELETE index optimisation?

On 18 Dec 2017, at 10:03am, Dinu wrote: > I honestly don't see how in any DB system the client process would not crash > if the index it's running a curson on were to be removed. SQLite doesn’t run cursors. There are no cursor commands in the SQLite API. SQLite does

Re: [sqlite] Atomic DELETE index optimisation?

Richard Hipp-3 wrote > DROP, CREATE, and ALTER are transactional in SQLite, just like DELETE, > INSERT, and UPDATE. This has always been the case, and always shall > be. Thanks! But still, isn't this incompatible with any lower-than-DB-level transaction locking mechanism? I.E. should you ever

Re: [sqlite] Atomic DELETE index optimisation?

On 12/18/17, Dinu wrote: > Rowan Worth-2 wrote >> The entire point of SQL transactions is to implement this magic! > > I beg to differ; you proposed: >> (1) BEGIN TRANSACTION >> > (2) DROP all indexes from the table > This is by no means valid SQL semantics; in all RDBBMS

Re: [sqlite] [EXTERNAL] Re: Atomic DELETE index optimisation?

As you are running in WAL mode, readers will still be seeing the state of the data BEFORE you started the delete transaction. This holds true also for readers that run while your delete transaction is running. Nobody is pulling out the rug from under any transaction at all. Even though you are

Re: [sqlite] Atomic DELETE index optimisation?

Rowan Worth-2 wrote > The entire point of SQL transactions is to implement this magic! I beg to differ; you proposed: > (1) BEGIN TRANSACTION > > (2) DROP all indexes from the table This is by no means valid SQL semantics; in all RDBBMS I've worked with, structure changes' relation to a

Re: [sqlite] Atomic DELETE index optimisation?

Keith Medcalf wrote > If you "moved" that memory (or some of it) from Level 2 to Level 1 you > would increase performance tremendously. > > pragma cache_size=262144; -- 1 GB page cache Thanks, I will try that! -- Sent from: http://sqlite.1065341.n5.nabble.com/

Re: [sqlite] [EXTERNAL] Re: Atomic DELETE index optimisation?

Hick Gunter wrote > SQLite does this too Thanks! -- 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] Atomic DELETE index optimisation?

On 18 December 2017 at 18:03, Dinu wrote: > Rowan Worth-2 wrote > > I'm not sure what you're worried about? Dropping and recreating identical > > indices within a transaction won't cause a visible structure change to > > concurrent readers -- that's the point of a

Re: [sqlite] Atomic DELETE index optimisation?

Not sure how relevant it might be, but what page size is the DB set to and what is the average size of a record? Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic

Re: [sqlite] Atomic DELETE index optimisation?

Rowan Worth-2 wrote > I'm not sure what you're worried about? Dropping and recreating identical > indices within a transaction won't cause a visible structure change to > concurrent readers -- that's the point of a transaction. I honestly don't see how in any DB system the client process would

Re: [sqlite] [EXTERNAL] Re: Atomic DELETE index optimisation?

SQLite does this too (I'm not sure about the "sort rowid" bit, but it would seem reasonable); and similarly for an update, it will first SELECT the affected rows in their result form and insert them all later. -Ursprüngliche Nachricht- Von: sqlite-users

Re: [sqlite] [EXTERNAL] Re: Atomic DELETE index optimisation?

If I understand correctly, you are running a single statement equivalent to DELETE FROM WHERE ; Since SQLite can drop a whole table very much faster than deleting ist rows one by one, try: BEGIN; CREATE TABLE temp_data AS SELECT * FROM old_data WHERE ; DROP TABLE old_data; ALTER TABLE