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 still roll with the idea that the WITHOUT ROWID tables with 151
columns and lots of data actually performed FASTER than the same ROWID
tables for the other queries but slower with the JOIN... but that much
slower?
I mean we are not talking a "little" slower, we are talking 50ms vs.
~70,000ms, that's a difference of a whopping 3 orders of magnitude and
change. And we are not talking huge tables, another query that simply
walks the tables can do so several million times in the same time the
JOIN query does.
Put another way, I can create 100 new tables and populate them each with
the rows from one test table, then delete the rows from each new table
that doesn't satisfy an EXISTS() check in the other test table
(essentially mimicking the JOIN query) and then output each full new
table, 100 of them in turn, and then DROP them all. SQlite can do ALL of
that in a fraction of the time that the normal JOIN query (between those
same two test tables) takes to complete.
We are talking a formula 1 car suddenly going at max speed of 1
mile-per-weekend, barely keeping up with a semi-athletic snail, and
people suggest checking the fuel octane rating. I'm saying there is
something wrong under the hood.
Cheers,
Ryan
On 2017/09/10 11:28 AM, Clemens Ladisch wrote:
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
<http://www.sqlite.org/withoutrowid.html> says:
| WITHOUT ROWID tables will work correctly ... for tables with a single
| INTEGER PRIMARY KEY. However, ordinary rowid tables will run faster in
| that case.
and:
| WITHOUT ROWID tables work best when individual rows are not too large.
So this is pretty much the documented worst case for WITHOUT ROWID tables.
If the query is executed by making a copy of all the table data into
a temporary B-tree (index), which is what INTERSECT and probably IN(...)
are doing, then the WITHOUT-ROWID-ness of the table does not matter.
Regards,
Clemens
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users