On Thu, Jan 28, 2010 at 10:26:45AM -0500, Tim Romano scratched on the wall:
> Thanks for this clarification.
>
> Wouldn't SQLite simply rewrite my IN-list query, transparently, as an
> equijoin against a transient table that has been populated with the
> values in the IN-list? I don't understand why the IN-list should have
> to be avoided.
It might do that when using the sub-SELECT or table syntax. I'm not
sure. But I know it does not do that when you provide an explicit
list of test expressions. For example, this:
t IN ( e1, e2, e3 ,... )
it is simply transformed into a sequence of equality tests, similar to:
t == e1 OR t == e2 OR t == e3 ...
except that "t" is only evaluated once.
SQLite is actually pretty fast at this for a reasonable size chain of
expressions, but I won't want to expand that out too far.
Overall, I wouldn't say IN is to be avoided. It is just my personal
opinion that the intention of the IN operator is for small to
moderate size datasets, especially when using an explicit value list.
If you're dealing with more than a dozen or so, an equi-JOIN is more
along the lines of what you're trying to do from a high level
perspective. I also have a serious personal dislike of any solution
that involves manually building SQL statements in code. It is
required from time to time since SQL just isn't that flexible, but
I also view them as an indicator that things are starting to get ugly.
Both concerns are more of a "code elegance" type thing, however, so we
can argue about it all day long without anyone being right or wrong.
At the end of the day you need something that works correctly, provides
the required performance, is easy to maintain, and that you can get out
the door in a reasonable amount of time.
My biggest suggestion is to try a few different approaches and see.
Throw a few larger datasets and your problem and see if it does what
you need it to do, what you're most comfortable maintaining, and what
will get the job done.
-j
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"Our opponent is an alien starship packed with atomic bombs. We have
a protractor." "I'll go home and see if I can scrounge up a ruler
and a piece of string." --from Anathem by Neal Stephenson
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
