 | |
#31
 
| |||
| |||
|
Re: Need Efficent Query -
11-16-2007
, 11:27 PM
|
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. |
good database design and good query design. In general, queries will be
written anywhere from 100 to 10,000 times as frequently as new databases
will be designed.
So if bad database design is compensated for by extra effort in writing
queries, the overall effect will be to degrade developer effort to an
extraordinary degree.
The theory and practice of good database design is a topic that's separate
from general programming expertise. A great many people who get into
database design after ten or twenty years in programming fail to recognize
this fact. As a consequence, they skip over learning the fundamentals of
database management. After all, it's just a big fancy file cabinet, isn't
it?
As a consequence, they produce and pass off database designs that are
mediocre, and sometimes outright unsatisfactory, as acceptable, and these
make their way into production. At that point, application programmers
writing software to update and/or query the database are force to expend
extra effort to compensate for bad design.
This is probably the rule, rather than the exception, in present practices.
#32
| |||
| |||
|
|
As a consequence, they produce and pass off database designs that are mediocre, and sometimes outright unsatisfactory, as acceptable, and these make their way into production. At that point, application programmers writing software to update and/or query the database are force to expend extra effort to compensate for bad design. This is probably the rule, rather than the exception, in present practices. |
Roy
#33
| |||
| |||
|
|
"Captain Paralytic" <paul_lautman (AT) yahoo (DOT) com> wrote in message news:1195033185.507438.249070 (AT) 57g2000hsv (DOT) googlegroups.com... 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. I agree with you, in concept. I have a quibble about the difference between good database design and good query design. In general, queries will be written anywhere from 100 to 10,000 times as frequently as new databases will be designed. So if bad database design is compensated for by extra effort in writing queries, the overall effect will be to degrade developer effort to an extraordinary degree. The theory and practice of good database design is a topic that's separate from general programming expertise. A great many people who get into database design after ten or twenty years in programming fail to recognize this fact. As a consequence, they skip over learning the fundamentals of database management. After all, it's just a big fancy file cabinet, isn't it? As a consequence, they produce and pass off database designs that are mediocre, and sometimes outright unsatisfactory, as acceptable, and these make their way into production. At that point, application programmers writing software to update and/or query the database are force to expend extra effort to compensate for bad design. This is probably the rule, rather than the exception, in present practices. |
From individual programmers, I would tend to agree. Too many of them
don't know about normalization rules, much less when one should break
the rules.
However, I've also found larger shops are much better at database
design, and the larger the shop, typically the better the design.
Eventually, you get into very large shops who have dedicated
designers/admins. And when you get into very large shops, the reasons
for a specific design may not be apparent to the individual programmer.
That's because the programmer is looking at one tree, while the
admin/designer is seeing the forest.
But there is no excuse for not having a reasonably good design in any
database. Normalization isn't hard to learn, and designing database in
3NF will be close for the vast majority of the smaller systems out there
today.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================
#34
| |||
| |||
|
|
... 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. |
Firebird, MaxDB...
Quote:
|
... SQL was intended to be a goal-oriented language. The idea was (explicitly) to allow the optimizer to discern the essential meaning of the query--no matter how it was expressed--and come up with an efficient way of producing the requested result. That's how it is supposed to work, and lots of products achieve that to a remarkable degree. 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. First of all that doesn't refute anything I said. ... Yes it does. It is not a "goal-oriented language". |
Quote:
|
It was never meant to be. ... |
#35
| |||
| |||
|
|
David Cressey wrote: "Captain Paralytic" <paul_lautman (AT) yahoo (DOT) com> wrote in message news:1195033185.507438.249070 (AT) 57g2000hsv (DOT) googlegroups.com... 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. I agree with you, in concept. I have a quibble about the difference between good database design and good query design. In general, queries will be written anywhere from 100 to 10,000 times as frequently as new databases will be designed. So if bad database design is compensated for by extra effort in writing queries, the overall effect will be to degrade developer effort to an extraordinary degree. The theory and practice of good database design is a topic that's separate from general programming expertise. A great many people who get into database design after ten or twenty years in programming fail to recognize this fact. As a consequence, they skip over learning the fundamentals of database management. After all, it's just a big fancy file cabinet, isn't it? As a consequence, they produce and pass off database designs that are mediocre, and sometimes outright unsatisfactory, as acceptable, and these make their way into production. At that point, application programmers writing software to update and/or query the database are force to expend extra effort to compensate for bad design. This is probably the rule, rather than the exception, in present practices. David, From individual programmers, I would tend to agree. Too many of them don't know about normalization rules, much less when one should break the rules. However, I've also found larger shops are much better at database design, and the larger the shop, typically the better the design. Eventually, you get into very large shops who have dedicated designers/admins. And when you get into very large shops, the reasons for a specific design may not be apparent to the individual programmer. That's because the programmer is looking at one tree, while the admin/designer is seeing the forest. But there is no excuse for not having a reasonably good design in any database. Normalization isn't hard to learn, and designing database in 3NF will be close for the vast majority of the smaller systems out there today. |
Quote:
|
-- ================== Remove the "x" from my email address Jerry Stuckle JDS Computer Training Corp. jstucklex (AT) attglobal (DOT) net ================== |
#36
| |||
| |||
|
|
On Nov 14, 11:36 am, "Roy Hann" <specia... (AT) processed (DOT) almost.meat wrote: [] This conversation is about the ability of optimizers to take a correct specification of a required result and generate an optimal execution plan. 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. I haven't considered the problem of optimization to the level you suggest. |
engineers of that product did consider precisely the issue you raise. I can
assure you that their optimizer was good enough so that I did not occupy
myself with second guessing the optimizer. During that time frame, I was
sometimes called in to speed up a database application. In almost all cases
a 2 for 1 or even a 10 for 1 improvement in speed was possible by just doing
the simple things right.
In particular, the list (or tree) of suggested solutions presented to the
optimizer is most likely a very small subset of the space of possible
solutions to the query. Most possible solutions aren't even considered.
It's possible that the very optimal solution lies in the set never
considered by the optimizer. It's also not only possible, but likely, that
the solution chosen by the optimizer i 90% as fast as the very optimal
solution.
It's also likely, in an awful lot of processing situations, that a solution
that runs at 90% of the limit is good enough for the task at hand. As Jerry
pointed out in a different response, manual tuning effort can be restricted
to only those cases where it's really needed.
#37
| |||||||||||
| |||||||||||
|
|
"Ed Prochak" <edproc... (AT) gmail (DOT) com> wrote in message [] On 13 Nov, 12:07, "Roy Hann" <specia... (AT) processed (DOT) almost.meat> wrote: I also expect that most working programmers have little or no interest in SQL, regard it as a distraction, 99.7% of all statistics are made up. I suspect this comment from the "Captain" is too. Er, actually Captain P didn't write that, I did. |
Quote:
|
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. |
Quote:
|
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. |
just as valid as yours. We could sit down and count how many
programmers we each know, and categorize their feelings. Really, all
I wanted to do was point out your claim is disputed. Whether it is a
far-fetched or "intuitively obvious" claim is irrellevant.
So lets keep the discussion to just us and leave vague other
programmers out of it. You feel this is a problem. I don't (at least
not in the same way as you).
Quote:
|
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. |
underlying application data model can be at least as influential.
Quote:
|
... 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. |
RDBMS products to help.
(e.g., the EXPLAIN PLAN command)
Quote:
|
... 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? |
There's a quote I'm fond of that goes
An engineer is someone that can do for one dollar
what any damn fool can do for two.
--- Wellington
There is one general principal they can follow: Know thy data. But
those programmers that you seem to encounter have no interest in
learning so this migth be asking too much from them.
Quote:
|
SQL was intended to be a goal-oriented language. The idea was (explicitly) to allow the optimizer to discern the essential meaning of the query--no matter how it was expressed--and come up with an efficient way of producing the requested result. That's how it is supposed to work, and lots of products achieve that to a remarkable degree. Wrong. 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." |
quote.
Quote:
|
Optimization is a late addition to SQL. 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. |
the one to go back to when SQL was being defined. So a decade is not a
long period of time for you? You keep wanting things on both sides of
the fence. If the history is not relevant, lets leave both aspects
discussed here out of it.
Quote:
|
It was originally intended to be a non-programmer's programming language. 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." |
some of the documents I read made a point of managers using it.I had
some success teaching a marketing manager to use it. That did reduce
programming costs in that he did some of his own reports with little
assistance from me.
But also here I think you are trying to have it both ways. If SQL is
for professional programmers, then I would expect them to have the
desire and the skills to know how to optimize a query.
[]
Quote:
|
Flipping it around, if we had a perfect SQL engine, no one would ever ask to add a feature so they could write an equivalent query that would be slower! :-) 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) 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". |
provide correct results, but it does NOT promise to produce those
results within any time constraint. To keep it in the present era, let
me ask:
If you can find a optimization or performance claim in the SQL
standard, please let me know.
The SQL standard, as I understand the situation (I haven't read the
real standard, just summaries), leaves optimization or performance
features in the hands of the DBMS products.
[]
Quote:
|
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. No, SQL is unlike any other comparably widely used language because it is declarative. Roy |
#38
| |||
| |||
|
|
"Ed Prochak" <edproc... (AT) gmail (DOT) com> wrote in message news:1195078632.855789.152790 (AT) 22g2000hsm (DOT) googlegroups.com... On Nov 14, 11:36 am, "Roy Hann" <specia... (AT) processed (DOT) almost.meat wrote: [] This conversation is about the ability of optimizers to take a correct specification of a required result and generate an optimal execution plan. 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. I haven't considered the problem of optimization to the level you suggest. |
rows.
That is really pretty small. Your other post about the stategies was
very good.
Quote:
|
But I used DEC Rdb/VMS in the 80s and 90s, and I would suggest the DBMS engineers of that product did consider precisely the issue you raise. I can assure you that their optimizer was good enough so that I did not occupy myself with second guessing the optimizer. During that time frame, I was sometimes called in to speed up a database application. In almost all cases a 2 for 1 or even a 10 for 1 improvement in speed was possible by just doing the simple things right. |
Quote:
|
In particular, the list (or tree) of suggested solutions presented to the optimizer is most likely a very small subset of the space of possible solutions to the query. Most possible solutions aren't even considered. It's possible that the very optimal solution lies in the set never considered by the optimizer. It's also not only possible, but likely, that the solution chosen by the optimizer i 90% as fast as the very optimal solution. It's also likely, in an awful lot of processing situations, that a solution that runs at 90% of the limit is good enough for the task at hand. As Jerry pointed out in a different response, manual tuning effort can be restricted to only those cases where it's really needed. |
improve it we might not have the optimal solution. But in business we
often settle for good-enough. (As coming from an engineering
background, this really grates on my nerves sometimes.)
Thanks for your comments.
Ed
#39
| |||
| |||
|
|
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) 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". That is exactly the problem in this discussion. SQL promises to provide correct results, ... The SQL standard, as I understand the situation (I haven't read the real standard, just summaries), leaves optimization or performance features in the hands of the DBMS products. [] 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. No, SQL is unlike any other comparably widely used language because it is declarative. Roy You are saying SQL is the ONLY declarative language? |
widely used'. No 'widely used' programming language is declarative
(when was the last time you used Erlang or Icon?
I am not an expert, but other things that I would consider declarative
are:
- makefiles (commonly used, but hardly considered a 'language')
- grammars (lex, yacc)
- regular expressions
- pattern matching &/or functional languages (Erlang, Mathematica)
- backtracking &/or inferential languages (Icon, Prolog)
....additions to this list welcome.
#40
| |||
| |||
|
|
(when was the last time you used Erlang or Icon? |
[...]
/Lennart
 |
« Previous Thread
|
Next Thread »
| Thread Tools | Search this Thread |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2008, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2008, Jelsoft Enterprises Ltd.