On Sun, 09 Nov 2014 00:45:16 +0900 Tristan Van Berkom <tris...@upstairslabs.com> wrote:
> 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. Oh, I think you'll agree SQL has explicit syntax. I think what you mean by "vague" is "nondeterministic with respect to the physical data structures and algorithms". About that you're right; it's considered a feature. :-) The idea is that the engine interpreting your SQL might not be absolutely as fast as the most optimal solution for your hardware and data at any one point in time. But it will be nearly so, much more adaptable as hardware and data change, and thereby not trouble your application with issues outside its problem domain. And of course there are other advantages besides, as you know. Partly it's a matter of trust. You trust the OS to schedule your job fairly, to supply virtual memory, to deliver your TCP packet. You trust SQLite to deliver on its ACID contract, and to produce logically correct result from queries. Why not trust it to find the shortest path to your data? > 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, A good SQL rule of thumb: if you can think of a way, so can the DBMS. "... no opportunity to make a good guess" is not true. In some sense, SQLite has had 10 years to make a good guess, and often does. A nested select need not be materialized as a "table", opaque or otherwise. It can be converted, or "flattened" under some circumstances. SQLite's query planner isn't the most sophisticated; no one suggests otherwise. It does not follow that every JOIN will outperform every EXISTS or vice versa. > 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). I don't know what you're mean by indexes that "don't seem to be declarative". > 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. If I may be so bold, beware of your assumptions. The fastest plan with 100 rows may perform very poorly with 100 million rows. Be careful what you optimize for. :-) My fans on this list (both of them) will be surprised, though, that I partly agree with you. In terms of technology, there's very little middle ground between fopen(3) and SQL's "I give query you give data" contract. SQL experts, no matter the platform, spend a fair amount of time coercing the system into using an efficient query plan. They cannot say "apply criteria X to index Y and join to table T"; they must work by indirection, creating indexes and using "query hints" (or, sometimes, other query formations) until the planner does the "right thing", however defined. I sometimes wish for a system that let me express the query algebraically and the order of operation explicitly, but afaik no such system exists except partially and grudgingly within SQL. --jkl _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
