Kitchen Sink à proscrire !

15 mai 2017

Commentaires fermés

Kezako “Kitchen Sink” en T-SQL ? C’est une technique qui consiste à essayer de remplir le plus de conditions possibles dans une requête. Cela se traduit par la répétition de clauses du type WHERE @variable IS NULL OR colonne = @variable. Et là c’est le drame, patatras, on attend encore le résultat… C’est une approche assez courante mais surtout coûteuse en terme de performance sur de gros volume de données. Cette problématique est d’autant plus intéressante qu’elle nous permettra d’aborder d’autres thèmes sous-jacents pour le tuning d’instance et de requête.

Kitchen Sink : version 1

C’est un exemple courant de kitchen sink.

USE [AdventureWorks2014]
GO

SET LANGUAGE English
SET NOCOUNT ON

DECLARE @CustomerId INT = NULL,
		@StartOrderDate DATETIME = '2006-05-01',
		@EndOrderDate DATETIME = NULL,
		@MinTotalDue MONEY = 1000

SELECT 	 COUNT(*)
FROM	Sales.SalesOrderHeader
WHERE  1 = 1
AND		(@CustomerId  IS NULL OR CustomerId = @CustomerId)
AND    	(@StartOrderDate IS NULL OR OrderDate >= @StartOrderDate)      
AND    	(@EndOrderDate IS NULL OR OrderDate <= @EndOrderDate)
AND    	(@MinTotalDue IS NULL OR TotalDue >= @MinTotalDue)
GO

USE [AdventureWorks2014]

SET LANGUAGE English

SET NOCOUNT ON

DECLARE @CustomerId INT = NULL,

@StartOrderDate DATETIME = '2006-05-01',

@EndOrderDate DATETIME = NULL,

@MinTotalDue MONEY = 1000

SELECT COUNT(*)

FROM Sales.SalesOrderHeader

WHERE 1 = 1

AND (@CustomerId IS NULL OR CustomerId = @CustomerId)

AND (@StartOrderDate IS NULL OR OrderDate >= @StartOrderDate)

AND (@EndOrderDate IS NULL OR OrderDate <= @EndOrderDate)

AND (@MinTotalDue IS NULL OR TotalDue >= @MinTotalDue)

Kitchen Sink : version 2

L’exemple ci-dessous est une version un peu plus élégante de kitchen sink syntaxiquement mais qui se traduit quasiment par le même résultat en terme de performance, suivant l’indexation, on le verra plus tard.

USE [AdventureWorks2014]
GO

SET LANGUAGE English
SET NOCOUNT ON

DECLARE @CustomerId INT = NULL,
		@StartOrderDate DATETIME = '2006-05-01',
		@EndOrderDate DATETIME = NULL,
		@MinTotalDue MONEY = 1000

SELECT 	 COUNT(*)
FROM	Sales.SalesOrderHeader
WHERE  1 = 1
AND		CustomerId = ISNULL(@CustomerId, CustomerId)
AND    	OrderDate >= ISNULL(@StartOrderDate, OrderDate)      
AND    	OrderDate <= ISNULL(@EndOrderDate, OrderDate)
AND    	TotalDue >= ISNULL(@MinTotalDue, TotalDue)
GO

USE [AdventureWorks2014]

SET LANGUAGE English

SET NOCOUNT ON

DECLARE @CustomerId INT = NULL,

@StartOrderDate DATETIME = '2006-05-01',

@EndOrderDate DATETIME = NULL,

@MinTotalDue MONEY = 1000

SELECT COUNT(*)

FROM Sales.SalesOrderHeader

WHERE 1 = 1

AND CustomerId = ISNULL(@CustomerId, CustomerId)

AND OrderDate >= ISNULL(@StartOrderDate, OrderDate)

AND OrderDate <= ISNULL(@EndOrderDate, OrderDate)

AND TotalDue >= ISNULL(@MinTotalDue, TotalDue)

Requête dynamique : EXEC

On pourrait se contenter de réécrire la requête comme ceci, en passant directement les valeurs littérales mais est-ce bien raisonnable ?

USE [AdventureWorks2014]
GO

SET LANGUAGE English
SET NOCOUNT ON

DECLARE @CustomerId INT = NULL,
		@StartOrderDate DATETIME = '2006-05-01',
		@EndOrderDate DATETIME = NULL,
		@MinTotalDue MONEY = 1000,
		@SQLQuery AS NVARCHAR(1500)

SET @SQLQuery =  'SELECT  COUNT(*) '
SET @SQLQuery += CHAR(10) + 'FROM	Sales.SalesOrderHeader '
SET @SQLQuery += CHAR(10) + 'WHERE   1 = 1 '

