[Info-vax] VSI roadmap

[Arne Vajhøj]

On 8/18/2023 5:08 PM, Jan-Erik Söderholm wrote:
> Den 2023-08-18 kl. 22:39, skrev Dave Froble:
>> Regardless, consider the following:
>>
>> SELECT * From CustomerTable Where Country is "USA"
>>
>> And where country is not a key.
>>
>> In RMS this cannot be done, without scanning all records, and use some 
>> type of mapping of the record definition, CDD, include file, in 
>> program definition. However, if the database contains the record 
>> definitions, then the database would return a recordset of the records 
>> with Country equal to USA.
>>
>> Yes, all records would be scanned internally, but database products do 
>> this rather quickly and efficiently, and transparent to the request.  
>> Would work on any system that supports the database.
>>
>> Just not anywhere as nice with RMS and "external to the database" 
>> record definitions.
> 
> Sure, any query on any column "works" on a relational database table.
> 
> But the performance goes down the drain if there is not an index
> matching the query that is running. The performance then depends on
> the speed of the system and storage, how much was cached and so on.
> 
> Can be fine as long as the table is small, but there is usually
> a "knee" where the cache can't hold the full table, and then the
> runtime first makes a jump and then more or less grows linearly
> with the size.
> 
> The performance with a matching index is only depending on the number
> of matching records, not the total number of records.

For a tree based index lookup should be O(logn) and only O(1) for
hash based indexes.

> The upside is that the query “work” no matter if there is an index or not.
> You will not get an runtime error and you get the expected result.
> 
> Another unrelated point is that production SQL code should never
> have “select-star” for queries. Any select should always specify the
> columns needed for each query. It’s OK for ad-hoc interactive queries.

There were other SQL problems ....

Arne