Re: Navigation question
Date: Mon, 19 Feb 2007 12:59:32 GMT
Message-ID: <UahCh.4396$lo1.1029_at_trndny05>
"dawn" <dawnwolthuis_at_gmail.com> wrote in message
news:1171853959.171262.207070_at_h3g2000cwc.googlegroups.com...
> On Feb 18, 2:27 pm, "Walt" <wami..._at_verizon.net> wrote:
> > "dawn" <dawnwolth..._at_gmail.com> wrote in message
> >
> > news:1171655147.355068.254690_at_p10g2000cwp.googlegroups.com...
> >
> >
> >
> > > On Feb 16, 11:16 am, "Marshall" <marshall.spi..._at_gmail.com> wrote:
> > > > On Feb 16, 8:34 am, "dawn" <dawnwolth..._at_gmail.com> wrote:
> >
> > > > > On Feb 16, 9:48 am, "Marshall" <marshall.spi..._at_gmail.com> wrote:
> >
> > > > > They
> > > > > have the same number of queries. How many times do I have to say
that
> > > > > I am not, not, not, absolutely not suggesting that we do more
round
> > > > > trips or split queries.
> >
> > > > You split a query. You had tin and you needed contacts, and you
> > > > did it with two queries. I did it with one. Navigation splits
queries.
> > > > The performance of navigational approaches relative to declarative
> > > > approaches ranges from as-good-as to unboundedly worse. Yes,
> > > > you kept the total query count the same. However you introduced
> > > > an explicit ordering between the two queries that wasn't there
> > > > before, and that isn't free.
> >
> > > > Here's your approach:
> >
> > > > select name, companyid from companies where tin = XXX;
> > > > mycompany = companyid;
> > > > select emailaddress from contacts where companyid = mycompany;
> >
> > > > vs.
> >
> > > > select name from companies where tin = XXX;
> > > > select emailaddress from contacts natural join companies where tin =
> > > > XXX;
> >
> > > > Here's a possible execution scenario:
> >
> > > > no index on tin
> > > > no index on contacts.companyid
> > > > system has contacts fully loaded in cache
> >
> > > > your approach:
> >
> > > > select name, companyid from companies where tin = XXX;
> >
> > > > 1) requires a full scan of companies because no index on tin
> > > > 2) loads companies from disk, pushing contacts out of cache
> > > > 3) full scan of companies
> >
> > > > select emailaddress from contacts where companyid = mycompany;
> >
> > > > 3) loads contacts from disk
> > > > 3) full scan of contacts
> >
> > > > my approach:
> >
> > > > I send these two queries to the system:
> >
> > > > q1: select name from companies where tin = XXX;
> > > > q2: select emailaddress from contacts natural join companies where
tin
> > > > = XXX;
> >
> > > OK, now we are getting somewhere. My knowledge must be dated and/or
> > > with the wrong tools. I was using ODBC against a DBMS where SQL was
> > > not its only language. You are saying that you can pass in two
> > > queries together and the tools would not execute them sequentially,
> > > but figure them out together. That is definitely a piece I was
> > > missing. I only played around with jdbc and mysql a little bit a few
> > > years back, but perhaps if I read the current jdbc and mysql
> > > information I would understand this. Are you passing both SQL
> > > statements in the same jdbc statement?
> >
> > I thought you lead several large projects where Oracle was the
environment.
>
> I've been directly involved in a couple with Oracle, one was OLTP read/
> write and the other BI (using OLAP tools, for example). If I said
> "several" were specifically Oracle (did I?), I exaggerated, which I
> would not have done on purpose. Additionally, Oracle was only one
> target environment so the software was written in such a way as to
> permit persistence in both UniData (Pick) and Oracle. I was not a
> programmer (but had employees who were), but did do some R&D and some
> training ("those who can't do, teach" perhaps), in addition to high
> level design. I have done a small amount of programming with Oracle
> in an R&D effort that was not production stuff (maybe I clumped that
> in there to get the several?)
>
> My intro to Oracle was around 1987, when a colleague did a feasibility
> study with it. I was not a programmer on that project either. I
> first touched a product that declared itself to be a relational
> database when I "played with" RBase, likely late 80's. Most of my
> heads-down programming years ('77-88) were spent with IMS (with COBOL/
> CICS) and indexed sequential files on Pr1mes and IBM iron. I became a
> manager at the end of '88 and since that time have had various
> projects with hands-on work, but most of my hands-on effort for the
> past almost two decades has been in the off-hours or on Friday
> afternoons (which I consider my "hands-on" time) just to keep at least
> a little bit current. I am not currently doing any project including
> any primarily SQL-DBMS, although I work with a s/w product that has
> Oracle as one of its target DBMS's and occasionally I am involved in
> such, but not as a developer.
>
> > The above seems to deny that.
> > Am I misreading you?
>
> Yes, or I misspoke. Feel free to point me to a statement you think is
> incorrect. The above is as accurate as my memory is right now. --dawn
>
Once you get enough "anectodal" evidence to contradict your long held illusions, you may no longer need the "empirical evidence" you have been demanding in order to see the merits of the tools you have been deriding. But I don't think that's going to happen. Received on Mon Feb 19 2007 - 13:59:32 CET