Doh. I meant the linked document on the error log. Silly me. <http://sqlite.org/errlog.html>
On September 11, 2017 9:41:39 PM EDT, "J. King" <jk...@jkingweb.ca> wrote: >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 -- 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
