Antwoord.

Analytische rapporten met dynamische filtering, groepering en aggregatie over meerdere parameters vormen de kern van BI- en DataWareHouse-applicaties. Voorheen werden dergelijke taken uitsluitend opgelost via statische queries, maar met de groeiende eisen ontstond de behoefte aan dynamische opbouw van SQL ter plekke: het aantal velden en groeperingen wordt bepaald door de gebruiker of de applicatie.

Probleem - de onmogelijk te voorspellen varianten van velden voor groepering en aggregatie. Men moet de queries dynamisch opbouwen, wat kan leiden tot fouten en prestatieverlies bij slechte planning van queries en het gebruik van ongepaste indexen.

Oplossing - implementeer de opbouw van queries met behulp van dynamische SQL (bijvoorbeeld via EXEC of sp_executesql in T-SQL), waarbij de lijst met velden voor SELECT, GROUP BY en de bijbehorende aggregatiefuncties wordt samengesteld uit de parameters van de gebruiker. Het is noodzakelijk om de namen van de velden zorgvuldig te escapen en bescherming tegen SQL-injectie te waarborgen. Voorbeeld voor SQL Server:

DECLARE @select_fields nvarchar(max) = N'customer_id, year';
DECLARE @agg_fields nvarchar(max) = N'SUM(amount) AS total, COUNT(*) AS row_count';
DECLARE @group_by nvarchar(max) = N'customer_id, year';

DECLARE @sql nvarchar(max) = 
    N'SELECT ' + @select_fields + ', ' + @agg_fields + N' FROM sales WHERE sale_date >= @start AND sale_date <= @end GROUP BY ' + @group_by;

EXEC sp_executesql @sql, N'@start DATE, @end DATE', @start='2023-01-01', @end='2023-12-31';

Belangrijke kenmerken:

Flexibele structuur van de query op basis van gebruikersparameters.
Gebruik van veilige dynamische SQL met verplichte parameterisatie.
Begrip van de noodzaak voor indexering van mogelijke velden voor filtering en groepering.

Vragen met een valstrik.

Is het mogelijk om van tevoren één universele query met CASE te maken voor alle mogelijke groeperingen in plaats van dynamische SQL?

Nee, CASE stelt je in staat om berekeningen in SELECT te maken, maar kan geen GROUP BY vormen op dynamisch bepaalde velden - deze moeten nog steeds expliciet worden opgegeven. Een statische benadering is te beperkt voor live BI-analyse.

Moet je SQL-queryparameters gebruiken bij dynamische opbouw van WHERE, of kun je waarden direct in de tekst van de string invoegen?

Gebruik altijd parameters (bijvoorbeeld via sp_executesql), anders loop je het risico op SQL-injectie en worden beveiligde waarden (bijvoorbeeld apostrofen in strings) verkeerd afgehandeld.

Zal een index op alle mogelijke combinaties van velden meteen alle rapporten versnellen?

Nee, het creëren van een samengestelde index over tientallen velden leidt tot een toename van de indexgrootte en een daling van de snelheid van invoegen/updaten, terwijl alleen een strikt beperkt aantal scenario's versneld wordt. Indexeer alleen werkelijk noodzakelijke velden/groepen.

Typische fouten en anti-patronen

Dynamische concatenatie van strings zonder parameterisatie (SQL-injectie)
Afwezigheid van analyse van selectiviteit bij het ontwerpen van indexen
Vorming van GROUP BY zonder rekening te houden met de werkelijke samenstelling van de tabel

Voorbeeld uit het leven

Negatieve case

In een oud BI-rapport zijn 30 groeperingsvarianten "hardcoded" met if-else in de applicatie. Elke nieuwe parameter vereiste een update van de broncode en het indexschema. Voordelen:

Eenvoud van begrip
Snel te realiseren met een klein aantal velden

Nadelen:

Geen flexibiliteit
Moeilijk uit te breiden en te onderhouden

Positieve case

Dynamische SQL gebruikt voor de opbouw van select/group by ter plekke, SQL-code in een apart module, parameters strikt geescaped. Het toevoegen van nieuwe velden is simpelweg een aanpassing van de catalogus, indexen worden toegevoegd op basis van filtersnelheid. Voordelen:

Flexibiliteit, schaalbaarheid
Veiligheid