On Sat, Nov 2, 2013 at 4:59 AM, Olaf Schmidt <n...@vbrichclient.com> wrote:
> Am 31.10.2013 14:09, schrieb Dominique Devienne: > > [Userdefined functions in conjunction with fast Exists-checks > in "Userland" - vs. SQLites built-in indexing in case of In (List)] > > [...] With a function based approach, you are *always* full-scanning the >> whole >> "real" table, no matter the cardinality of the InList operand [...] >> >> Of course that's speculation on my part, versus your timed >> experimentation, >> so could well be that I'm wrong. And I'll need to look into this >> eventually. >> >> > You're not wrong - although the UDF-timings in my previous post are > correct - it is true that they will remain (relatively) constant > Thanks for confirming with hard-experimentations my guesswork. > [...] what came as a surprise > was the kind of "inverse-lookup" the SQLite-optimizer apparently > performs, when an index exists on the "real" table which provides > the Column-value to compare against the "In"-list. > Yes, SQLite probes several plans for most index combinations, and the order you write your SQL statement and its joins matters little if at all AFAIK. (disclaimer: non-authoritative guesswork again). > With only an index on the real table (on the compare-value-column): > 0.4msec (100 items in the compare-list) > 1.9msec (1000 items in the compare-list) > 26msec (10000 items in the compare-list) > 116msec (40000 items in the compare-list) > This is consistent with findings we've seen in our own software, where rewriting queries to use joins instead of custom SQL functions sped up some queries considerably. It's very tempting for a C/C++ developer with little SQL experience to write a C++ UDF as a WHERE filtering predicate, STL-style (most of our tables are virtual tables over native C++ containers, so often you can write such UDFs in our app), but as the tables they you end up full-scanning get large, it doesn't scale well against an index-path plan if there's one possible. > Maybe all these values provide an insight also for others - in what > "regions" the SQLite-In-List functionality (roughly) operates > timing-wise (under somewhat idealized conditions). Once again, thanks for the detailed and thorough analysis Olaf. Cheers, --DD _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
