[MSSQL] One-shot Upswing ♫ ♪
Reverse-Engineering or Normalization/BI Flattening, Conditional Turbo Charged Staging Tables with In-Memory OLTP, Bulk Insert, Exploratory Pivoting and SQL Performance Tuning…
Not so long ago, a SQL routine that I usually cracked without the slightest scrutiny had driven me on a challenging performance quest! In fact, this article features a simpler version, and I do hope you will benefit from its hands-on !!
The purpose of this tutorial is to present some SQL techniques… Obviously, here and there, you will find enough articles, hunks and chunks that deal in depth with the resulting practices
The issues ?!!
Based on the following list of students grades we simply have to,
- Replace the typos of negatives entries with their absolutes values.
- Calculate the average score for each student with a coefficient of 1/6 for the test subtotals 1, 2, 3 and 4 and 1/3 for the final score.
- Knowing that another typing error is at the origin of the one rank shift above the marks of the 4th test… we must then correct the error without (very challenging indeed !!) using the Lead() function !!
For more convenience and chitchat saving😎, please refer to the links.
So,
our grades table’s definition and populating script is as follows :
Bulk Insert
then,
prior to analysis, let’s apply the 2 steps normalization…
Conditional Memory-Optimized Table Creation
Normalization
We can apply the same recipes to recreate the relational tables, indexes, or even to automate data migration and all the sub-processes of the MERISE model-based just on the fly. Conceptually, the required bricks are exposed right here, up to you to extrapolate, fine-tune, tweak and so on ?!!
So !!
as our rocky data are now nooormalized, sleekly indexed and fields just in a well-shaped suit, we can then start slice and dice…
Exploratory Pivoting
And,
unless abusing the so willing Analytical functions, let’s try simulating the LEAD() one, just in case ?! Yeah, I’m fiercely devoted to the Shadoks motto:
Performance Tuning
with Analytical function (LEAD),
with No Joins,
CTE usage,
another CTE usage with Joins,
and the 🏆 is the co-Related Subquery.
⛔ Before launching the job, save gradesList.csv in drive C:, then run bulkInsert.sql for populating the staging table or the scriptReady for a one-shot processing.
and for the so-beloved lazy ones, the One-Shot Upswing scriptReady ❤️ GO !!