On 09/10/2017 08:30 PM, R Smith wrote:
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?
The difference seems to be that, currently, SQLite never creates
automatic indexes on WITHOUT ROWID tables.
http://sqlite.org/optoverview.html#autoindex
I don't think there is a good reason that it cannot do so. It just
doesn't yet.
Dan.
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
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
