There's nothing vague about select statements, they're logical formulas involving the data in your database and as exact as any other programming language, albeit in a very different domain. Relational databases are based on first order predicate logic and have operations that are are rigorously defined.
You're obviously thinking about the limitations of the database's optimizer and the opaque way it does its work, but just as a real time or embedded programmer may have qualms about (the uncertainty of) the code produced by a compiler and prefer hand rolled assembler, you want more control and certainty over what the database does. The problem in both cases is that the programmer assumes they can do better than the machine, which is highly questionable given the current state of the art in both cases. You can give the optimiser tips about how to go about its work and that can help improve efficiency, but you are better off trusting the optimiser rather than trying to fight it. If there is an actual problem with the efficiency of a well written select, it's almost always rooted in the design of the database schema, especially to much or too little de-normalization. On Sat, Nov 8, 2014 at 7:45 AM, Tristan Van Berkom <tris...@upstairslabs.com > wrote: > On Sat, 2014-11-08 at 10:23 -0500, James K. Lowden wrote: > > On Sat, 08 Nov 2014 22:55:46 +0900 > > Tristan Van Berkom <tris...@upstairslabs.com> wrote: > > > > > So I would have to say, the "right way to do it" is the most efficient > > > way, the one which provides SQLite with the best indications of how > > > to plot an efficient query plan. > > > > Keith is suggesting that the right way to do it is neither "any way that > > works" nor necessarily "whatever is fastest" but "the clearest > > formulation of the query". Clarity has the salutary property of being > > most likely to be correct (because understood by the human) and stands > > a better than fair chance of being executed efficiently (because it > > translates easily to a good query plan). > > > > Most of time -- not every time, but most of the time -- indexes > > and table design matter much more to efficient execution than query > > syntax. When a clearly expressed query is not executed efficiently in > > the presence of useful indexes, and especially when a slightly different > > one does, that's usually considered a defect of the query planner. > > I see what you're saying, as I mentioned in the initial email I do > consider myself to be a relative newbie, and the majority of work > I've been doing with SQL (until this year) has been working on > optimizing existing schemas/queries for embedded use. > > While I do understand SQL as a functional language, most functional > programming I've done still has rather explicit syntax/rules, so I get > particularly uncomfortable with writing vague statements, such as > JOIN tableA, tableB WHERE ... without being exactly specific on the > heart/nature of the join which is happening. > > Also what I've found in my limited experience is that nesting SELECT > statements, at least in SQLite, completely throws off the planner, > as in it has no opportunity to make a good guess and has to deal > with the result set of a nested SELECT as an opaque table, which > is generally not an indexed table (or perhaps it is, but I wouldnt > know because those indexes don't seem to be declarative in any way). > > So indeed, I am not comfortable with 'leaving it up to chance', > and if there is a way to get higher specificity, I try to achieve that. > > In any case, I do have queries which work well at this point, but > posted this question to the list in the hope I could find the right > specificity for the given query - it's not a very big deal, I'll > probably just stick with what works and try to fix it in the places > where profiling reveals that I'm doing something wrong. > > Cheers, > -Tristan > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
