[Info-vax] VSI roadmap

[Chris Townley]

On 18/08/2023 23:47, Arne Vajhøj wrote:
> 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
> 
> 

I got quite good at optimising Oracle queries - easy under 10, 11, but 
carp for 8. Ingres in its day wasn't bad - in the days of Ingres 6 it 
was said to have the best optimiser.

We ran it on VMS as well!

-- 
Chris