Roy Hann wrote:
Actually, Paul brings up some excellent points. And no, I don't think
he is straining the definition of "programmer". Any database design is
dependent on the data and how it must be accessed (queries). There are
certain rules for normalization, but the higher the normalization level,
the slower the access can be. Therefore there are times when the
normalization rules have to be bent - more often in large databases.
3NF is a pretty good goal to shoot for, but not always possible and
still get good access time.

And even in large projects with dedicated database designers, the
designers always have some programming experience, and work with
programmers to jointly develop the best design for the project.

It's true that a database design will benefit the programmers. However,
at the same time, the programmers must still be able to write efficient
queries to access that data. Database designers aren't going to do that
for them.

I know of a lot of companies who strive towards as much platform
independence as possible. And look at all of the platform independent
open source projects out there.

Platform independence is a good thing. Now I'm not saying everything
has to be platform independent. However, if you design your databases
and queries properly, much of your code can be completely platform
independent.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

Roy Hann wrote:
True, and for maximum efficiency, you may need to tune a few queries if
you switch databases. But in my experience, that is seldom necessary,
and if it is, it is a very minor number of queries which need to be
adjusted.

And you're expecting perfection from any database. That's impossible,
so it is not nearly as realistic as you writing a RDBMS - which,
although difficult, would not be impossible.

And which products are those? I know of none which do all you ask. If
they exist, then why are we even discussing it here? Just use those
products.

Just that SQUARE used ideas gained from COBOL, and COBOL used ideas
gained from Assembler. So by your logic, SQL is descended from
Assembler (or, if you want to go back to the very first computers,
machine code).

Sure, I hope optimizers get better. But it will be many decades (if not
longer) before they can get anywhere near what you're looking for.

In the meantime, good programmers using RDBMS's need to know how to
write optimum SQL statements and help design efficient database structure.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

On Nov 13, 8:34 am, "Roy Hann" <specia... (AT) processed (DOT) almost.meat>
wrote:
99.7% of all statistics are made up. I suspect this comment from the
"Captain" is too.
and Roy joins in with his unsupported claim. Two people said it so it
must be true!

And it is sad that they feel that way. They possibly think programming
in only one language, like java or COBOL, is "A good thing". And many
poeple around the world think Americans are self-centered. How much
moreso are these kinds of programmers.

Wrong.
Optimization is a late addition to SQL. It was originally intended to
be a non-programmer's programming language. The idea was Managers
should be able to write a query to get a report with what they need
without bothering a programmer. needless-to-say a good number of
managers do not understand any select statement beyond a two table
join. So writing queries fell back into the laps of programmers.

(The fact that SQL had a nice translation to relational logic which
was being developed about the same time only helped it along.)
We are still waiting for the DWIMNWIS computer. I think it will be a
long wait.

(DWIMNWIS = Do What I Mean, Not What I Say)
To the extent that we have people that are Expert SQL programmers and
unable to program in anything else, I would agree that this is a bad
thing. Otherwise, I think your optimizer arguement would apply to
about any programming language.

Ed

On Nov 13, 9:32 am, "Roy Hann" <specia... (AT) processed (DOT) almost.meat>
wrote:
Yes it does. look at your paragraph above (the one with "explicitly"
in it)
Optimization was NOT part of the original goal or purpose of SQL.

Ah good you do know some history, even though you may misremember some
of it.

As we increased the power of hardware, we also make heavier demands.
The same query on 100,000 rows on a 1MHz CPU might very well finish
faster than on 10,000,000 rows on a 1GHz CPU. Optimizer improvements
can only take us so far.

Yes, better products should be rewarded. Now define better so that
everyone agrees and I'll nominate you for the Nobel Peace prize.


[]
I have heard of such a computer. Deep Thought reportedly computed the
answer to Life, the Universe, and Everything. The answer, as you may
have heard, was 42. It only took it 6million years to compute it!
8^)

Now what was the question?
Ed

On Nov 14, 4:39 am, Captain Paralytic <paul_laut... (AT) yahoo (DOT) com> wrote:
Excellent point!

