Re: Navigation question

On Feb 14, 4:47 pm, "dawn" <dawnwolth..._at_gmail.com> wrote:
> On Feb 14, 6:15 pm, "Marshall" <marshall.spi..._at_gmail.com> wrote:
>
> > On Feb 14, 1:59 pm, "dawn" <dawnwolth..._at_gmail.com> wrote:
>
> > > On Feb 14, 3:32 pm, "Marshall" <marshall.spi..._at_gmail.com> wrote:
> > > I have not seen any
> > > applications that execute exactly one or even only (unordered) sets of
> > > SQL statements,
>
> > Any two SQL queries where query B is parameterized by
> > data retrieved by query A can be rewritten into a single
> > query.
>
> Yes, but that isn't how software is written, is it?

It's how *I* write software, but yes, people do write software using suboptimal techniques. That's why I have to carry a fish around--because people write queries inside loops in client code that could have been a single query.

> Have you ever
> seen an entire application that does not navigate the database in this
> way at some point (and for good reason)?

We have a bug tracking application that sucks and has few reports and those reports run slow. I wrote a reporting application that had a copy of the bug database and all the queries were written in the one-query-per-report style. Most pages generated in ~100 ms. The longest report you could generate was "graph the history of open bugs over the entire company from the beginning of time" which included a self-join of the table containing all the *changes* to all the bugs ever made: it took 9 s.

This was using an OLTP schema, by the way; no precomputation was used at all. Eventually the report generator was turned into a full-fledged bug tracking package, and about half of the company projects have converted over to using the new one.

> > The rewritten single query will (absent pathological
> > behavior in the query optimizer) be at least as fast as
> > the sum of the two original queries, and possibly faster.
>
> I'm not sure I believe that, but let's say it is true, then what.
> What does the application code then do with this cartesian cross-
> product of data?

I don't think I've ever written a cartesian product into client code. I've done a few at the command line, though.

> Include just one "property" of an entity where the
> property has multiple values, such as e-mail addresses. We want the
> name of the company an the list of contact e-mail addresses (let's say
> there are 5 for our example), plus the postal address of the HQ, in
> some "header" block on a data entry page and then a list of orders and
> dates below it (let's say there are 100). So we retrieve a view that
> includes one row per e-mail address per order, for 600 rows we need to
> now deconstruct for the screen. Have you ever seen this done?

I've seen pages that look like that, but not that use cartesian product.
(Although I suppose you could do it that way, and I can even think of ways to implement that effeciently. But I wouldn't recommend it.)

But none of what you describe produces any queries that have any navigation, or any order dependencies, or that are parameterized by information that has to be gotten from other, distinct queries.

Let's even do what you describe the hard way, and say that we have some unique description of the company, TIN say, that is *not* a key of the other tables, nor the primary key of the company table.

select name from companies where tin = XXX;

select c.emailaddress from contacts c, company co   where co.tin = XXX and
  co.companyid = c.companyid;

select a.* from addresses a, company co
  where co.tin = XXX and
  a.companyid = co.companyid and
  a.type = 'HQ';

select o.orderid, o.date from orders o, company co   where co.tin = XXX and
  o.companyid = co.companyid;

Note how declarative the last line is: "give me all the order numbers and dates of all the orders for the company with tin XXX".

> I'm
> interested in whether there are really any significant applications
> that do no navigation -- do you really think there are?

I can't think of any navigation that I did anywhere in the aforementioned bug tracking software.

Marshall Received on Thu Feb 15 2007 - 02:24:08 CET