Roy Hann wrote:
I agree with Paul here. And I wouldn't say they are necessarily
*expert* SQL programmers. I've seen a lot of average SQL programmers
who are still good at what they do.

I don't think that's a fair statement. Most of the experienced
programmers I know who use SQL treat it for what it is - just another
way to access data. They may not be expert at it, but they know enough
to create reasonably complex queries to get the information they want.
And they know how to use EXPLAIN or its equivalent in other databases to
help make their queries more efficient.

No, SQL (Structured Query Language) was created to access data in IBM's
System R relational database. And back in the early 70's, optimizers
were a pipe dream.

Current RDBMS's do try to optimize the database, and do so in different
ways and to different extents. But if you know of others who do what
you want, you're also free to use those products.

But even the best optimizers don't get it right all of the time, and you
need to have knowledge of the strengths and weaknesses of each one to be
able to do it well.

And if we had perfect computer systems, we would never need programmers.
People would be able to just speak into a microphone and get the
response to their questions.

Programmers are actually a bad thing. We shouldn't want to want them.

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

"Roy Hann" <specially (AT) processed (DOT) almost.meat> wrote


[snip]

Agreed.

People who began using SQL in the context of a DBMS with an excellent
optimizer got adapted, very quickly, to the idea that the query should
specify *what* data to retrieve, with a minimum of attention paid to *how*
to retrieve that data. People who began using SQL in the context of a DBMS
with a lousy optimizer quickly adapted to helping the DBMS do its job,
something those of us who were more forutnate didn't have to bother with.
Every optimizer has "blind spots".

But some optimizers are better than others. Different products will deliver
different results (in terms of speed) for the same queries.

As far as attention to optimization goes, I'll admit that there are times
when attention to optimization is necessary to produce good results. But
that's the exception rather than the rule. As a general rule, one is
better off paying attention to simple but sound data architecture and
database design. If you do that, and you use a good DBMS, query
optimization pretty much takes care of itself. When an exception to this
generalization comes up, you can deal with it on an exceptional basis.

"Jerry Stuckle" <jstucklex (AT) attglobal (DOT) net> wrote

I would hope the average SQL programmer is good at what they do! :-) But
you are clearly talking about SQL programmers, not Java/Cobol/XYZ
programmers who are obliged to use SQL. My claim is that those programmers
should expect better. If they make the effort to write a syntactically
correct statement of their required result then they ought to expect the
server to figure out a good way to execute it. There is no sane argument
for why they should want anything less and there are products that do much
better at that than others. The ones that don't do well deserve to be
criticised for it.

There is no dispute that there are people at that level of skill, and that's
great. But they are compensating for the defects of the product, and their
ability to compensate makes them expert SQL programmers. But there are lots
of programmers who have to use SQL who aren't at that level of skill who
suffer unnecessarily. Furthermore, the ones who are at that level of skill
got there through some effort that could have been devoted to something else
more productive. You just can't make a case for preferring anything less
than the best optimizer that the state-of-the-art can give you.

First of all that doesn't refute anything I said. Secondly SQL (nee SEQUEL)
predates System R by many years--almost a decade. SQL was added to System R
very late in the day. Thirdly there were quite reasonable RDBMS optimizers
by the late 70s. And fourthly, we are in the 21st century now and what was
dreamt of 30 years ago is eminently possible now--as demonstrated daily by
the better products you can actually buy or download today.

Sure. Experts will always be needed, but one should hope they'll be needed
with ever-decreasing frequency and only for ever more complicated queries.
The OP's query was, IMO, capable of a trivial optimization and there are
products that you can use today that would have found it automatically.

Maybe you think that's whimsical, but actually that is exactly what I think,
in absolute, complete seriousness.

Roy

Well, sort of. SQL is the best known functional language around, but
this is only true within one query. Across queries, SQL is as imperative
as basic. That is why I said that the programmer will have to determine
the strategy. The fact that network traffic is slow enough to take into
account makes getting data from a database server in an optimal fashion
a nice challenge.