On Nov 14, 11:36 am, "Roy Hann" <specia... (AT) processed (DOT) almost.meat>
wrote:
[]
Have ou considered anywhere in your evaluations of optimizers exectly
what the problem involves? There are problems that for a given volume
of data cannot be solved in any reasonable amount of time. The best
execution plan for a query involves traversing tables in a way that is
exactly equivalent to the travelling salesman problem. It is
essentially impossible to solve with out using a brute force approach.
So you can dream of optimizers getting better, but they are going
against a fundamental limit. There is a point they cannot do any
better.

Keep dreaming.
Yes, but they are stuck against a limit. For any given query, they can
only get incrementally better until they reach that limit and then not
better at all. It's kind of like the Speed of light in physics. Unless
you have some tacheons in your pocket, you can't get there any faster.

Ed

"Ed Prochak" <edprochak (AT) gmail (DOT) com> wrote

Maybe you haven't followed this conversation from the start so I will repeat
this for you. I am only saying some *existing* optimizers are better than
others. A reasonable person would prefer the one that is best overall,
meaning the one that most frequently comes up with the best plan for a
getting a required set of data, so that changes to correct SQL to get *that
product's* best plan are needed as infrequently as possible. Note that I am
talking about the "best plan" in the context of that particular DBMS--not
some fantastic plan that particular engine can't even execute.

What am I dreaming about? Optimizers have improved continuously since they
were invented. Are you saying that you think optimization technology has
maxed out and that all implementations have achieved the same ultimate
state-of-the-art and there are now no differences between them?

I am not aware of any reason to suppose we've got much further than a brisk
walking pace yet. But I know nothing about the limits of what is possible
in principle. I am talking about the limits of what has already been
achieved.

Roy

"Ed Prochak" <edprochak (AT) gmail (DOT) com> wrote

Er, actually Captain P didn't write that, I did. And I didn't offer it as a
statistic, I said I "expect" that is the case. That pretty clearly flags it
as just my opinion. If I had any statistics you can be sure I'd assert it
as an undisputed fact, loud and clear.

Nope still just me, and again I clearly indicated it is just a suspicion, so
I really can't be criticised for not supporting it with evidence. On the
other hand it is not a very far-fetched suspicion. Feel free to cite
evidence to contradict me or we're just as unreliable as each other.

You miss the point. SQL just describes the required result. The obvious
way to express the query can work perfectly well in one product and very
badly in another. Since the programmer has done nothing wrong, and has not
coded a bad algorithm, they basically have to do a Monte Carlo search for
another way of expressing the result they need that just happens to tip
their optimizer into doing the job efficiently. There is no general
principle they could follow, they just have to know, or get lucky. Most
programmers who are new to SQL find that bewildering, frustrating, and
unhelpful. It sure isn't desirable behaviour, is it?

About what? I already cited Chamberlin and Boyce's own words. Let me do it
again: "The work on [SQL]...is consistent with the trend to declarative
problem specification."

I guess that depends when "late" starts. It goes back at least 20 years in
some products, and maybe more. Long enough that is doesn't matter in the
context of this conversation.

Partially correct, yes, but that was literally the second explicit
objective. The first stated objective (and here I quote Chamberlin and
Boyce again) was: "...to lower software costs among professional
programmers."

Actually that is not true. SQL/SEQUEL/SQUARE/etc. were at best influenced
by relational logic. Even at the time there were bitter rows within IBM
over the poor fidelity to relational algebra and to the relational model,
and it has only got worse. But we digress.

That isn't the problem. That is exactly what the problem is not. The
problem is that a reasonable person (such as the OP of this thread) can
write a query that says *exactly* what he means, and it still isn't good
enough. He has to discover some equivalent but different formulation,
somehow, by no means anyone can explain except by cataloging "tricks that
have worked in the past and might continue to work in the future".

No, SQL is unlike any other comparably widely used language because it is
declarative.

Roy

