mai
2012
Quand les fonctions tables surpassent les vues et CTE.
SQL Server établit ses stratégies pour optimiser les requêtes en fonction d’une intelligence artificielle qui manque parfois d’efficacité. Ce sujet aura pour but de démontrer que l’emploie d’une fonction table remplace parfois avantageusement celle d’une vue ou une CTE.
On aime les vues et les CTE :
En effet, les vues et les CTE sont pratiques et faciles à manipuler (puisqu’elles se manipulent comme des tables). On aurait tort de s’en priver et il ne faudrait pas conclure à la lecture de ce sujet qu’elles souffriraient d’un handicap dont la manifestation serait autre que très rare.
Le bât blesse :
Le problème qui peut apparaître lorsque l’on utilise une ou plusieurs vues et/ou CTE en conjonction à d’autres ensembles de résultat est l’incapacité qu’a SQL Server à « raisonner » avec abstraction afin de déterminer des optimisations clés desquelles profiteraient ces vues et/ou CTE.
Exemple :
En considérant NUMBERS une table d’un million d’entier « n » distincts pour lesquels existe un index, imaginez le query suivant :
; WITH CTE AS (
SELECT
n
, ROW_NUMBER() OVER (ORDER BY n) AS Row
FROM VIRTUAL_NUMBERS
)
SELECT
C1.n
, C2.n
FROM CTE AS C1
INNER JOIN CTE AS C2 ON (
C2.Row = C1.Row + 1
)
WHERE C1.n = 500
Ce query qui renverra au plus une seule ligne, mettra un temps plus que significatif à s’exécuter. La cause en est que SQL Server auquel nous soumettons une jointure filtrée en fonction du numéro de ligne et malgré que nous ne récupérions pas ces numéros (4 et 5, 300 et 301, … ?) va déterminer la valeur exacte de ces numéros (de 1 à 1.000.000 et c’est tout un labeur). Si SQL Server était capable d’une abstraction suffisante, il ferait la corrélation entre le filtre sur n (n = 500) et les numéros de ligne dont l’ordre dépendants de n.
Il est temps d’agir !
Le query précédent peut être réécrit comme suit :
SELECT
C1.n
, C2.n
FROM NUMBERS AS C1
CROSS APPLY (
SELECT TOP(1)
C2.n
FROM NUMBERS AS C2
WHERE C2.n > 500 -- ou C1.n lui-même forcément égal à 500
ORDER BY C2.n ASC
) AS C2(n)
WHERE C1.n = 500
Ici, le critère de filtrage est directement ré exploite dans le sous-query et ainsi la performance générale de la requête sera optimale.
Pour peu que ce sous-query ait une certaine propension à se retrouver parmi un nombre significatif de requêtes, on peut souhaiter en éviter la redondance et en définir le comportement une seule et unique fois. Cela peut justement être fait par une fonction table (impossible en CTE ou vue).
La définition d’une telle fonction dans notre cas serait :
CREATE FUNCTION fNEXT(@pN INT)
RETURNS TABLE
AS
RETURN (
SELECT TOP(1)
n
FROM NUMBERS
WHERE n > @pN
ORDER BY n ASC
)
Et notre query deviendrait :
SELECT
C1.n
, C2.n
FROM NUMBERS AS C1
CROSS APPLY dbo.fNEXT(500) AS C2
WHERE C1.n = 500
Row by agonizing row ?
Note : Si vous ingorez ce dont il s’agit, il vous faudra songer à vous renseigner (car ce serait bon pour vous).
RBAR ! Cet anagrame, quasi onomatopé signifiant « Y a mal ! », pourrait venir à la bouche des néophytes qui ont une expérience suffisament longue pour avoir oublier en être (des néophytes). Il est fréquent d’entendre que la lecture de tables dans une fonction provoque systématiquement le phénomène RBAR. Cela est une grosse erreur. SQL Server est bien plus malin que ça*.
Sachez donc que la fonction et son usage tels que décrits offrent des performances excéllentes et ne provoquent pas le RBAR. D’ailleurs je laisse à votre curiosité le query suivant qui vous permettra de vous faire une opinion bien tranchée (mais pas trop, restez critique).
SELECT
C1.n
, C2.n
FROM NUMBERS AS C1
INNER JOIN NUMBERS AS C2 ON C2.n = C1.n + 1
WHERE C1.n BETWEEN 10000 AND 20000
* Si vous êtes dubitatifs, comparez les excécution des deux queries suivants et faites vous une idée :
SELECT
N.N
, X.N
FROM [Demo].[dbo].[NUMBERS] AS N
CROSS APPLY (
SELECT N2.N
FROM [Demo].[dbo].[NUMBERS] AS N2
WHERE N2.N = N.N - 1
) AS X
WHERE N.N BETWEEN 1 AND 500
SELECT
N.N
, X.N
FROM [Demo].[dbo].[NUMBERS] AS N
INNER JOIN [Demo].[dbo].[NUMBERS] AS X ON (
X.N = N.N - 1
)
WHERE N.N BETWEEN 1 AND 500
Conclusions :
Il n’y a parfois pas d’autre alternative pour faire profiter une requête (ou sous requête) d’un ou plusieurs critères de filtrage qu’en employant des fonctions (ou des sous-requêtes) jointes par l’opérateur APPLY. Cette approche n’étant pas en elle-même RBAR, les performances peuvent s’en retrouvées considérablement améliorée.
1 Commentaire + Ajouter un commentaire
Commentaires récents
- Et si on se passait des clés étrangères ? dans
- Quand les fonctions tables surpassent les vues et CTE. dans
- Quelques choses à savoir sur les espaces en fin de chaîne dans
- Assigner des variables et renvoyer un résultat en une seule opération dans
- Quelques choses à savoir sur les espaces en fin de chaîne dans
Le résultat que vous souhaitez obtenir n’a rien à voir avec la récursivité utilisée dans votre CTE (CTE ne veux pas dire récursif au passage….)???
Vous pouvez appliquer cette logique à n’importe qu’elle auto-jointure si vous allez par là…