There's an extra word in the first paragraph of Section 4 of that document, by the way:
" The error logger callback has also proven useful in catching errors occasional errors that the application misses..." On September 11, 2017 11:22:50 AM EDT, Dan Kennedy <danielk1...@gmail.com> wrote: >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 -- Sent from my Android device with K-9 Mail. Please excuse my brevity. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
