Re: 4 the FAQ: Are Commercial DBMS Truly Relational?

"Kenneth Downs" <firstinit.lastname_at_lastnameplusfam.net> wrote in message news:r2rbkc.5b8.ln_at_mercury.downsfam.net...
> Have you ever "optimized" the work of others after the fact? I was in a
> situation once where I optimized several programs, gaining performance
> improvements in every case counting between 1-3 orders of magnitude. The
> supervisor was astonished and asked me how I did it. In each case the
> answer was the same, the program was doing too much work. It would do a
> lot of wrong work, and then somewhere do the right work. The entire
> optimization effort was in reducing the program to doing only what was
> necessary. There may have been a couple of tricks-of-the-trade for the
> platform in question, but mostly it was eliminating work.

All the time. About half of my consulting contracts were speed up jobs, period.

Even when my primary task was something else, programmers would come up to me all the time asking me to
speed up their queries. I was better at doing that in DEC Rdb than in Oracle. I began to get used to the Oracle RBO, which is really lame, at about the time that Oracle released the CBO. The CBO, which uses some of the same techniques as the DEC optimizer, is much better, as long as the database designer and the DBA know what they are doing.

Sometimes queries run slow because the database simply lacks routine maintenance.

Sometimes queries run slow because of atrocious table design, or unfortunate index design.

Sometimes queries run slow because the optimizer needs a few "hints".

Sometimes there is more than one way to do it, and one of the is much faster than the other.

But most of the time, when a query runs slow and gives wrong results, it's because the database designer figured that no one in his right mind would want to do that query.

Two of my favorite speed ups:

The DEC optimizer is AWFULLY GOOD! It usually knows more than I do about it's job. But there's one little trick it missed:

GROUP BY A,B runs like a dog, but GROUP BY B,A ORDER BY A, B runs much faster, even though it has to do an extra sort.

Another one:

We have 30,000 addresses, but they all have COUNTRY = 'USA' in them. There is a query that
specifies the state, city, and zip code, but doesn't specify the country. After all, why specify the country, when it's always 'USA' in this database? The query scans the index. We add the specification COUTRY = 'USA' to the WHERE clause, and all of a sudden it uses the index for lookup, which runs much faster.

Can you figure out why each of these got sped up? Hint: think about compound index keys.

Maybe these cases are much too simple to post in here, but back when I was making the big bucks, this is the sort of thing that made customers grateful! Received on Sun Oct 10 2004 - 20:09:41 CEST