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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
