Re: Wishing trolls away

In message <OZ6kdDHEzTpAFwj2_at_thewolery.demon.co.uk>, Anthony W. Youngman <wol_at_thewolery.demon.co.uk> writes

Yes I'm replying to myself - I know ...

>Ask yourself this - how many times do you select one object, based on
>the attributes of another? Would you select a list of men based on
>their wife's bra size? Yep, I'm being facetious. But, let's take a
>simple question. Who owns a blue car?
>
>SELECT CARS WITH COLOUR EQ "BLUE" SAVING UNIQUE OWNER
>LIST CAR-OWNERS
>
>So no, you can't do it in a single command, unlike SQL. (Actually, you
>could, by indexing CAR-OWNERS on car colour, but that would be a stupid
>move...). But by doing a simple "divide and conquer", we can run that
>query extremely fast. Assuming there are, say, 20 owners, we can prove
>statistically that, on average, we need to issue just 22 disk seek
>commands to retrieve the entire dataset into memory. Even if you want
>the owner's addresses (provided you've declared "address" as an
>attribute, and not linked using a foreign key to PROPERTY). Even that
>would just add another 21 seeks. (And actually, if the "rows" happen to
>be in the same disk block, you'll save an access, but lets leave this
>out because the same would apply to SQL.)

Oops - I slipped up here. Assuming just those two tables, we'd probably need 43 seeks. One to get a list of blue cars, one per car to get the owner(s), and one per owner. Plus probably two misses where the data wasn't at the first-guess location.

That's probably another reason why you thought that Pick couldn't handle complex queries - it doesn't have an optimiser, and Pickies can't understand why it should need one! But if you look at the figures I've given you, where is the room for improvement? With forty "fetch data" events, and optimiser could - at max - save you two. It's just not worth the candle.

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