This article is written by our sponsor nautilus2k.com which is a new startup trying to help other startups building data-driven apps on the Microsoft .NET Core/ Azure tech stack.
I found myself thinking today that SQL is a trap. It maybe complex to write and tune, & you may do it very deliberately with a lot of planning & thought, but in many ways, it is a shortcut we take to finish that page, or get that report completed.
We do the job, and everyone is happy, and all is done, right?
Wrong.
From my experience, we keep coming back to it over & over again. The team which is constantly looking back at features which were “completed”, and fixing them over & over again is an unsuccessful team.
#1 We are running the query on a table which is always increasing in size. So, the query is bound to change over time — by design, as it eventually fails when the row counts increase.
#2 Look at what it is doing within the query: aggregate operations, joins, even cursors.
- IMHO, running all of those operations on-demand, on an ever increasing table is bad design.
If someone runs the same thing a minute later, it will do everything all over again.
A much better design would be to generate aggregates as the data stream comes in via…