I have ranted enough about SQL, even though it has beautiful
optimizations and room for them. So I do not want to do that again. That
said, about 90% of my queries are just of the type "this table, that
index, number 5 please" (sometimes called "ISAM style"). In SQL, the
command is usually bigger than the result I want. For databases, I
compact as many queries into one by using IN clauses instead of many
selects, but here we are again optimizing the SQL. And why? Because,
across queries, SQL is *not* a functional language.

Back to the drawing board...
--
Willem Bogaerts

Application smith
Kratz B.V.
http://www.kratz.nl/

Roy Hann wrote:
No, I am talking about Java/C/C++/Cobol, etc. programmers who use SQL.
And most of them are not "obliged" to use SQL - they CHOOSE to use SQL
because it's the best tool for their applications. That's how I got
started in SQL over 20 years ago.

And every database works differently. One may be the best at optimizing
one SQL statement, but among the worst in optimizing a different SQL
statement. No RDBMS is "best" at optimizing all SQL statements. If
you're going to call yourself a programmer, it's up to you to learn the
strengths and weaknesses of the RDBMS you're using, just like any other
tool.

And if you don't like the way MySQL does it, no one is forcing you to
use it. Get Oracle, DB2, SQL Server or whatever.

No, they aren't compensating for any defects. They know EVERY tool has
strengths and weaknesses. And they learn the strengths and weaknesses
of the tools they use. That makes them good programmers.

But if you think a RDBMS should be able to optimize everything, then why
don't you write your own? I'm sure you'll do better than IBM,
Microsoft, Oracle, MySQL and all the others out there.

Yes it does. It is not a "goal-oriented language". It was never meant
to be.

And no, SQL does NOT predate System R. SQL was created specifically for
the System R in the early 1970's by IBMers Donald D. Chamberlin and
Raymond F. Boyce. Among many other such references:
http://en.wikipedia.org/wiki/Sql

Also well documented in IBM history.

And you don't have to be an expert to learn how to write better SQL
statements. Just a programmer.

And about that time mankind will be replaced by machines.

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

On 13 Nov, 13:34, "Roy Hann" <specia... (AT) processed (DOT) almost.meat> wrote:
Actually there's another aspect that hasn't been mentioned yet and
that is the database design and the choice of indexes. My background
to using databases is ISAM on System 36 and CICS/VSAM. When I design
database tables (structure, indexes, ...), I'm always thinking of how
it will be used and what the data will look like. I know that if I
design it wrong, no optimzer in the world will ever be able to access
the data efficiently. It seems to me that, programmers who have no
appreciation of how to craft an efficient query, are unlikely to have
given sufficient thought to this important aspect. I don't see a great
deal of difference between helping the optimizer by good design of
database and helping it with good design of query.

"Jerry Stuckle" <jstucklex (AT) attglobal (DOT) net> wrote

Agreed.

Of course not, and that would be a foolish claim. I can't imagine how what
I wrote could be interpreted to mean that.

What I *do* claim is that some DBMSs are generally worse overall, and others
are generally better overall. No one could possibly argue against that
without arguing that they are all the same, and we agree they're not. It is
just common sense.

I agree. But the need to do that is not a virtue of the product. All other
things being equal you should prefer the tool that puts the least burden on
its user.

Having to know a special, usually non-obvious, non-portable formulation for
doing common tasks isn't a defect?

No dispute. I think programmers with those skills are admirable in every
way. It's the products I'm grumbling about.

Are you suggesting that isn't a desirable aspiration? Why should I want an
RDBMS that doesn't if I could have one that does?

For the very simple reason that I wouldn't know how. But what I do know is
how to compare existing products and rank them by how close to my ideal they
get, so that I can choose the best of the many real products I can actually
get/buy today.

Now, now. Be serious.

Using the term colloqually, yes it was. To quote Chamberlin and Boyce: "The
work on [SQL]...is consistent with the trend to declarative problem
specification." (But perhaps you have knowledge of a special
domain-specific meaning of the term "goal-oriented language" that I don't
share.)

SQL was the name given to the language component of System R, but it was
derived from other, earlier projects at IBM and elsewhere, including a
language called SQUARE. But I will concede this point. Where it came from
is irrelevant to my main point that in the 21st century optimizers can
reasonably be expected to be highly intelligent.

