Re: Navigation question

From: dawn <dawnwolthuis_at_gmail.com>
Date: 19 Feb 2007 14:47:18 -0800
Message-ID: <1171925238.559620.165900_at_m58g2000cwm.googlegroups.com>


On Feb 19, 2:29 pm, "Kevin Kirkpatrick" <kvnkrkpt..._at_gmail.com> wrote:
> On Feb 16, 9:43 am, "dawn" <dawnwolth..._at_gmail.com> wrote:
<snip>
> > This might be an example of removing navigation, but it also sounds
> > like a) possibly removing unnecessary added queries/combining queries
> > and b) using higher level tools so that any navigation is not
> > specified by the developer. I do like reporting tools, and I don't
> > think of coding in "navigation" when I use them.
>
> YES! I did use a "higher level tool". The "higher level tool" I used
> to avoid specifying the navigation is called a RDBMS. Even if I
> hadn't had a reporting tool, I still would have used *one* query, and
> I still would not be "navigating from this table to that table".
>
> > > Our reports are compiled into report executables -
> > > the old executable was 130k, the new one was 10k. The old report was
> > > running in about 1.5 hours at night; but had run for upwards of 4
> > > hours during peak business hours (when the OLTP applications are
> > > hammering away at the indexes). My version ran in 40 minutes on our
> > > development box, and in production it ran 25 minutes at night and 35
> > > minutes during the day.
>
> > Great!
>
> Even better - we have the freedom to tell the optimizer "feel free to
> use concurrency resources to speed this up". At this time, it's a low-
> priority report and these resources are limitted, so we haven't taken
> this step. However, testing showed that enabling parallelism for the
> report will reduce runtimes to as little as 5 minutes, no coding
> changes needed. Such a reduction could ony be approached (and then,
> only with massive code changes) with a "navigational" version of the
> program.
>
> It's all part of the beauty of telling the database "what I want" and
> leaving the "how to get it" up to the optimizer.

I do agree that set processing, letting the optimizer choose, etc is all good. I'm not to the point where I disrespect "navigation," however. Perhaps I do not understand what is meant by "navigating a database" when it is treated as a bad thing. In particular, I like to have the DBMS retrieve data from multiple tables using navigation specifications (links). I hear that this is a bad thing, but I don't fully comprehend why.

> > > Out of curiosity, I reviewed my base query's execution plan: it used
> > > only one index on one table; all other tables were full-scanned and
> > > hash-joined. Of course, having taken a declarative approach, I didn't
> > > really need to see what indexes were used or what order the tables'
> > > data were read (since I never even considered the problem as a logical
> > > "navigational" problem;
>
> > and, for the record, in case it wasn't clear, this is not where
> > "navigation" kicks in for me either. I think sets, sets, sets when
> > using reporting tools.
>
> Try thinking "sets, sets, sets" when writing applications. Don't
> think of reports as a "special class" of applications - think of them
> as part of a continuum of applications, all of which may need
> different information at different times.

I understand your point (but will still think of read/write as different from read-only for a number of good reasons, I think.)

> A single prinicple applies
> across this continuum: use queries that retrieve all of the data your
> application needs and only the data your application needs.

That's fine. I have a screen requiring values for a single companyid, companyName, emailAddresses, classifiers, orderid & orderPrice for all orders for this customer. How many SQL queries do you submit to retrieve this data? I execute one query (not standard SQL) to get it as a single "row" to correspond to this single screen, but the DBMS navigates. The query is something like

select companyid, companyName, emailAddresses, classifiers, Order.orderid, Order.orderPrice from Customer where tin='xyz';

The DBMS has a spec telling it how to "get from" Customer to Order.

> > > I didn't really care what table was visited
> > > first, second, or third; any more than a java developer would care
> > > about the byte-code generated from compiling a java program). It was,
> > > however, interesting to note that the optimizer actually "went to" the
> > > company table last - exactly opposite of the intuitve navigational
> > > decision made by the original developer.
>
> > but oddly similar to my intuition. Did the reporting tool make
> > multiple SQL queries?
>
> Of course not. The tool just ran the query I specified, which
> returned all of the raw data needed by the report.
>
> The fact that you had an intution is telling. On what did you base
> your intuition? (this is not a rhetorical question)

If I were to guess, I would suggest it has to do with the difference in thinking that I did in SQL compared to UniQuery (Pick). With UniQuery, users and developers think in terms of "going from" a 1 to a Many relation, while in SQL (for the same DBMS) performance was best if joining from the Many to the 1 (at the time).

> > > What approach to these types of reports or application designs is
> > > generally better - a navigational approach "go to t1 and get company,
> > > now go to t2 and get department, then use those to go to t3 for
> > > budgets", or declarative: "join tables t2, t3, t1 and return company,
> > > department, and budgets"?
>
> > This is ill-thought-out, off the top of my head --
> > If there is no reporting tool, so that you cannot simply declare the
> > entire report and are writing the database access statements for the
> > DBMS to execute, then you have both the issue of retrieving the data
> > and presenting it. I admit I haven't written a sophisticated report
> > with break points, header and footer data, multi-line addresses that
> > are stacked, but do not take up more space than necessary, allowing
> > other data next to these lines, etc without a reporting tool in quite
> > some time, but something in the mix needs to take the result sets from
> > the queries and turn it into the presentation. If the query language
> > is incapable of doing that, then, hmmm, I'm thinking the reporting
> > tool does iterations under the covers, so I might have to as well?
> > That isn't the same as navigation, but within the iterations, one
> > might navigate, perhaps?
>
> Yes, the reporting tool automates the coding of iterations and
> aggregation over the result set of the query. Lacking a reporting
> tool, you'd need your application to do the same;

Yes.

> but think of it this
> way: once I declaratively specified exactly the information I needed,
> the code for processing the results was so straightforward that it
> could be *generated*.

Incredibly complex code can be generated for all sorts of purposes. I don't think that tells us anything.

> Without the reporting tool, but still starting with a single
> declarative SQL query, my program would have been mind-numbingly easy
> to write.

What if your report needed to show the same data I indicated for the screen, but for all customers? What one SQL query would you use?

> The reporting tool does *not* divvy up the query and run it
> piecemal to "go to this table" then "go to that table" (that's the
> "hard", "error-prone", "performance bug waiting to happen" approach) -
> applications should not do this either.

Good, agreed.

> > Thanks, Kevin. I was using an example of interactive data maintenance
> > applications, so thinking about read-only is helpful. But it does
> > seem like the reporting tools have removed any need/desire for "me" to
> > think about navigation for reports. cheers! --dawn- Hide quoted text -
>
> Again, don't think about navigation for *any* applications; reports,
> interactive, or otherwise. Think sets, always. State what you need
> when you need it, and move on.

I am not new to such opinions. I'm reasonably fluent in "sets" but also comfortable with navigation and think that there is room for both. I have not yet figured out what precisely is wrong with navigation, in general, if anything. Thanks. --dawn Received on Mon Feb 19 2007 - 23:47:18 CET

Original text of this message