On Wed, 14 Nov 2007 01:39:45 -0800, Captain Paralytic wrote:
There is, however, a difference between REALLY helping the optimizer and
just thinking you're helping, and the latter is often part and parcel
with the whole concept of "premature optimization" on the part of the
programmer.

--
86. I will make sure that my doomsday device is up to code and properly
grounded.
--Peter Anspach's list of things to do as an Evil Overlord

"ZeldorBlat" <zeldorblat (AT) gmail (DOT) com> wrote

I believe that Roy Hann's responses in this topic are quite correct with
regard to what one may reasonably expect from a good DBMS. Without
detracting one iota from Roy's response, I want to explore what some query
optimizers would do with either the original query or the suggestion you
made. I haven't checked your suggestion for logical correctness, but it
looks good on a casual read. I wonder if it helped the original poster?

Anyway, here's a strategy that might be proposed to perform the original
query:

Do a table scan to pick up all the rows of the table. This involves
accessing 10,000 rows.
Within each iteration of the loop that scans the table, perform the
subquery.
Perform the subquery by doing a table scan of the table to determine the max
value for the tag_id currently seen in the outer loop. This involves
accessing 10,000 rows 10,000 times, or 100,000,000 row accesses.

Even if 99 out of 100 row acesses are performed with no disk i/o, this is
still going to be an intolerably slow strategy.

Next, let's try this strategy:

Do a table scan to pick up all the rows of the table. This involves
accessing 10,000 rows.
Within each iteration of the loop that scans the table, perform the
subquery.

This time, use an index to restrict the search to only the rows with the
corresponding tag_id.
This results in reducing the number of row accesses to 20,000, but adds
10,000 index lookups to the process. It should be on the order of 100 to
1000 times faster than the first strategy. It, of coruse, depends on the
existence of an index on tag_id. A really clever optimizer might choose to
create an index on the fly, if necessary. That's still faster than the
first strategy.

Now, let's try a third strategy:

This strategy depends on a sorted index on tag_id, or generating one on the
fly as discussed before.

Retrieve the rows from the table in order by ascending (or descending, it
doesn't matter) tag_id. Only evaluate the subquery when a row is retrieved
with a new tag _id, one that differs from the tag_id of the previous row
processed. When the subquery is evaluated, use the sorted index to locate
the relevant rows, as discussed above. This reduces the number of index
lookups in the subquery to about 100.


Finally, the last strategy:

Perform a sort-merge-join strategy on the scan of the table in tag_id order,
using a sorted index on tag_id, as discussed above. This involves walking
the entire index twice, and retrieving 20,000 table rows.

Compared to the first strategy, it should be blindingly fast.

Now, I want to return to the question of whether your suggestion might have
been helpful to the original poster. I can easily imagine an optimizer that
would pick the last strategy for the join solution you presented, but pick
one of the other strategies for the query as originally presented in the
topic starter.

So it's possible that your query is both logically correct, and up to 100
times as fast as the original query. If so, I would hope the original
poster would thank you.

It's to be emphasized that the above discussion of strategies is all
generally under the covers, and not exposed to the SQL programmer.
However, some DBMS products allow the SQL programmer to expose the
optimizer's strategy, and even to influence it, via "hints".

Now let me return to the comments that Roy Hann has made in here. It's
entirely reasonable to expect an industrial strength DBMS to contain a very
clever optimizer these days. It's entirely reasonable to expect the
optimizer to rank the four strategies I've outlined above from slower to
faster, in exactly the same order I've given. It's reasonable to expect a
really good optimizer to consider at leat one of the three faster strategies
regardless of how the query is expressed.

So, over all, I have to agree with Roy. A good application (or report)
developer should be able to express the query in whatever form seems
"natural" to the author, and to presumed maintainers, and to expect the
optimizer to take care of the details.

In situations where the optimizer picks a very slow strategy, the kind of
"expert SQL programming" suggested by others may be apropos. Updgrading
to an industrial strength DBMS may be apropos, if this sort of thing slows
down development effort repeatedly, and if the cost of slowing down
development effort is greater than the cost of updgrading to a different
DBMS.

Hope this helps.