Re: [sqlite] Equivalent syntax in sqlite

If you change from an auto increment to a GUID/UUID you can simply use 'REPLACE INTO' and you don't have to worry about the select, because you'll already know the ID. http://www.sqlitetutorial.net/sqlite-replace-statement/ On Wed, Apr 26, 2017 at 4:05 PM, Joseph L. Casale

Re: [sqlite] Equivalent syntax in sqlite

From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of David Raymond Sent: Wednesday, April 26, 2017 3:00 PM To: SQLite mailing list Subject: Re: [sqlite] Equivalent syntax in sqlite > With the comment that the insert or

Re: [sqlite] SQLite memory calculator (was Multiple sessions, page sizes and cache)

On 26 Apr 2017, at 10:00pm, Kim Gräsman wrote: > Specifically, I wonder if 4MiB for the general-purpose heap is maybe > entirely unreasonable? Is there a way to forecast how much memory will > be necessary for transactions and query processing, or does that > depend

[sqlite] SQLite memory calculator (was Multiple sessions, page sizes and cache)

On Fri, Apr 21, 2017 at 5:18 PM, Kim Gräsman wrote: > Hi all, > > On Sun, Jan 15, 2017 at 5:50 PM, Richard Hipp wrote: >> On 1/15/17, Kim Gräsman wrote: >>> >>> 1) If I configure a global SQLite heap with SQLITE_CONFIG_HEAP, won't

Re: [sqlite] Equivalent syntax in sqlite

With the comment that the insert or ignore method there will only work if there's an explicit unique constraint on your given criteria. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin Sent: Wednesday, April 26, 2017

Re: [sqlite] Equivalent syntax in sqlite

On 26 Apr 2017, at 9:42pm, Joseph L. Casale wrote: > Whats the trick with SQLites working set to format a single statement with > parameters > where if a row exists for a given criteria, returns its Id, otherwise insert > and return the > last_insert_rowid()? It

[sqlite] Equivalent syntax in sqlite

Whats the trick with SQLites working set to format a single statement with parameters where if a row exists for a given criteria, returns its Id, otherwise insert and return the last_insert_rowid()? For example: CREATE TABLE Foo ( Id INTEGER PRIMARY KEY NOT NULL, ColA TEXTNOT

Re: [sqlite] -shm grows with large transaction

On Wed, Apr 26, 2017 at 5:58 PM, Dominique Devienne wrote: > On Wed, Apr 26, 2017 at 5:34 PM, Kim Gräsman wrote: > >> Great, that means the numbers add up. This is a monster transaction >> updating 5M rows, and page size is 512 bytes, so I think we

Re: [sqlite] -shm grows with large transaction

On Wed, Apr 26, 2017 at 5:34 PM, Kim Gräsman wrote: > Den 26 apr. 2017 3:45 em skrev "Richard Hipp" : > > > On 4/26/17, Richard Hipp wrote: > > > That would imply you are changing about 5 million pages. > > Great, that means the

Re: [sqlite] Increasing performance of query

Also, I have question about the cache_size pragma. If I run the query (this is regarding the count query), without first running ANALYZE on the database, I can see that the times are affected by the cache_size pragma as follows: cache_size = 0 timing = 3.7 s cache_size = -2000 (2000 kb) timing =

Re: [sqlite] -shm grows with large transaction

Den 26 apr. 2017 3:45 em skrev "Richard Hipp" : > On 4/26/17, Richard Hipp wrote: > > That would imply you are changing about a > > half million pages of your database inside a single transaction. > > Correction: About 5 million pages. Missed a zero. (Time

Re: [sqlite] -shm grows with large transaction

On 4/26/17, Richard Hipp wrote: > That would imply you are changing about a > half million pages of your database inside a single transaction. Correction: About 5 million pages. Missed a zero. (Time for coffee, I guess) -- D. Richard Hipp d...@sqlite.org

Re: [sqlite] -shm grows with large transaction

On 4/26/17, Kim Gräsman wrote: > > But for some reason, the WAL-index (-shm) file also grows to about > 40MiB in size. From the docs, I've got the impression that it would > typically stay at around 32KiB. Does this seem normal? The -shm file is an in-memory hash table,

[sqlite] -shm grows with large transaction

Hi again, I've been experimenting with limiting memory usage in our SQLite-based app. Ran into an unrelated oddity that I thought I'd ask about: We're running a couple of massive upgrade steps on over 5 million quite large (70+ columns) rows. There are two unrelated steps; 1) DROP