Ryan, my dataset isn't that big. 11K records. Your solution is noticeable faster 996 ms vs 13126 ms. Interesting!
gert Op za 23 jun. 2018 om 18:09 schreef R Smith <ryansmit...@gmail.com>: > > On 2018/06/23 2:47 PM, Gert Van Assche wrote: > > Barry, that's even easier indeed. And it works perfect! > > Thanks for sharing this. > > I very much like the method which Barry suggested for the simplicity, > but have avoided it since I thought it would be significantly slower on > a large data set, however, SQLite is known for some pretty neat > optimizations (especially where the row_id is concerned) and I'm now > interested to know if indeed such optimizations feature in this case. I > have a few such implemented queries that might benefit from changing over. > > May I ask, if you do have a rather large dataset, and perhaps don't mind > the time, would you care to compare the two methods and let us know if > there is any discernible difference in speed? Also one small important > item - how many F1 items are there typically per unique F2 item in > total? Does it grow over time? > > Also, you do not need hashes, for either of the two queries - the > queries should work regardless of integer or Unicode text used, perhaps > the comparison might need a specific collation? Surrounding both > references with a TRIM() function might also help. Essentially, if F1 of > record 3 in Table T is '*&^%@#*&^@#' then "... FROM T AS A, T AS B > WHERE A.F1 = B.F1" must resolve correctly. If it doesn't, it's a bug. > The <, <=, >, >= might all produce some Unicode weirdness upon > inadequate collations. > > > If time doesn't allow, then don't spend effort on this, it's simply a > curiosity. :) > > Thanks! > Ryan > > > _______________________________________________ > 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