IF @CustomerId IS NOT NULL
	SET @SQLQuery += CHAR(10) + 'AND CustomerId = ' + CAST(@CustomerId AS VARCHAR)

IF @StartOrderDate IS NOT NULL
	SET @SQLQuery += CHAR(10) + 'AND OrderDate >= ''' + CONVERT(CHAR(16), @StartOrderDate, 120) + ''''

IF @EndOrderDate IS NOT NULL
	SET @SQLQuery += CHAR(10) + 'AND OrderDate <= ''' + CONVERT(CHAR(16), @EndOrderDate, 120) + ''''

IF @MinTotalDue IS NOT NULL
	SET @SQLQuery += CHAR(10) + 'AND TotalDue >= ' + CAST(@MinTotalDue AS VARCHAR)


PRINT @SQLQuery
EXEC (@SQLQuery)
GO

USE [AdventureWorks2014]

SET LANGUAGE English

SET NOCOUNT ON

DECLARE @CustomerId INT = NULL,

@StartOrderDate DATETIME = '2006-05-01',

@EndOrderDate DATETIME = NULL,

@MinTotalDue MONEY = 1000,

@SQLQuery AS NVARCHAR(1500)

SET @SQLQuery = 'SELECT COUNT(*) '

SET @SQLQuery += CHAR(10) + 'FROM Sales.SalesOrderHeader '

SET @SQLQuery += CHAR(10) + 'WHERE 1 = 1 '

IF @CustomerId IS NOT NULL

SET @SQLQuery += CHAR(10) + 'AND CustomerId = ' + CAST(@CustomerId AS VARCHAR)

IF @StartOrderDate IS NOT NULL

SET @SQLQuery += CHAR(10) + 'AND OrderDate >= ''' + CONVERT(CHAR(16), @StartOrderDate, 120) + ''''

IF @EndOrderDate IS NOT NULL

SET @SQLQuery += CHAR(10) + 'AND OrderDate <= ''' + CONVERT(CHAR(16), @EndOrderDate, 120) + ''''

IF @MinTotalDue IS NOT NULL

SET @SQLQuery += CHAR(10) + 'AND TotalDue >= ' + CAST(@MinTotalDue AS VARCHAR)

PRINT @SQLQuery

EXEC (@SQLQuery)

Il y a une petite différence par rapport aux deux appels précédents en mode kitchen sink, dans une requête dynamique, le moteur est capable de détecter un index manquant qui compte tenu du prédicat est tout à fait justifié.

Requête dynamique : sp_executesql

Voici une manière plus académique de faire du T-SQL dynamique.

USE [AdventureWorks2014]
GO

SET LANGUAGE English
SET NOCOUNT ON

DECLARE @CustomerId INT = NULL,
		@StartOrderDate DATETIME = '2006-05-01',
		@EndOrderDate DATETIME = NULL,
		@MinTotalDue MONEY = 1000,
		@SQLQuery AS NVARCHAR(1500)

DECLARE @ParameterDefinition AS NVARCHAR(100) = N'@CustomerId INT, @StartOrderDate DATETIME, @EndOrderDate DATETIME, @MinTotalDue MONEY'

SET @SQLQuery =  'SELECT  COUNT(*) '
SET @SQLQuery += CHAR(10) + 'FROM	Sales.SalesOrderHeader '
SET @SQLQuery += CHAR(10) + 'WHERE   1 = 1 '

IF @CustomerId IS NOT NULL
	SET @SQLQuery += CHAR(10) + 'AND CustomerId = @CustomerId'

IF @StartOrderDate IS NOT NULL
	SET @SQLQuery += CHAR(10) + 'AND OrderDate >= @StartOrderDate'

IF @EndOrderDate IS NOT NULL
	SET @SQLQuery += CHAR(10) + 'AND OrderDate <= @EndOrderDate'

IF @MinTotalDue IS NOT NULL
	SET @SQLQuery += CHAR(10) + 'AND TotalDue >= @MinTotalDue'

PRINT @SQLQuery

EXECUTE sp_executesql @SQLQuery, @ParameterDefinition, @CustomerId, @StartOrderDate, @EndOrderDate, @MinTotalDue

USE [AdventureWorks2014]

SET LANGUAGE English

SET NOCOUNT ON

DECLARE @CustomerId INT = NULL,

@StartOrderDate DATETIME = '2006-05-01',

@EndOrderDate DATETIME = NULL,

@MinTotalDue MONEY = 1000,

@SQLQuery AS NVARCHAR(1500)

DECLARE @ParameterDefinition AS NVARCHAR(100) = N'@CustomerId INT, @StartOrderDate DATETIME, @EndOrderDate DATETIME, @MinTotalDue MONEY'

SET @SQLQuery = 'SELECT COUNT(*) '

SET @SQLQuery += CHAR(10) + 'FROM Sales.SalesOrderHeader '

SET @SQLQuery += CHAR(10) + 'WHERE 1 = 1 '

IF @CustomerId IS NOT NULL

SET @SQLQuery += CHAR(10) + 'AND CustomerId = @CustomerId'

IF @StartOrderDate IS NOT NULL

SET @SQLQuery += CHAR(10) + 'AND OrderDate >= @StartOrderDate'

IF @EndOrderDate IS NOT NULL

SET @SQLQuery += CHAR(10) + 'AND OrderDate <= @EndOrderDate'

IF @MinTotalDue IS NOT NULL

SET @SQLQuery += CHAR(10) + 'AND TotalDue >= @MinTotalDue'

PRINT @SQLQuery

EXECUTE sp_executesql @SQLQuery, @ParameterDefinition, @CustomerId, @StartOrderDate, @EndOrderDate, @MinTotalDue

Là encore, l’index manquant est détecté.

Coût respectifs des 4 requêtes

Voici le résultat final en terme de coût, les requêtes dynamiques sont “légèrement” plus performante, 2% de gain mais c’est loin d’être suffisant.

Ajout de l’index manquant

Comme indiqué, pour les requêtes dynamiques, la détection d’index manquant se fait bien. Ajoutons notre index et rejouons nos 4 requêtes. Ha là c’est beaucoup mieux, on obtient un gain significatif sur les 3 derniers appels à savoir :

– Kitchen Sink : version 2
– Requête dynamique : EXEC
– Requête dynamique : sp_executesql

Et si on en profitait pour prendre connaissance du paramètre d’instance Optimize For Ad Hoc Workload et exécutons la requête dynamique EXEC avec une valeur de paramètre différente, @StartOrderDate DATETIME = ‘2006-05-01’. A l’arrivée, on se retrouve avec autant de plans que de valeurs de paramètre distinctes, sans parler de la taille des plans et des compilations à répétition.

SELECT	bucketid, size_in_bytes, plan_handle, text
FROM	sys.dm_exec_cached_plans ecp
CROSS	APPLY sys.dm_exec_sql_text(ecp.plan_handle) sql
WHERE	1 = 1
AND		sql.text LIKE 'SELECT  COUNT(*) %FROM%Sales.SalesOrderHeader%'

SELECT bucketid, size_in_bytes, plan_handle, text

FROM sys.dm_exec_cached_plans ecp

CROSS APPLY sys.dm_exec_sql_text(ecp.plan_handle) sql

WHERE 1 = 1

AND sql.text LIKE 'SELECT COUNT(*) %FROM%Sales.SalesOrderHeader%'

On pourrait se contenter d’activer le paramètre d’instance Optimize For Ad Hoc Workload mais est-ce bien suffisant ? Non car contrairement à la requête dynamique sp_executesql, on ne peut pas bénéficier du mécanisme de cache des requêtes préparées, ni profiter de paramètre en sortie et en plus on est sujet à l’injection SQL. Bref, cela fait beaucoup de concessions au final.

Conclusion

Bien que la syntaxe du T-SQL dynamique soit laborieuse à appréhender et le debug pénible en pratique, c’est une méthode redoutablement efficace au niveau performance. Même si la détection d’index manquant n’est pas à prendre au pied de la lettre, elle est fonctionnelle pour les requêtes dynamiques. Dans le cas présent, cette proposition d’index était pertinente. Et enfin la méthode utilisée pour recourir à une requête dynamique a aussi son importance. Pour finir voici une session co-animé avec David Barbarin sur l’optimisation de requêtes sur la base des plans d’exécution dans laquelle la kitchen sink est évoquée.

Auteur

Sarah Béquet

Archietcte Data Microsoft, les maîtres mots sont : performance, industrialisation, méthodologie & bonne humeur.

Derniers articles

2019.12.02BI & Big Data Etude des contextes en DAX (4/4) : Transition de contexte
2019.11.25BI & Big Data Etude des contextes en DAX (3/4) : Contexte de requête
2019.11.17BI & Big Data Etude des contextes en DAX (2/4) : Contexte de filtre
2019.11.11BI & Big Data Etude des contextes en DAX (1/4) : Contexte de ligne

Articles liés

Différences entre tables temporaires et variable table : ##temp, #temp, @temp... ou CTE ?

Optimize for ad hoc workloads : Requêtes Ad Hoc et valeurs littérales

AlwaysOn

Mode de secours SQL Server (DAC)

Blog