On Wed, Dec 23, 2020 at 6:56 PM Guyren Howe <guyren@xxxxxxxxx> 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.
