Re: [sqlite] JOIN vs. INTERSECT vs. WHERE IN (...) - speed and efficiency differences

Doh. I meant the linked document on the error log. Silly me. On September 11, 2017 9:41:39 PM EDT, "J. King" wrote: >There's an extra word in the first paragraph of Section 4 of that >document, by the way: > >" The error logger callback has

Re: [sqlite] JOIN vs. INTERSECT vs. WHERE IN (...) - speed and efficiency differences

There's an extra word in the first paragraph of Section 4 of that document, by the way: " The error logger callback has also proven useful in catching errors occasional errors that the application misses..." On September 11, 2017 11:22:50 AM EDT, Dan Kennedy wrote: >On

Re: [sqlite] JOIN vs. INTERSECT vs. WHERE IN (...) - speed and efficiency differences

On 09/10/2017 08:30 PM, R Smith wrote: Well yes but the documentation suggests that one could expect a slight degradation. The words "works best with" does not seem to imbue an idea of "give WITHOUT ROWID tables a wide berth when your tables are more than few columns wide", and I don't think

Re: [sqlite] JOIN vs. INTERSECT vs. WHERE IN (...) - speed and efficiency differences

Well yes but the documentation suggests that one could expect a slight degradation. The words "works best with" does not seem to imbue an idea of "give WITHOUT ROWID tables a wide berth when your tables are more than few columns wide", and I don't think the Devs intended that either. I can

Re: [sqlite] JOIN vs. INTERSECT vs. WHERE IN (...) - speed and efficiency differences

R Smith wrote: > I am using 151 columns for both tests. The only thing that changes > between the two scripts are the words "WITHOUT ROWID" being added says: | WITHOUT ROWID tables will work correctly ... for tables with a single | INTEGER PRIMARY KEY.

Re: [sqlite] JOIN vs. INTERSECT vs. WHERE IN (...) - speed and efficiency differences

On Sat, Sep 09, 2017 at 11:26:35PM +0200, R Smith wrote: > I think you are missing something or my explanation was not clear. > When I say "first test" I mean of THIS test suite, not the previous set from > 3 days ago. I meant the opposite. ___

Re: [sqlite] JOIN vs. INTERSECT vs. WHERE IN (...) - speed and efficiency differences

On 2017/09/09 9:20 PM, Nico Williams wrote: On Sat, Sep 09, 2017 at 05:56:03PM +0200, R Smith wrote: *Findings* pertaining to JOIN, INTERSECT and WHERE IN (...) type queries on tables *WITHOUT Row_ids*: (This is the full test posted below because it is the one that matters most) INTERSECT AND

Re: [sqlite] JOIN vs. INTERSECT vs. WHERE IN (...) - speed and efficiency differences

On Sat, Sep 09, 2017 at 05:56:03PM +0200, R Smith wrote: > *Findings* pertaining to JOIN, INTERSECT and WHERE IN (...) type queries on > tables *WITHOUT Row_ids*: > (This is the full test posted below because it is the one that matters most) > INTERSECT AND WHERE IN (...) queries posted similar

[sqlite] JOIN vs. INTERSECT vs. WHERE IN (...) - speed and efficiency differences

Full tests completed with findings ranging from less interesting to exposing a rather significant inefficiency in SQLite. I won't post all the tests because that would take far too much space, in stead I will simply discuss the experiment and findings and post the test script so that anyone

Re: [sqlite] JOIN vs. INTERSECT speed and efficiency

ell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of David Raymond >Sent: Thursday, 7 September, 2017 10:31 >To: SQLite mailing list >Subjec

Re: [sqlite] JOIN vs. INTERSECT speed and efficiency

rg] On Behalf Of R Smith Sent: Thursday, September 07, 2017 3:51 PM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] JOIN vs. INTERSECT speed and efficiency On 2017/09/07 6:31 PM, David Raymond wrote: > Although it may not translate as well to the more complex examples, would you >

Re: [sqlite] JOIN vs. INTERSECT speed and efficiency

On Thu, Sep 07, 2017 at 09:51:07PM +0200, R Smith wrote: > INTERSECT will happily match however many columns you desire (and specify), > there is no need to match full records or single keys specifically. But the two queries on either side of the set operator must have the same number of columns

Re: [sqlite] JOIN vs. INTERSECT speed and efficiency

On 2017/09/07 6:31 PM, David Raymond wrote: Although it may not translate as well to the more complex examples, would you also consider adding the IN operator to your tests? I found for example that "select v from t1 where v in t2;" did even better than the join or the intersect. Will do.

Re: [sqlite] JOIN vs. INTERSECT speed and efficiency

ry more complex/ugly... correct? -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of R Smith Sent: Thursday, September 07, 2017 8:06 AM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] JOIN vs. INTERSECT speed and efficiency

