Fast lookup of bulky "table"

[dn]

On 16/01/2023 08.36, Weatherby,Gerard wrote:
> I think any peformance improvements would have to come from a language change or better indexing of the data.

Exactly!

Expanding on @Peter's post: databases (relational or not) are best 
organised according to use. Some must accept rapid insert/updates. 
Others are about look-ups (data-retrieval).

A basic RDBMS, just as a Python dict, may only offer a single key for 
efficient retrieval.

Postgres and MySQL (for example) enable the establishment of multiple 
and sophisticated indices/indexes, and the aptly-named "views" of data.

If the queries can be grouped according to the manner in which the data 
must be accessed, a view could be built for each. At which time, even if 
every row must be accessed, the retrieval will be made more efficient 
and/or the response better-organised.

Thus, if we have a DB of people. Many retrievals are likely to utilise 
an index on 'name'. However, if at times interest is limited to place or 
suburb, an index and view of such will speed things from O(n). 
Similarly, if a query is only to return people with a dog license.

Some programmers don't realise that SQL can also be used for 
calculations, eg the eponymous COUNT(), which saves (CPU-time and 
coding-effort) over post-processing in Python.

If there are many way to group/examine the data, then this may not be 
possible, but that depends upon the count of views cf the value of 
speedy-response - and bearing-in-mind that the demands for response-time 
may vary by type of query/data-access.

So many variables to consider ...

-- 
Regards,
=dn