Re: [sqlite] Query plan gone haywire lays waste to my library's performance

On Friday, 28 April, 2017 15:55, Simon Slavin wrote: > > The only difference is the explicit JOIN statement. I was under the > > impression that using this, vs. the way I wrote it, is a matter of taste > > that doesn’t affect the execution of the query. > SQLite computes

Re: [sqlite] Unary + isn't disabling use of index

You can always list the tables in the order you want them visited replacing the "," operator with CROSS JOIN to force the optimizer to piss off. SELECT sequence, revs.doc_id, docid, revid, deleted FROM docs CROSS JOIN revs WHERE sequence > ? AND current=1 AND revs.doc_id

Re: [sqlite] Query plan gone haywire lays waste to my library's performance

The syntactic sugar of the ON clause does nothing for equijoins. Only for outer joins. Please RTFM: See https://sqlite.org/queryplanner.html And https://sqlite.org/optoverview.html And https://sqlite.org/queryplanner-ng.html -- ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı >

Re: [sqlite] Unary + isn't disabling use of index

> On Apr 28, 2017, at 2:43 PM, Igor Tandetnik wrote: > > It seems the index is used to implement "ORDER BY revs.doc_id" part. Try > "ORDER BY +revs.doc_id" Yes! That fixed it, and the other query I was posting about too. Thanks! —Jens

Re: [sqlite] Unary + isn't disabling use of index

On 4/28/17, Igor Tandetnik wrote: > On 4/28/2017 5:30 PM, Jens Alfke wrote: >> Another query using the wrong plan, even though I’ve added a unary “+” to >> prevent use of indexes on ‘current’: > > It seems the index is used to implement "ORDER BY revs.doc_id" part. Try >

Re: [sqlite] Query plan gone haywire lays waste to my library's performance

On 4/28/2017 4:37 PM, Jens Alfke wrote: CREATE TABLE docs (doc_id INTEGER PRIMARY KEY, docid TEXT UNIQUE NOT NULL, expiry_timestamp INTEGER); CREATE INDEX docs_docid ON docs(docid); For the record, this index is redundant. There's already an automatically created index on docs(docid), thanks

Re: [sqlite] Query plan gone haywire lays waste to my library's performance

On 28 Apr 2017, at 10:23pm, Jens Alfke wrote: > On Apr 28, 2017, at 2:00 PM, Simon Slavin wrote: > >> What indexes do you have on these two tables ? I can’t recommend one >> without knowing which columns belong to which tables. > > I showed them at

Re: [sqlite] Unary + isn't disabling use of index

On 4/28/2017 5:30 PM, Jens Alfke wrote: Another query using the wrong plan, even though I’ve added a unary “+” to prevent use of indexes on ‘current’: SELECT sequence, revs.doc_id, docid, revid, deleted FROM revs JOIN docs ON docs.doc_id = revs.doc_id WHERE sequence > ?

[sqlite] Unary + isn't disabling use of index

Another query using the wrong plan, even though I’ve added a unary “+” to prevent use of indexes on ‘current’: SELECT sequence, revs.doc_id, docid, revid, deleted FROM revs JOIN docs ON docs.doc_id = revs.doc_id WHERE sequence > ? AND +current=1 ORDER BY

Re: [sqlite] Query plan gone haywire lays waste to my library's performance

> On Apr 28, 2017, at 2:00 PM, Simon Slavin wrote: > > What indexes do you have on these two tables ? I can’t recommend one without > knowing which columns belong to which tables. I showed them at the end of my first message. > First query rewritten for clarity: > […]

Re: [sqlite] Query plan gone haywire lays waste to my library's performance

On 28 Apr 2017, at 9:49pm, Jens Alfke wrote: > SELECT revs.doc_id, sequence, docid, revid, json, deleted FROM revs, docs > WHERE sequence>? AND current!=0 AND deleted=0 AND revs.doc_id = docs.doc_id > ORDER BY revs.doc_id, deleted, revid DESC > 0 0 0 SCAN TABLE revs USING

Re: [sqlite] Query plan gone haywire lays waste to my library's performance

Sorry for the quick follow-up, but I’ve just run a quick test that does an EXPLAIN QUERY PLAN for every statement that gets compiled, in an empty database, and confirmed that the query plan comes out wrong right from the start: SELECT revs.doc_id, sequence, docid, revid, json, deleted FROM

[sqlite] Query plan gone haywire lays waste to my library's performance

I’ve got an urgent-to-me issue wherein a customer says that our library has slowed down by several orders of magnitude in iOS 10.3 compared to iOS 9. I ran a test case they provided and found that some queries that should be fast are taking a very long time (~500ms) and generating huge numbers

Re: [sqlite] Semantics regarding command instances and queries in the C# client

Joseph L. Casale wrote: > Each row from the CSV required several tables with relationships to be > populated. One could > certainly abstract this out into an api, but that can have impacts on > performance for large > batch processing if you are creating parameters for every insert rather than

Re: [sqlite] Semantics regarding command instances and queries in the C# client

From: sqlite-users on behalf of Clemens Ladisch Sent: Friday, April 28, 2017 2:51 AM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] Semantics regarding command instances and queries in the C# client   > Show

[sqlite] sqlite3_open_v2("",... schema name?

Dear, http://sqlite.org/c3ref/open.html says: "If the filename is an empty string, then a private, temporary on-disk database will be created. This private database will be automatically deleted as soon as the database connection is closed." On such a successfully opened database,

Re: [sqlite] Controlling the lifetime of shared-cache, in-memory SQLite databases.

Errr … Clemens has cites. I don’t. Believe him, not me. Sorry about that. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Controlling the lifetime of shared-cache, in-memory SQLite databases.

Randall Smith wrote: > A shared-cache, in-memory database is "persistent" across connections > from the same process that do not overlap in time? No. says: | The database is automatically deleted and memory is reclaimed when the | last

Re: [sqlite] Semantics regarding command instances and queries in the C# client

Joseph L. Casale wrote: > that practice looks a bit ugly. Show some example. Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users