Re: Pizza Example

From: Jan Hidders <jan.hidders_at_REMOVETHIS.pandora.be>
Date: Sun, 18 Apr 2004 12:42:45 GMT
Message-ID: <9Jugc.75998$vO4.5098632@phobos.telenet-ops.be>

Anthony W. Youngman wrote:
> 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.

I believe immediately that Pick queries are very hard to optimize. That is exactly what you would expect in a system that lacks data-independence and has a non-declarative query language.

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

I can only conclude from this that the person who wrote that FAQ doesn't have a clue about what most relational query optimizers do.

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

Sure. Retrieving records with an index is also very fast with DBM files. But what happens if you have a query with a couple of joins, a little aggregation and some subqueries?

Jan Hidders

Received on Sun Apr 18 2004 - 07:42:45 CDT