"Ed Prochak" <edprochak@[EMAIL PROTECTED]
> wrote in message
news:4b1507c9-207e-4555-bd27-42dd0e84de9d@[EMAIL PROTECTED]
> On Jun 26, 12:31 pm, "David Cressey" <cresse...@[EMAIL PROTECTED]
> wrote:
> > "Roy Hann" <specia...@[EMAIL PROTECTED]
> wrote in message
> >
> > news:T_adnWNsCqvQDP7V4p2dnAA@[EMAIL PROTECTED]
> >
> >
> >
> > > "Arved Sandstrom" <asandst...@[EMAIL PROTECTED]
> wrote in message
> > >news:VEL8k.493$7%6.472@[EMAIL PROTECTED]
> > > > Not "getting" data is part of a larger problem, which is
forgetting
what
> > > > software is actually for. Most end users couldn't care less about
how
> > > > their solution is implemented, they just want a useable program.
> >
> > > End-users absolutely do not care about anything except how easily
they
can
> > > get through the 9-5. They don't own the business and they don't pay
it's
> > > bills and they have no investment in it's most valuable (and
expensive)
> > > non-tangible asset--namely it's data. The end users' opinions on
the
> > > subject of databases is as relevant as the cows' opinions on
> > cheese-making.
> >
> > > > Useable meaning reliable, not too hard to use, reasonably fast and
so
> > > > forth. Not "getting" data is also accompanied by not "getting"
user
> > > > interfaces, re****ting requirements, do***entation, error recovery
etc.
> >
> > > Not "getting" data means being a fraud and an imposter. No business
buys
> > > database applications just to run applications; they buy them to get
> > > accurate information and for no other reason. Not "getting" the
other
> > > things you list means delay and awkwardness and you'll surely be in
> > trouble
> > > if you can't deliver efficiency and speed, but those come second.
To
> > prove
> > > it, propose to a company director that you can make his slow systems
twice
> > > as fast but they'll produce undetectable corruptions of the data,
and
see
> > if
> > > he goes for it.
> >
> > I can't tell you the number of times programmers have come to me for
help
> > with a query
> > that produces wrong results, and when I try to change their "select"
to
> > "select distinct" to see if maybe that's the problem, they exclaim
"Oh
I
> > never use 'select distinct'! It runs too slow!"
> > I patiently explained to them that I first want to come up with a
query
that
> > is logically correct, then if necessary, come up with one that's
logically
> > equivalent, but runs fast.
>
> When I see
> SELECT DISTINCT
> in anything but an ad hoc query, I know something is wrong with the
> query. Possibly it is joining to a wrong table (to a detail table
> instead of a header/parent table for example) or some filtering
> condition is missed (a column not in the select list needs to be
> used), or some join condition was missed (there is a compound key and
> one component was left out). Even in an ad hoc query I tend to use
> SELECT COUNT(*)
> since that gives me a little bit more information for the same cost as
> DISTINCT (i.e. it reads the same amount of data).
>
> Seeing this post, I'm actually a little disappointed in you, David.
> Your last sentence does show you mainly follow a good approach. It's
> just that DISTINCT should be left to ad hoc queries IMO.
>
> Have a good day.
> Ed
Your comment doesn't agree with my experience. In general, there are two
cir***stances where SELECT DISTINCT is the right approach. Both involve
situations where SELECT generates a bag, but the desired result is a set.
The first is a situation where the database has been misdesigned. If the
database has been frozen for a year or more, and there is a lot of
production software that depends on the existing table design, it may
simply be unfeasable to correct the design in order to make one query
logically easy to write.
The second is a situation where the query requires a subset of the data
that
does not include any candidate keys from the underlying tables. In that
case, the generation of a query requires its own mechanism for eliminating
duplicates.
Perhaps the development of a new program, a long time after the database
is
in production, and not easily altered, has much the same flavor of what
you
have called an "ad hoc query". Using the database to organize data in a
way
that the designer did not contemplate.
|
