Re: Is there a good discussion of optimizations?

On Wed, Dec 23, 2020 at 6:56 PM Guyren Howe <guyren(at)gmail(dot)com> wrote:

> I’d like to put together a good video and writeup about what the…
> philosophy behind relational databases is.
>
> Most folks, in my experience, who use relational databases don’t really
> understand the basic theory or even more important the why — the philosophy
> — of what a relational database is and how to get the most out of them. I
> see a lot of folks trying to use SQL in an imperative manner — make this
> temp table, then update it some, then make this other temp table, etc… I
> see this particularly among analysts who for some reason often prefer SQL
> Server. I think this is down to afaict SQL Server having an abominable
> query optimizer.
>

I find temp tables quite helpful to get needed and consistent performance
when doing large data warehouse type queries on source data especially when
it isn't fully & properly normalized. Many row estimates being low because
of correlation with specified client_id and sometimes having 15-25 tables
involved in a report, has meant that temp tables (that are analyzed to
ensure statistics are present) have seemed the best tool for the job.
Perhaps that's all a hack though.

I look forward to when extended statistics may help with join planning and
building out a comprehensive warehouse that facilitates use of simpler
queries, but for now the "imperative straight-jacket" seems to help more
often than it hurts.

>