Re: Pizza Example
Date: Sun, 18 Apr 2004 12:42:45 GMT
Message-ID: <9Jugc.75998$vO4.5098632_at_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.
> 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