Re: Navigation question

On Feb 16, 11:52 am, "Tony D" <tonyisyour..._at_netscape.net> wrote:
> On Feb 15, 7:56 pm, "dawn" <dawnwolth..._at_gmail.com> wrote:
>
> > Yes, I'm not sure why I brought that in,
>
> If you're not sure, I'm definitely going to struggle.
>
> > except that I often refer to
> > result sets that are might not be "normalized" relations as views.
>
> Why ? "view" is a fairly well defined term.

I don't "live in" the SQL world these days, but you are right.

> > I
> > realize that an SQL View is a stored query, so my use of it here was
> > not SQL-compliant. Apologies.
>
> Depends on the SQL product. Some produce things called "materialized
> views" which sounds like a contradiction in terms to me.

Yes, agreed.

> > In order to retrieve data, something somewhere moves to where that
> > data is ("are" but I have switched to common use) in order to read it
> > (from a disk sector, for example).
>
> If you want to conceptualise yourself as a disk drive, go for it.

I typically conceptualize myself as a user, then sit in between these two. Both the user and the disk navigate. Navigation is useful when going from one screen to another, one page to another, and maybe even one relation to another. I'm still trying to figure that out.

> > At a higher level of abstraction,
> > conceptually "I" (when coding) am "going to" the node, with values for
> > the attributes about an entity (not caring about hardware, nor about
> > whether the information about my conceptual entity is split up on the
> > disk).
>
> Then, conceptually, you're totally out of whack. Sorry. There are no
> "nodes" to "go to".

I understand that you think that.

> > I realize that this can be conceptualized differently as well,
> > but this is not an uncommon way of thinking about it, even if I am not
> > using the most common terms.
>
> The queries I was comparing were this one of Marshall's; let's call it
> Query M:
>
> > select c.emailaddress from contacts c, company co
> > where co.tin = XXX and
> > co.companyid = c.companyid;
>
> And this proposed alternative from you; let's call it Query D :
>
> > select name, companyid from companies where tin = XXX;
> > mycompany = companyid;
> > select emailaddress from contacts where companyid = mycompany;

You missed Marshall's first query that corresponds to my first query. We each had two queries, which I thought was two round trips for each of us. I think Marshall is telling me that both of his queries could together go to the DBMS at the same time and the DBMS might execute them together in some different order.

> Query M: one query, one round trip to database, one result set.
> Query D: two queries, two round trips to database, two result sets,
> one local variable.
>
> All this talk of "nodes", and "then", and "going to" and so on -
> that's not what's happening. There is no "moving finger" that you
> guide through the database from table A to table B.

Conceptually there can be.

> You are issuing
> multiple independent queries with (hopefully) increasingly defined
> parameters. There is no connection between them, other than some
> shared parameters. Even in an SQL DBMS, you *cannot* "navigate" around
> tables.

I do not typically work in SQL DBMS's. What I do is a non-SQL setbased  query, but where there are no joins at all, but there are "links" (navigation) specified so that data can be retrieved from "elsewhere." This is considered inferior because of the navigation, so I'm trying to understand that. It has been over 5 years now, but I have worked with SQL DBMS's a bit and with SQL against a not-really- typically-SQL DBMS more extensively. I have never passed multiple SQL statements together (via ODBC, which is what I was doing at that time) with the DBMS not executing them sequentially as written, but putting them together to figure out the plan. That is one piece I was definitely missing.

> > We had already established that it was not a single query. So, I am
> > making two queries and Marshall is making two queries. If we compare
> > our two second queries, how does my query differ in nature from
> > Marshalls?
>
> Hopefully, that is now obvious.

If my new understanding is correct that multiple SQL statements can be passed to most/all SQL-DBMS's which will then assess them altogether rather than executing them sequentially, then I'm starting to catch on. But I think you missed that we both had two SQL statements.

> > In both cases we are telling the DBMS what to retrieve.
> > In each case we are providing the query with information we have
> > within the application. The only difference I can see is that one
> > will perform better than the other.
>
> > In both cases, there are two SQL statements, each of which leaves it
> > to the DBMS to determine how best to get the response.
>
> Hopefully you're now seeing why this is wrong. You haven't left it to
> the DBMS to work out how to do it; you've told the DBMS how it will be
> done. And thus it shall be done, for ever and ever, or until you
> change the code, anyway.
>
>
>
> > Should we not also tune it by providing the information we have
> > already retrieved? Is there something wrong with that?
>
> "Tune" it ? I'm not seeing how you're tuning anything, other than
> issuing mulitple queries with differing parameters.
>
> > I do understand this issue, trust me. Again, both Marshall and I were
> > providing two SQL statements, so we are not talking about additional
> > queries.
>
> > I am not talking about adding in any iteration. I have never before
> > thought that the question of "navigation" had anything to do with
> > "iteration" but now both you and Marshall have brought this up. I
> > find that fascinating, I really do. How did "navigation" ever get
> > confused with or intermingled with "iteration"? I am not at all
> > speaking up in favor of cursors when asking about navigation. I know
> > the party line on iteration, even if I will admit to writing such.
>
> Where this idea of "navigation" turns up, "iteration" usually follows
> like some deformed lemming seeking out a cliff.

I understand why you think that, but that need not be the case. It is very important to separate these two concepts. You navigate the web and need not do any iterations when doing so, correct?

> [ snippage ]
>
> > I probably should not admit it, but I've presented such material, so,
> > yes, I understand that. It is more specifically the term and concept
> > of navigation, of moving from my house to the movie theater, of going
> > from data about a person to data about this person's children, etc.
> > that is of interest to me with this question. It sounds like I might
> > need a term that does not imply iteration to others. Surely when
> > navigating in a car it does not bring iteration to mind, so this is
> > confusing me.
>
> Probably because you *can't* "navigate" around an SQL database in the
> way you would navigate in a car. There are no predetermined routes
> that you must follow; you can join tables using whichever columns you
> like,

You can navigate a DBMS (at least the ones I use and I think this is also true of SQL) by moving from a foreign key value to other data. If there is no specification for the route (no specification for a value to be a foreign key to some other table), then you would not have the routes, but if you do have that information, you can find your way around the data. Did any of that make sense? I realize I still have more to learn to be permitted to discuss this with people who know tons more than I about SQL, but I do have knowledge of models other than relational that work very well and I am trying to figure out why they are not considered as good from a theory perspective.

Thanks. --dawn

> and leave the DBMS to worry about how it will be done. If you
> really must persist with a car driving metaphor, consider the DBMS as
> your taxi driver and the query optimizer his GPS. You ask him to take
> you to the store, but you don't sit behind him telling him when to
> take a left.
>
> - Tony
Received on Fri Feb 16 2007 - 21:08:13 CET