Re: Navigation question

"dawn" <dawnwolthuis_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. The above seems to deny that.
Am I misreading you? Received on Sun Feb 18 2007 - 21:27:36 CET