Re: The joy of Ada

On Sun, 27 Oct 2024 09:46:15 +0000, The Natural Philosopher wrote:

I never bothered learning the more arcane side of SQL because if I ever
managed to get the query right, it was always so abysmally slow that it
was quicker to use a series of simple queries and join them together in
application code rather than SQL

I have found quite the opposite. Remember that, the more information you
put in the query up front, the more clues you give to the query optimizer.

I was maintaining this application for a client, that had been created for
them by a prior developer who knew PHP, but not much else. They didn’t
seem to know anything about SQL joins.

There was a query page that returned timesheet records for a specified
range of dates. It did a separate query for each day in the range, so the
wider the range, the slower the report became. For example, asking for
records over a six-month range was quite painful, since it would be doing
about 180 separate queries.

I figured out a query that would return all the records in the specified
date range in a single sequence. Result: the page now appeared in a flash,
even for date ranges spanning six months or more.

Another possibly useful thing is to learn how to use the EXPLAIN statement
<https://mariadb.com/kb/en/explain/>. If a query is slow, this can be
helpful to figure out where the bottleneck lies.