[Info-vax] CRTL and RMS vs SSIO

[Arne Vajhøj]

On 10/13/2021 4:53 PM, Dave Froble wrote:
> On 10/13/2021 1:59 PM, Arne Vajhøj wrote:
>> On 10/13/2021 11:09 AM, Dave Froble wrote:
>>> On 10/13/2021 10:04 AM, Arne Vajhøj wrote:
>>>> On 10/12/2021 9:52 PM, Dave Froble wrote:
>>>>> On 10/12/2021 5:10 PM, Arne Vajhøj wrote:
>>>>>> On 10/12/2021 4:42 PM, Dave Froble wrote:
>>>>>>> On 10/12/2021 3:55 PM, Arne Vajhøj wrote:
>>>>>>>> But the money math has changed.
>>>>>>>>
>>>>>>>> I would say that over the last 30 years:
>>>>>>>>
>>>>>>>> RDBMS license cost changed from expensive to free options available
>>>>>>>>
>>>>>>>> RDBMS hardware resource cost changed from expensive to 
>>>>>>>> insignificant
>>>>>>>>
>>>>>>>> writing and maintaining code to manage IDX file cost is more or 
>>>>>>>> less
>>>>>>>> constant
>>>>>>>
>>>>>>> Are you suggesting writing and maintaining code for RDBMS is any
>>>>>>> different?
>>>>>>
>>>>>> You need much less code because the database software does
>>>>>> so much.
>>>>>>
>>>>>> It is a tradeoff - you write much less code but the generic code
>>>>>> in the RDBMS use more CPU and memory.
>>>>>
>>>>> Excuse me, I'm just a dummy, come down out of the hills.  But:
>>>>>
>>>>> 1) Open file
>>>>> 2) Access data
>>>>> 3) Do some work
>>>>> 4) Write/Update data
>>>>> 5) Done
>>>>>
>>>>> and
>>>>>
>>>>> 1) Access database
>>>>> 2) Access data
>>>>> 3) Do some work
>>>>> 4) Write/Update data
>>>>> 5) Done
>>>>>
>>>>> Guess I don't see much difference.
>>>>
>>>> That is because you describe *what* is being done not *how* it is done.
>>>>
>>>> In general you can expect:
>>>>
>>>> data maintenance - replacing a lot of application code with few 
>>>> lines of
>>>> SQL
>>>
>>> You claim that, but I just don't see it.
>>>
>>>> applications with simple queries - slightly less code
>>>>
>>>> application code with complex queries - a lot less code
>>>>
>>>> adhoc just get some numbers - replacing a lot of application code with
>>>> few lines of SQL
>>>
>>> Don't see that.
>>>
>>> Also, I've noticed that doing some things with SQL can be much more
>>> complex.
>>
>> Here is an example of some (Python) code using SQL to
>> join, select, aggregate, sort and limit some data.
>>
>> import sqlite3
>>
>> con = sqlite3.connect('test.db')
>> c = con.cursor()
>> c.execute('''SELECT customers.name,SUM(orderlines.price) AS totalsales
>>              FROM customers
>>              LEFT JOIN orders ON customers.customer_id = 
>> orders.customer_id
>>              LEFT JOIN orderlines ON orders.order_id = 
>> orderlines.order_id
>>              WHERE orders.status = 'Delivered'
>>              GROUP BY customers.name
>>              HAVING totalsales > 10
>>              ORDER BY totalsales DESC
>>              LIMIT 3''')
>> for row in c.fetchall():
>>     print('%s : %d' % (row[0], row[1]))
>> con.commit()
>> con.close()
>>
>> I believe that it would take a lot more code to
>> retrieve data from index-sequential files and
>> do the work on the data.
> 
> Not a lot, however the sorting by customer name would entail some 
> additional code.
> 
> However, reading the order file, doing the selection, look-up customer 
> record, and release to record sort, then read back sorted records is not 
> "a lot more" code.

I tried writing it in Pascal using index-sequential files.

program cmplx(input,output);

const
    MAX_ORDERLINES_PER_ORDER = 100;

type
    orderline = record
                   item : packed array [1..50] of char;
                   price : integer;
                end;
    customer = record
                  customer_id : [key(0)] integer;
                  name : packed array [1..50] of char;
                  address : packed array [1..100] of char;
               end;
    order = record
               order_id : [key(0)] integer;
               customer_id : [key(1)] integer;
               ref : packed array [1..50] of char;
               status : packed array [1..25] of char;
               no_orderlines : integer;
               orderlines : array [1..MAX_ORDERLINES_PER_ORDER] of 
orderline;
            end;

const
    MAX_CUSTOMERS = 10000;

type
    extcustomer = record
                     basis : customer;
                     total_sales : integer;
                  end;
    extcustomerlist = array [1..MAX_CUSTOMERS] of extcustomer;

var
    no_xc : integer;
    xc_list : extcustomerlist;

procedure load_data;

var
    fc : file of customer;
    fo : file of order;
    c : customer;
    o : order;
    i : integer;

begin
    open(fc, 'customer.isq', old, organization := indexed, access_method 
:= keyed);
    open(fo, 'order.isq', old, organization := indexed, access_method := 
keyed);
    resetk(fc, 0);
    no_xc := 0;
    while not eof(fc) do begin
       c := fc^;
       no_xc := no_xc + 1;
       xc_list[no_xc].basis := c;
       xc_list[no_xc].total_sales := 0;
       resetk(fo, 1);
       findk(fo, 1, c.customer_id);
       while not(ufb(fo)) and not(eof(fo)) and (fo^.customer_id = 
c.customer_id) do begin
          o := fo^;
          if o.status = 'Delivered' then begin
             for i := 1 to o.no_orderlines do begin
                xc_list[no_xc].total_sales := xc_list[no_xc].total_sales 
+ o.orderlines[i].price;
             end;
          end;
          get(fo);                  ;
       end;
       get(fc);
    end;
    close(fo);
    close(fc);
end;

procedure sort_data;

var
    i, j : integer;
    tmp : extcustomer;

begin
    for i := 1 to no_xc do begin
       for j := (i + 1) to no_xc do begin
          if xc_list[i].total_sales < xc_list[j].total_sales then begin
             tmp := xc_list[i];
             xc_list[i] := xc_list[j];
             xc_list[j] := tmp;
          end;
       end;
    end;
end;

procedure dump_data;

var
    i : integer;

begin
    for i := 1 to min(no_xc, 3) do begin
       if xc_list[i].total_sales > 10 then begin
          writeln(xc_list[i].basis.name,' ',xc_list[i].total_sales);
       end;
    end;
end;

begin
    load_data;
    sort_data;
    dump_data;
end.

Arne