What an absurd assertion. What is the difference between a programmer who
writes one correct SQL statement (like the OP) and a programmer who knows
how to write a better one? Isn't that the very meaning of "expert"?

For writing programs, yes. Just like we use machines for generating machine
code and harvesting wheat.

Roy

"Captain Paralytic" <paul_lautman (AT) yahoo (DOT) com> wrote

I think this is starting to strain the definition of the term "programmer".
No doubt in small projects you do have jacks-of-all-trades and many of them
are just as good as you say. But even just moderately large projects will
have way more programmers writing applications to someone else's database
design. Whether the job titles distinguish them or not, the people doing
the database design are not the same as the much larger number of people
doing most of the coding.

A good database design benefits all the programmers without them even being
aware of it. They get the benefit of it without having to do anything
special. They also automatically get the benefit of any future server-side
performance enhancements. This is hugely significant. In July I went to
one site and set up rule-based table partitioning. The application speeded
up by a factor of more than 10 without any code changes. In September I
went to another site confidently expecting to pull off the same trick and
found I would have to re-implement the entire application to make it work,
because their code was written so that it couldn't exploit new server-side
features.

And let's not forget platform-independence. I don't personally worship at
application-portability alter, and I don't think many people really do. But
even though it doesn't matter whether an application is
platform-independents, programmers are better off being
platform-independent.

Roy

Roy Hann wrote:
What is "special, usually non-obvious, non-portable"? In MySQL, you can
do it all in ANSI standard SQL. Portable to every database which
supports ANSI SQL.

No, the impossible is not a desirable aspiration. Reality should be,
though.

Then you shouldn't be complaining that programmers who DO know it well
aren't getting it right. Perhaps it's a lot harder than you think -
i.e. impossible - for them to get it right.

I am serious. You seem to be complaining that "they" should get it
right, but have absolutely no idea what you're asking "them" to do.

I've heard the term applied to 4GL languages and some theoretical
languages but never SQL. SQL is simply a means for accessing data.

Actually, not. Sure they got some ideas from SQUARE - but SEQUEL was a
completely separate implementation. But if you want to say that by
getting ideas from SQUARE SEQUEL was derived, then I could point out
SQUARE got ideas from COBOL (which got ideas from Assembler...).

Not at all. Expert programmers can write much more complicated SQL
statements.

For instance, given the following table:

flight_number
departure_airport
departure_time
arrival_airport
arrival_time

an table of airline flights - provide a list of all possible ways to get
from London, England to Salt Lake City, UT for a particular day.
Include all flights with not more than 4 connections.

And yes, it can be done with one SQL statement (but not in MySQL). I
would expect an expert to be able to do this, but not necessarily a good
programmer.

For everything.


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

"Jerry Stuckle" <jstucklex (AT) attglobal (DOT) net> wrote

Sure the syntax is portable. No one has ever argued that it isn't. But you
just got through telling me that what is efficient SQL to one DBMS may not
be efficient to another (and that is true). The whole conversation has
been about efficiency from the start (look at the Subject heading), not
syntax. I have nothing to say about syntax.

You are the one who wanted me to write my own DBMS. I just wanted to be
able to rank real products so I could choose the best one. Who's being more
realistic?

First of all there is never a burden on any consumer to know how to design a
better product before they ask for it. Do you know how to design a car?
That didn't stop you wanting a better car than your first one though, did
it? Secondly, I am not asking for the impossible. I said products already
exist that do what I was talking about. I was not asking anyone to build a
never-before-seen feature.

As I said, I concede the point, although I struggle to see what COBOL and
Assembler have to do with it.

This conversation is about the ability of optimizers to take a correct
specification of a required result and generate an optimal execution plan.
No one is talking about the limits of the complexity of the code programmers
can write. But you do raise an important distinction. There are two kinds
of expertise involved. One kind of expertise is having sufficient
understanding to express a desired result, and the other is knowing which of
the very many equivalent ways of expressing it will work efficiently. I say
the first skill will always be valuable, but the value of the second is
inversely proportional to how good the optimzer is. And one hopes
optimizers are getting better all the time, so one also necessarily also
hopes the second kind of expertise becomes less valuable.

You do hope optimizers get better don't you?

Roy