Re: Dreaming About Redesigning SQL

In article <bmutga$jdk$1@nyytiset.pp.htv.fi>, Lauri Pietarinen
<lauri.pietarinen@atbusiness.com> writes
>Anthony W. Youngman wrote:
>
>>Well, as far as we MV'ers are concerned, performance IS a problem with
>>the relational approach. The attitude (as far as I can tell) with
>>relational is to hide the actual DB implementation from the programmers.
>>So it is a design "flaw" that it is extremely easy for a programmer to
>>do something stupid. And you need a DBA to try and protect the database
>>from the programmers!
>>
>>As soon as a requirement for a database specifies extraction of the
>>maximum power from the box, it OUGHT to rule out all the current
>>relational databases. MV flattens it for it for performance. As an MV
>>programmer, I *KNOW* that I can find any thing I'm looking for (or find
>>out it doesn't exist) with just ONE disk seek. A relational programmer
>>has to ask the db "does this exist" and hope the db is optimised to be
>>able to return the result quickly. To quote the Pick FAQ "SQL optimises
>>the easy task of finding stuff in memory. Pick optimises the hard task
>>of getting it into memory in the first place".
>>
>So in your opinion, is the problem
>
>1)  SQL is so hard that the average programmer will not know how to use it
>efficiently

Nope

>or
>2)  Relational (or SQL-) DBMS'es are just too slow
>
Yes.

>If 2)  then why don't we get a bit more concrete.  Could you give
>an example of a query that in your experience would be too slow using
>a standard SQL database (e.g. Oracle, or MySQL). We could then
>actually try it out on some machine and compare.  I suggest using
>the customer-order-order_detail-product database

Okay. Give me a FORMULA that returns a time in seconds for your query.

Let's assume I want to print a statement of how many invoices were sent
to a customer, along with various details of those invoices. My invoice
file is indexed by company/month, and we can reasonably assume that the
time taken to produce the statement is infinitesimal compared to the
time taken to retrieve the invoice data from disk. For MV

T = (2 + N) * ST * 1.05

Where T is the time taken to produce the report, N is the number of
invoices, and ST is the hard disk seek time.

I've assumed I have to access the company details as well, hence the 2
(1 for company, 1 for the index). I've also assumed that the data isn't
cached in RAM, which I think is reasonable if we assume the hardware is
being stressed.
>
>If 1)  I would like to hear some concrete examples. 

It's 2, so ...

But as I understand relational theory, such a question is completely
outside the scope of the theory. Seeing as it tries to divorce the
database logic from the practical implementation ...

And you know it's been proven that Huffman coding is the most efficient
compression algorithm? (Actually, it isn't - it's been proven it can't
be improved upon, which isn't the same thing...). Can you improve on the
formula I've just given you? Given that if we could change the 1.05 to 1
then we can prove it can't be improved upon ... again - I've taken the
liberty of assuming that a MV FILE is equivalent to an entity if we
assume the relational designer has been thinking in an entity-attribute-
relation sort of way. My maths isn't good enough to prove it, but I
think it would be pretty easy to prove that accessing data as "one and
only one complete entity" at a time is the most efficient way.
>
>best regards,
>Lauri Pietarinen
>
Looking forward to you coming up with maths that can prove relational
can even EQUAL MV :-)

Cheers,
Wol
-- 
Anthony W. Youngman - wol at thewolery dot demon dot co dot uk
Witches are curious by definition and inquisitive by nature. She moved in. "Let 
me through. I'm a nosey person.", she said, employing both elbows.
Maskerade : (c) 1995 Terry Pratchett