ghway to Hell 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
>
ite.org] 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
>
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 (
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. The
e query 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 effici
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 having
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 cray
o:sqlite-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, Se
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 then
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 ROWID
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 to
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 INT
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 out
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 SE
14 matches
Mail list logo
