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 WHERE IN (...) queries posted similar times here with WHERE IN
(...) being the slightly faster (similar to the above findings), but BOTH
were a good 10 - 15% faster than on the ROW-ID tables, so WITHOUT ROWID
tables seem to have a definite advantage here (it is currently unclear to me
why this is).

A troubling test is the JOIN on WITHOUT ROWID tables - it took several
orders of magnitude longer than any other test in the entire experiment.
In your first test you were ordering by PK, now you're not, and you
don't have an [covering] index on the columns you're ordering by, so,
yeah, "orders of magnitude" slower is to be expected.  You're comparing
apples and oranges. /// etc ....

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 am in no way ordering by PK in this set of tests, anywhere. I am not using 1 column, I am using 151 columns for both tests. The only thing that changes between the two scripts are the words "WITHOUT ROWID" being added or removed, NOTHING else. The problem also happens on the un-ordered tests. The ordering is of no consequence.

To be clear, there are two tests, both do not order by PK, both do not have covering indexes, both have 151 columns and lots of data... the only difference between them is the words "WITHOUT ROWID" at the end of the test tables. One query runs in ~50 milliseconds, the other runs for minutes. The results are exactly the same.

You can test this by taking the script I provided, run it, it will run for longer than 2 minutes (depending on your machine speed) because of the JOIN queries taking over a minute each, then remove the words "WITHOUT ROWID" from the two test tables and run it again. The entire script will finish in underĀ  3 seconds with the JOIN queries weighing in at circa 50ms each.

That cannot possibly be an expected circumstance and it isn't an apples-oranges thing.

sqlite-users mailing list

Reply via email to