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.


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.
| 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.

sqlite-users mailing list

sqlite-users mailing list

Reply via email to