"Roy Hann" <specially@[EMAIL PROTECTED]
> wrote in message
news:u8mdnbcFpqO_kPzVnZ2dnUVZ8q2dnZ2d@[EMAIL PROTECTED]
> "David Cressey" <cressey73@[EMAIL PROTECTED]
> wrote in message
> news:5u78k.10$0b.5@[EMAIL PROTECTED]
> >
> > "Roy Hann" <specially@[EMAIL PROTECTED]
> wrote in message
> > news:iYydnTlMkq9hZ_3VRVnyvwA@[EMAIL PROTECTED]
> >> It is, doing it the way some of them do it. I just finished a
re-write
> >> of
> > a
> >> batch job that consisted of over 14,000 lines of code and sent 2.5
> >> million
> >> queries to the server, and ran for 4.5 hours. When I'd finished with
it,
> > it
> >> was 600 lines of code and it sent 6 queries to the server and ran in
12
> >> seconds. And I am pretty sure one of the six queries isn't needed.
>
> > I think the best I ever did was 15 minutes to 6 seconds. The amazing
> > thing
> > to me is that almost all of the truly doggy code I've seen in database
> > work
> > was justified as "we did it that way for efficiency". People who try
to
> > optimize an application by doing manually what a good query optimizer
> > does
> > mechanically almost always slow themselves down.
>
> Good point, and in fact my anecdote above has an amusing prequel. About
> four months earlier I had decomissioned a system for a customer who had
> seven years worth of data on a high-end Alphaserver with a StorageWorks
> array. They wanted to keep the data available so I suggested ****ting it
to
> a virtual PC on one of their available PCs. It had a single very large
> capacity disk drive which was just big enough for the database. About a
> month later I got a call complaining that it worked but the re****ts were
> dog-slow. (Really? Whoda thunk?) Anyway, I consented to see what I
could
> do and a couple of hours of poking about showed it would be possible to
use
> a rule-based partitioning scheme on the tables to speed up the re****ts.
A
> couple of hours later the big tables were suitably partitioned and I
turned
> on parallel query execution to process the partitions in parallel.
Presto!
> The virtual PC with one disk now out-performed the original Alphaserver
> running the same re****t by 40-50%. (If I'd had four or five disks to
play
> with it would probably have been 200-300% faster.)
>
> Flushed with this notable triumph, when the next client came along with
a
> performance problem, I smugly trotted out this tale and made extravagant
> claims for what I could do for them almost for free.
>
> Wrong-o!
>
> Unfortunately the next application had completely subverted the server
(for
> performance reasons) and was not only bringing the tables to the
application
> and doing the joins in the application, they had also completely
hardcoded
a
> query strategy that actually started with one of the least restrictive
> conditions. Anyway, to cut the story short, that's how I ended up doing
a
> re-write. The original programmer had made it impossible for the
optimizer
> to have a global picture of the problem; the code was incapable of using
> server-side parallelism to exploit all the cores and register sets, and
the
> storm of messages flying back and forth between the application and the
> server actually looked like a denial of service attack!
>
> As Hoare wrote (and was quoted by Knuth): premature optimization is the
root
> of all evil. To which I add: especially when it is so premature you are
> still clueless.
>
> Roy
>
After dealing with performance problems on Rdb/VMS on several contracts,
I
developed a short list of things to check for, regardles of where the
client told me to look for performance issues. Briefly summarized the run
like this:
1. Defragment the Disks
2. Rough tune the virtual memory.
3. Map all unmapped tables (this one is good for Oracle as well)
4. Index all unindexed tables, unless there's a real good reason not to.
(Oracle gives you an index when you declare a primary key. Rdb didn't,
back then)
5. Rebuild all sorted indexes that allow duplicates (a flaw in Rdb).
6. Put over populated hash indexes in a right sized area (read:
tablespace)
7. Eliminate redundant indexes.
8. Tune the queries in the application code.
9. Balance the load on the disks.
10. Increase the number of buffers for database pages.
If I got positive results on about 5 of these, it was enough to make me
look like a genius. I remember one site where the end users said "We
didn't
realize you were going to install a whole new system." They were used to
the app delaying 10 minutes to insert a new contract, instead of only
waiting two seconds.
Then again, I remember the site where I recommended defragmenting the
disks,
and the DBA treated me like I was criminally insane. That guy thought
that
everyone was a moron, except for him.
PS: I like your comment about subverting the server. Some programmers
like
to tell the server what to do, a little bit at a time, out of fear of
what
will happen if they tell the server what they want and not how to retrieve
it. The programmers are just avenging themselves on the users who reveal
the
requirements to the programmers a little bit at a time, instead of all at
once.
|
