Re: Navigation question

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
> 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.

> 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.

> 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 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;

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. 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.

> 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.

> 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.

[ 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, 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