"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
|
