Re: Pizza Example

In message <KGmdc.65126$bn6.4336759_at_phobos.telenet-ops.be>, Jan Hidders <jan.hidders_at_REMOVETHIS.pandora.be> writes
>My goodness. Do I really have to explain to you what the trade-offs are
>between a DBMS with a declarative query language and a good query
>optimizer vs. a system with an imperative query language and no query
>optimizer? Could you, just to indulge me, sketch for me what you think
>the trade-offs are and under what circumstances you would prefer one
>type of system over the other?

Yes you do. It's actually quite easy (if you understand statistics) to prove that an optimiser would *actively* *hinder* the Pick query engine. Okay, you need to predict and predeclare indices (Pick won't create them "on the fly"), but the maths to then prove that no improvement is possible is fairly trivial.

To quote from the Pick faq, "SQL optimises the easy task of finding data in memory. Pick optimises the hard task of getting the data from disk into memory". Given a known primary key, it takes on average about 1.05 disk seeks (excluding OS file management overhead) to retrieve a record. Given a known index value, it takes a further 1.05 disk seeks to retrieve a list of ALL primary keys associated with that value. Added to that, a Pick record usually contains all data associated with an entity - that would likely be scattered across multiple rows in multiple tables requiring multiple seeks in a relational db.

A tuned Pick system runs like greased lightning. I've given this example before - somebody quoted the war story where a bunch of consultants spent SIX MONTHS tuning a snoracle query to the point where it could beat the Pick (actually UniVerse) system by 10%. They were proudly proclaiming this to the client management until the guy looking after UV pointed out that this query was optimised to the hilt, and was running on a twin 800 Xeon stuffed to the gills with ram. The machine they were so proud of beating was an UNtuned uni-processor P90. This relative performance actually seems to be par for the course...

Cheers,
Wol

-- 
Anthony W. Youngman - wol at thewolery dot demon dot co dot uk
HEX wondered how much he should tell the Wizards. He felt it would not be a
good idea to burden them with too much input. Hex always thought of his reports
as Lies-to-People.
The Science of Discworld : (c) Terry Pratchett 1999