Re: Date's First Great Blunder

In message <UW9ic.33158$h44.4931728_at_stones.force9.net>, Paul <paul_at_test.com> writes
>Dawn M. Wolthuis wrote:
>> While it seems to me that the PICK model is much more flexible (but I
>> don't have evidence to prove that), I will grant that all of the
>> systems I have seen that use the model are mid-range in size. I
>> suspect that when you need to scale beyond the millions to the
>> billions of stored "records" in one "file" you might be out of the
>> PICK league. I'll have to ask some pickies about scaling up. I'm
>> more inclined to think that recent approaches to scale, such as
>> clustering, might apply to the big guys where PICK might be left in
>> the dust on newer scaling techniques.
>
>When I said scaling I was actually thinking in terms of more tables,
>more queries, and more complex queries, rather than more rows. But I
>guess the same might apply to that sort of scaling, I don't know enough
>about how Pick works to say that.

Pick needs far fewer "tables". So in that regard, it doesn't NEED to scale as far...
>
>I think this example has already been made:
>If you have an invoicing database and the order lines are physically
>stored with the orders (as in Pick), then it is optimised for printing
>invoices. But say you want to know how many orders there are with a
>particular order line?

If you mean "how many times did customers buy three widgets", then yes, Pick might not like it. But I don't think relational would, either.
>
>With relational, your database is already optimized for this; it
>"scales" nicely to the new type of requirement. With Pick I understand
>you'd have some extra work to do by scanning every single order record
>looking for a particular order line.

Pick does it the same way as relational. If you haven't got an index, relational is screwed because it has to scan every row looking for that order line ...

If Pick has got an index, the db engine will hand it a list of record primary keys that reference that order line, and then it will scan through just that list of records. Just like a relational index will enable the db engine to retrieve a list of rows.

And Pick will actually score over relational if a record has multiple instances of the line.

What you have to watch out for, is that Pick will hand more work to the CPU, because it has to extract the lines from the records, but because in practice it seems to retrieve its records from disk far faster than relational retrieves its rows, the i/o time saved more than outweighs the cpu time spent (by a LARGE margin). (And the other thing is, relational probably spends the same cpu time, except it's apparent with Pick and hidden with relational.)
>
>My experience has been that businesses will ask for the most convoluted
>reports you could imagine, not just the ones that you might think are
>needed at first analysis.

You can say that again!
>
>Paul.

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