Using OrderedSets Re: [IDEA] BinaryLink that supports order !!!

[Ruslan Zasukhin]

On 12/18/05 9:07 AM, "Ed Kleban" <Ed at Kleban.com> wrote:

>> Also this give ability improve SQL to do all above operations *and*
>> Searches of kind:
>> 
>>     find records of table A, which have FIRST child with such conditions....
> 
>> 
>> Particular example:
>> 
>>     SELECT Person.*
>>     FROM Person parent, Person child USING Born
>>     WHERE FIRST child.Name = 'Peter'
>> 
>> To express this right now in Valentina 2 (or in mySQL, Oracle, ... )
>> You must self first of all provide additional column to keep order, spend
>> hours to write logic, then do sql as
>> 
>>     SELECT Person.*
>>     FROM Person parent, Person child JOIN parent.id = child.ptr
>>     WHERE child.Name = 'Peter' and child.index = 1
>  
>> I underline that The main advantage is not in simpler SQL, but in hours of
>> work to implement order manually using additional columns.
>> 
> 
> The benefits of using OBL therefore are that:
> 
> 1) The OBL saves you the hassle of having to explicitly allocate the
> additional column to keep the order, because it effectively moves that
> column into the OBL automatically and manages it for you no matter whether
> OBL is implemented with (A), (B), or (C) above.  This is a benefit you get
> whether using OBL from SQL or from API, and...

Right!
 
> 2) Perhaps...  Actually (1) is the only benefit I can come up with.  As you
> point out it makes the SQL a little more clear perhaps (at a penalty for
> using non-standard SQL) but that's a minor issue.  Perhaps you can come up
> with some words for other benefits this offers.  But (1) is a GREAT benefit.

2 main befits as always in Valentina

1) SIMPLER for developer, because Valentina take care self on stupid
        error prone, repeating task, that is not so easy for developer.
        

2) we get MUCH FASTER solution than based on RDBMS way.
Now you want to know why. Yes? :-)

Because. First of all look how data are stored in that INDEX column for
RDBMS:

    * let you have table with 10-20-50 columns.
    * one of them is INDEX column.

    * when you change order of some group, you get N records to
        update value of INDEX column.

    * How to update record: right!
          DB need load into RAM each such record, with all its fields.
          in fact DB load the whole page where such record is.

    * Now RDBMS can update value of that record, and go to next.

    * Ops. RDBMS need yet jump to index and DeleteOld + AddNew

It is easy see that the bigger record of table, the more data from disk you
need load. I.e. The number of fields in table affect speed of this
operation. 

------------
Valentina instead will touch very precisely only required group of values to
be changed. 


----------
Example in numbers:

    let you have table in million records.
    let avg size of row is 100 bytes only.
    let you need correct index in group of 10,000 records.

    let this 10,000 records located in random order.
        Hmm, I think this is real life case.
        so each 100th record is record to be changed.


* RDBMS *

    We have (1M * 100 byte) / 4K = 25,000 pages for table.

    100 records * 10 bytes = 10KB this is 3 pages of 4K size.
    So we need touch each third page of table.

    We need load about 8300 pages into RAM. Change value,
    and write them back.

      8300 pages * 4 k = 33 MB for read + 33 MB for write.

    PLUS we have probably index for this field.


* Valentina * 

    Valentina need "load - correct - write" in average only 20 Kb.

    Even better: for operations 'Insert At position'
    only one page will be touched.

Now you can count advantage:
        at least 70+ MB / 20 Kb = 3500 times.

IMHO, not bad.  


If take the best case for RDBMS -- all records are located together, we get
10K * 100 = 1MB. So we still win 50 times.


----------------------------------------
3) I can point another advantage, Ed.
  When db engine *knows* something more it can do things more smart.

At least you get advantage, that you can develop some Valentina Database in
REALbasic, but this feature will work correctly and in the same way in other
languages and IDEs. For example Valentina Studio will correctly you show
order of records.
    
Compare to RDBMS. You application know that INDEX field means, and YOUR
application have algorithms to work with it. But other apps believe that
this is just USHORT column.


------------------------------------
4) and even 4th advantage can be pointed.

* in RDBMS way * to update indexed we need lock records of table.

* in Valentina way * we operate on Binary Link, so records of table is not
locked. This can be good for multi-user concurrency.


-- 
Best regards,

Ruslan Zasukhin
VP Engineering and New Technology
Paradigma Software, Inc

Valentina - Joining Worlds of Information
http://www.paradigmasoft.com

[I feel the need: the need for speed]