On Fri, 20 Mar 2020 06:38:41 +0000, The Natural Philosopher wrote:

> No. In the case where I did the biggest job- it was normalising a flat
> database of a few million UK postcodes into a relational one - none of
> these were the problem.
>
... and shouldn't have been one.

> What was the problem was Mysqls inability to create good optimised
> machine code out of SQL statements. Unlike - say - moderb C compilers
> which astound me in their ability to write better assembler than I could
> myself, MySQL is like going back to the first 8 bit C compilers I used.
>
I'm not surprised. MySQL was known as being a limited system which lacked
any form of query optimisation. It and MS Access were both known to be
very limited, especially when the data volume gets large.

The original big three were Informix, Ingres and Oracle, with IBM joining
in later, initially having led the field with System/R, developed by Ted
Codd and Chris Date. Incidently, both have written extremely good books
about the care and feeding of RDBMS systems.

Oracle has always been expensive and seems to need a lot of routine
attention, or so I found when I briefly looked after a site.

I know very little about Informix, never having used it.

Ingres was always pretty good. Quick, easy to manage and with a decent
query optimiser. There was a special University license which was cloned
and became PostgreSQL, which is excellent, free and is currently
maintained and developed. It has a good query optimiser and can be
ignored for weeks or months on end - it just quietly gets on with
automated housekeeping, etc.

Ingres also sold a developers license for version 10 to Microsoft - this
is where Microsoft SQL Server came from.

> On simple queries, yes, bit not on complex ones involving conditional
> selections of selections etc.
>
Try PostgreSQL next time. You'll be pleasantly surprised.

> When I had fished what I wanted ran well, with over a million records
> but it did not use complex queries.
>
Indeed. It was, after all, only MySQL.

> Creating it from the data I started with would have, if I hadn't given
> up trying to do the whole job with SQL and restricted myself to simple
> queries, building enormous linked lists in C - over a gigabyte in size -
> and thinking hard about how I would access the contents.
>
I've done much the same in Java rather than using the Derby RDBMS, but
that was only because I wanted a small and fairly simple in-memory
database behind the covers of a club rostering system I wrote for my
gliding club. The translation from RDBMS terms to Java looks like this:

Row  -> Class with getters, setters and some table-level methods in it

Table -> ArrayList

Index -> TreeMap

and, before you ask, yes I did normalise the data first and then draw an
ERD before cutting any code. It also implements a number of rules about
minimum gaps between duties, not rosterinf members of a glider syndicate
on the same day, etc, etc. Performance is good, with no delays noticeable
during normal duty allocation/deallocation/moves or in switching between
rosters.


--
Martin    | martin at
Gregorie  | gregorie dot org

--- SoupGate-Win32 v1.05