Re: Wishing trolls away

In message <vx1pc.3233$wI4.332863_at_wards.force9.net>, Paul <paul_at_test.com> writes
>Dawn M. Wolthuis wrote:
>> That is what I used to think (and speak on). Then I had and saw teams of
>> software developers working to make companies productive with SQL-based
>> tools on SQL-DBMS's and the MV query language on MV databases. There really
>> is no comparison from an "end-user" standpoint. And when trying to make
>> companies that had been using MV query languages now happy or even satisfied
>> with SQL-based products, it was pretty much impossible. It is my adventure
>> with reporting tools and query languages that led me to believe that PICK
>> had something that was both invisible to the industry and superior in many
>> respects to what folks were doing (SQL).
>
>My anecdotal experience of Pick has been that it was used for an online
>system where it seem to perform fairly well. But the data was exported
>every day to a SQL DBMS for use in queries, reports etc.
>
>Quite often the data that would come over would be corrupted due to
>some internal problems with the Pick system. Whereas in the SQL system
>the only errors would be logical because the DBMS would make sure all
>the constraints were enforced.

And I've heard loads of moans from people doing data warehousing that fetching data from relational systems was a real pain in the neck because huge amounts of data was invalid.

Yep, I know what you mean - you're expecting an integer and you get a text string or whatever - that's crap validation in Pick. But what does the user do if the system won't accept valid data?

How often do I walk away from a website because it won't let me enter what I consider to be correct data. Or it insists I select a US state as my place of residence :-) Yep. That's crap design, not a fault of the model. But so is dud data in Pick - a design fault not a model fault. And it bites relational just as much as it bites Pick.
>
>Maybe the strength of Pick is for systems that do a lot of simultaneous
>editing and selecting of single records? Whereas the strength of
>relational is for performing complex queries? It seemed to me that Pick
>was very much a "front-end" system, and SQL/relational is very much a
>"back-end" system.
>
>I've not had direct experience of Pick but my understanding was that
>kind of queries we needed (several layers of subselects, complicated
>EXISTS clauses, aggregation, etc.) would just be too much for Pick,
>both in terms of writing the queries, and running them.

Ummm. I'll repeat my little story. When doing a port from Pick to SQL, some consultants spent SIX MONTHS trying to get a complex query up to speed against the Pick system (actually, UniVerse). Finally, they crowed to management that they could beat the Pick system by 30 seconds (10% of five minutes). They promptly went strangely quiet when the Pick guy pointed out that the Pick system was an old P90, and shouldn't their brand-spanking-new twin Xeon 800 be able to beat that by a hell of a lot more than 10%?

You need to learn rather more about Pick. Yes, it will struggle if you give it a query like yours, but that's because you've tried to make it dance to the relational tune when it's not a relational database. If you've designed your Pick database AS A PICK DATABASE, I don't think an EXISTS concept makes any sense. Subselects are no problem whatsoever - you just need to know how to ask the question right :-) I don't know what an aggregation is, so I can't say.

But think about this. Yep, I'm bringing implementation into this, but why do relational people seem so determined to leave it out? In a PROPERLY DESIGNED Pick system, our "table" will contain a normalised view of a real-world object. Each instance of the object is a single "row", and the primary key isn't indexed - because it's hashed it's effectively a pointer directly to where the data is stored.

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

And even a badly designed Pick database would probably perform this well. Let's say you need the address. How many disk seeks is THAT for a relational database, especially if you haven't clustered ADDRESS with OWNER? So. I would say that if you tried to translate your SQL into the Pick data query language then yes, Pick would choke. If you talk English to a Frenchman he will (a) not understand, or (b) refuse to listen in disgust, or (c) possibly be polite and try and understand even if he doesn't understand English very well.

Same with Pick - it's NOT relational, and if you talk SQL-ese it won't like it. But if you talk to it in its own language, all the evidence says that it will outperform SQL - my example of a P90 holding its own against a twin Xeon seems to be quite common ...
>
>Are there any free/open-source implementations of Pick-style DBMSs?
>It might be interesting to set up a Pick database and a SQL database on
>the same spec machine, with the same data, and see how they both cope
>with various queries. I've been re-reading the red/blue car exchange
>that was on this newsgroup a while ago; that may be a good one to try.

I'd suggest downloading a commercial freebie and playing with it. Go to www.u2ug.org and look for the links to the IBM download pages. The denizens of comp.databases.pick are friendly if you need any help.

And yes there are various free(ish) Pick-style systems. I hesitate to point you at them if you plan to do any performance comparisons, because they're young, not yet version 1, and not yet really up to strength.

I'm writing one, too :-) but look at my sig for the leading one at the moment.
>
>Paul.

Cheers,
Wol

-- 
Anthony W. Youngman <pixie_at_thewolery.demon.co.uk>
'Yings, yow graley yin! Suz ae rikt dheu,' said the blue man, taking the
thimble. 'What *is* he?' said Magrat. 'They're gnomes,' said Nanny. The man
lowered the thimble. 'Pictsies!' Carpe Jugulum, Terry Pratchett 1998
Visit the MaVerick web-site - <http://www.maverick-dbms.org> Open Source Pick