On Sun, 2014-11-09 at 15:04 +0200, RSmith wrote:
> On 2014/11/09 14:11, Tristan Van Berkom wrote:
> >> 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.
> > Indeed this is a large misconception on my part, on the queries which
> > I *have* profiled, it did turn out that JOINs were more effective than
> > solutions which involve nesting select statements.
>
> This seems like the appropriate place to interject with a Statistics /
> Sample-size comment, but I think the point is obvious.
>
> > Anyway, I appreciate the input and will try to accept that I should not
> > be in control of how the query is run - I was under the impression that
> > SQL engines can perform better when given more context about how the
> > query should run (i.e. being more explicit with JOINs), but I do agree
> > that, at least ideally, the planner should be able to make a better
> > guess as to how to plot a query with a more relaxed/vague statement,
> > than with a more explicit one.
>
> Woah... this is not at all what James tried to say and it might just be that
> your choice of words is unfortunate and you did mean it
> correctly, but just to be sure, allow me to elaborate somewhat:
>
> The query should in no way be "Vague" or "Relaxed", it should be precise,
> succint, exact and fully correct making the intended
> result impossible to misinterpret.
[...]
No you're right, it's a poor choice of words on my part, I don't mean
vague in terms of what exact set of rows I want to extract, but rather
vague in terms of 'what will happen when I issue this query'.
Which I understand, and do agree, is something that ideally the query
author should not have to trouble himself with.
This year in particular I've been faced with my first queries of modest
complexity, see for example this (temporary) paste:
http://www.fpaste.org/148918/41545194/
This is the beginnings of a query which will try to fetch an available
employee for a given 'task', ordered eventually by their relevance,
first set 'is it their job ?' second set 'do they have the licenses and
formations to actually perform the task, even if it's not their job' ?
and so on and so forth.
I've found that when trying to bend my brain around these problems of
modest complexity, they are only understandable if I visualize them in
terms of 'sets of rows' and how they join to each other.
You'll note that I have nested the UNIONs in a select specifically
because there are common JOINs I want to perform on the collective
result, thus reducing the redundancy of the query (here I am again,
trying to out-optimize the query planner).
Anyway, I think you've all collectively succeeded in pointing out the
error of my ways ;-)
I will try to formulate more straight forward queries which only ask
for the succinct set of data that I want to pull, rather than the
queries I've been writing which are probably more constrictive to
the planner for possibly no justifiable reason.
Cheers,
-Tristan
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
