[Info-vax] CRTL and RMS vs SSIO
Dave Froble
davef at tsoft-inc.com
Wed Oct 13 22:54:34 EDT 2021
On 10/13/2021 8:31 PM, Arne Vajhøj wrote:
> 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
>
>
Gee, I'm sure glad I never tried Pascal.
Ok, the gauntlet has been thrown down. I accept. I will attempt to
better define the task, then implement it, in Basic, using my tools, and
my coding style. Give me a day or two.
Task definition
Select customers with shipped orders
Select orders with total dollars shipped >= 10
Sort by customer name, then by dollars, descending
Limit output to 3 orders per customer
print customer name and total dollars per order
Is that it?
--
David Froble Tel: 724-529-0450
Dave Froble Enterprises, Inc. E-Mail: davef at tsoft-inc.com
DFE Ultralights, Inc.
170 Grimplin Road
Vanderbilt, PA 15486
More information about the Info-vax
mailing list