Re: [sqlite] JOIN vs. INTERSECT speed and efficiency

On Wed, Sep 06, 2017 at 07:43:07PM -0600, Keith Medcalf wrote: > Try the same test using 147 columns in each table. > > 1 column is rather trivial. Even a kindergarten kid could do it in no > time using crayons and the wall. > > [...] > > In other words except in very trivial cases (like

Re: [sqlite] JOIN vs. INTERSECT speed and efficiency

On 2017/09/07 3:43 AM, Keith Medcalf wrote: Try the same test using 147 columns in each table. Exactly the plan for this weekend :) 1 column is rather trivial. Even a kindergarten kid could do it in no time using crayons and the wall. So? That is non-sequitur, I am sure given enough

Re: [sqlite] JOIN vs. INTERSECT speed and efficiency

ite-users- >boun...@mailinglists.sqlite.org] On Behalf Of R Smith >Sent: Wednesday, 6 September, 2017 14:58 >To: sqlite-users@mailinglists.sqlite.org >Subject: Re: [sqlite] JOIN vs. INTERSECT speed and efficiency > >On 2017/09/06 8:26 PM, Nico Williams wrote: >> On Wed, Sep 06,

Re: [sqlite] JOIN vs. INTERSECT speed and efficiency

On Wed, Sep 06, 2017 at 11:54:35PM +0200, R Smith wrote: > It's still remarkable that in both tests 5 and 6 I've used the very same PK > setup, yet Test 6 was significantly faster with the added ORDER BY clause. > In tests 1 through 4 I did not use a PK at all, just plain INT data field, > but

Re: [sqlite] JOIN vs. INTERSECT speed and efficiency

On 2017/09/06 11:17 PM, Nico Williams wrote: If you'll redo this I'd urge you to use WITHOUT ROWIDS. First, that's almost always the right thing to do anyways. Second, it won't perform worse but likely will perform better. Third, write performance definitely should improve with WITHOUT

Re: [sqlite] JOIN vs. INTERSECT speed and efficiency

On Wed, Sep 06, 2017 at 10:57:41PM +0200, R Smith wrote: > On 2017/09/06 8:26 PM, Nico Williams wrote: > >On Wed, Sep 06, 2017 at 08:05:57PM +0200, R Smith wrote: > >>-- Another interesting thing to note: The INTERSECT test produces ORDERED > >>-- output, which suggests that an ORDER-BY addition

Re: [sqlite] JOIN vs. INTERSECT speed and efficiency

On 2017/09/06 8:26 PM, Nico Williams wrote: On Wed, Sep 06, 2017 at 08:05:57PM +0200, R Smith wrote: -- Another interesting thing to note: The INTERSECT test produces ORDERED -- output, which suggests that an ORDER-BY addition to the query would -- favour the INTERSECT method. Nothing about

Re: [sqlite] JOIN vs. INTERSECT speed and efficiency

On Wed, Sep 06, 2017 at 08:05:57PM +0200, R Smith wrote: > -- Another interesting thing to note: The INTERSECT test produces ORDERED > -- output, which suggests that an ORDER-BY addition to the query would > -- favour the INTERSECT method. Nothing about INTERSECT requires it to produce ordered

[sqlite] JOIN vs. INTERSECT speed and efficiency

Hi all, For those interested, after a recent thread from a poster called Joe asking about the most efficient way to find values that coincide from two separate tables, a response from Clemens Ladisch and a further elaboration from myself suggested the following: SELECT v FROM t1 INTERSECT

Re: [sqlite] JOIN vs IN

On Fri, Nov 11, 2011 at 8:06 PM, Petite Abeille wrote: >> No, "exists" in this case will change query plan significantly and >> performance can degrade drastically as a result. > > Why would that be? How would you rewrite the query using exists? The only thing I have in

Re: [sqlite] JOIN vs IN

Fabian wrote: Suppose I have two tables, and I want to have look for a value in the first table, but display the columns from the second table. The most obvious way would be joining them on rowid. But I don't need to SELECT any columns from the first table, and it's a FTS4 table (which always

Re: [sqlite] JOIN vs IN

On Nov 12, 2011, at 1:58 AM, Pavel Ivanov wrote: > No, "exists" in this case will change query plan significantly and > performance can degrade drastically as a result. Why would that be? ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] JOIN vs IN

On Fri, Nov 11, 2011 at 11:58 AM, Petite Abeille wrote: >> It returns the same results, but it doesn't seem much faster. Is there any >> performance difference to be expected from using IN instead of JOIN, or >> does SQLite internally rewrite JOIN queries to something

[sqlite] JOIN vs IN

Suppose I have two tables, and I want to have look for a value in the first table, but display the columns from the second table. The most obvious way would be joining them on rowid. But I don't need to SELECT any columns from the first table, and it's a FTS4 table (which always joins a bit slower