Discussion :

[Sergejack]

Préambule :
Je parle de CTE dans le texte, mais ce qui est dit s’applique aussi bien aux view.



Depuis la version 2005 de Sql Server, avec les CTE (Common Table Expressions) et les possibilités d’en faire des appels récursifs, il n’est pas fou de se dire que presque tout traitement et récupération de données peut se faire par le biais d’une CTE.

Il est en effet possible d’effectuer des opérations très complexes au sein de ces CTE afin de faire tout voire n’importe quoi.

Les CTE nous permettent de définir des sous queries qui peuvent interagir les uns aves les autres.
Nous pourrions par exemple avoir une CTE « A » qui fait une sélection récursive particulière sur une table « X » et une autre CTE « B » qui fasse une sélection particulier sur une table « Y » et, enfin, joindre ces deux CTE pour arriver à notre résultat finale.

Il se pourrait très bien que nous ayons certains filtres portant sur notre résultat final qui pourraient permettre à nos deux CTE d’éliminer une certaines partie de leur travail mais qui hélas ne le font pas car ces filtres ne surviendraient qu’après ces traveaux effectués en « A » et en « B ».

Par exemple imaginons deux CTE « A » et « B ».
« A » et « B » font une sélection de certaines données dans des tables différentes (respectivement « X » et « Y ».
Dans les tables « X » et « Y » se trouvent des dates de début et des dates de fin qui, pour « X », peuvent indiquer des chevauchements.
« A » qui récupère les données de « X » élimine les chevauchements en « fusionnant » les lignes qui se chevaucheraient.
On pourrait imaginer différentes façon de procéder pour « A » mais il est probable (voire certains) que les champs de date de début et de fin ne soient plus SARGABLE (càd dont les index ne soient plus exploitables).

Si « A » et « B » ont une relation établie sur les dates de début et/ou dates de fin, on regretterait que cette relation ne permette pas en « A » de ne travailler que sur les éléments qui serviront réellement à établir notre résultat final.

Pour une question de performance il pourrait être salutaire de remplacer la CTE « A » par une fonction table (fonction renvoyant une table) « F » et dont ou pourrait paramétrer les dates de début et de fin à considérer.

Ainsi, nous aurions notre CTE « B » en relation avec notre fonction « F » paramétré vis-à-vis de B (via une opération de jonction de type APPLY) où les index de « X »seraient utilisés intelligemment.


Voilà, j’espère que malgré l’absence d’exemple exhaustif, cette explication vous permettra de rester vigilant quant aux cas où une fonction table pourra aider les performances de vos queries.

PS: J'attends vos remarques et réflexions pour peut-être rédiger autrement cette explication.

[SQLpro]

Le danger est justement le manque d'optimisation des fonctions table et de toutes les fonctions en général qui ne peuvent agir que presque exclusivement en SCAN de table et non en SEEK d'index !

A +

[Sergejack]

Le danger est justement le manque d'optimisation des fonctions table et de toutes les fonctions en général qui ne peuvent agir que presque exclusivement en SCAN de table et non en SEEK d'index !

A +

Quelle manque d'optimisation ?
Y a t'il quelque chose de particulier qui se fasse dans un query d'une fonction table qui n'aurait pas lieux ailleurs (ou inversément) ?

Attention, il faut bien se rendre compte que je parle de fonction table faites pour le besoin du query !
Et non d'une fonction table lambda qui refait une partie de ce que fait le reste du query, voire pire une fonction row by row.

[zinzineti]

Préambule :
Je parle de CTE dans le texte, mais ce qui est dit s’applique aussi bien aux view.

Si tu peux nous montrer un exemple concret, se sera plus édifiant

Merci d'avance

[Sergejack]

Si tu peux nous montrer un exemple concret, se sera plus édifiant

Merci d'avance

Pour indiquer que CTE et View sont des concepts analogues dans cette explication ?
Si oui, c'est simple :

Une view c'est un SELECT et une CTE c'est un SELECT ^^

Le problème à rédiger un exemple, est que le cas de figure qui pose l'hypothèse problèmatique de départ est forcément assez complexe.
Tout comme on ne pourrait pas parler théologie avec pour seules figures des pommes et des poires.

Mais je vais quand même essayer, laissez moi un moment.

[zinzineti]

Je me suis peut être mal exprimé, sorry
Quand je parle d'exemple c'est par rapport à ce que tu affirmes ici :

cette explication vous permettra de rester vigilant quant aux cas où une fonction table pourra aider les performances de vos queries.

As-tu un exemple concret ?

A+

[Sergejack]

Voici un exemple "concret" :

Imaginons que nous ayaont une table "demoInt" dans laquelle nous avons une série d'entiers positifs sans doublons.
Et que nous ayons une seconde table "demoBorne" danslaquelle nous avons des bornes (minima & maxima) pour nos entiers.

Nous souhaiterions obtenir pour chaque borne une paire d'élément i1 et i2 tel que i1 et i2 sont compris dans la borne et tel qu'il n'existe aucun élément i3 compris entre i1 et i2 (en somme, i2 est le plus petit entier de la table "demoInt) supérieur à i1).

Voici un script pour la création et la population de ces tables :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
 
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[demoInt]') AND type in (N'U'))
DROP TABLE [dbo].[demoInt]
GO
 
CREATE TABLE demoInt (
	i INTEGER PRIMARY KEY
)
GO
 
; WITH RandomNumber AS (
	SELECT
		0 AS i 
		, 0 AS Depth
	UNION ALL
	SELECT
		i + 1 + ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 3
		, Depth + 1
	FROM RandomNumber
	WHERE Depth < 10000
)
INSERT INTO dbo.demoInt
SELECT i
FROM RandomNumber
WHERE i > 0
OPTION (MAXRECURSION 0)
GO
 
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[demoBorne]') AND type in (N'U'))
DROP TABLE [dbo].[demoBorne]
GO
 
CREATE TABLE [demoBorne] (
	mi INTEGER
	, ma INTEGER
	, PRIMARY KEY (mi, ma)
)
GO
 
; WITH RandomNumber AS (
	SELECT
		0 AS mi
		, 100 AS ma
		, 0 AS Depth
	UNION ALL
	SELECT
		mi + 100 + ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 3
		, mi + 200 + ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 3
		, Depth + 1
	FROM RandomNumber
	WHERE Depth < 100
)
INSERT INTO dbo.[demoBorne]
SELECT mi, ma
FROM RandomNumber
WHERE mi > 0
OPTION (MAXRECURSION 0)
GO

En procédant uniquement avec des CTE on pourrait obtenir ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
; WITH CTE AS (
	SELECT 
		i
		, ROW_NUMBER() OVER(ORDER BY i ASC) AS Row
	FROM dbo.demoInt
)
SELECT
	B.mi
	, B.ma
	, i1.i
	, i2.i
FROM dbo.[demoBorne] AS B
INNER JOIN CTE AS i1 ON (
	i1.i BETWEEN B.mi AND B.ma
)
INNER JOIN CTE AS i2 ON (
	i2.Row = i1.Row + 1
)
WHERE i2.i BETWEEN B.mi AND B.ma

On se rend compte qu'il n'est pas nécessaire que SQL Server parcour la table demoInt en deça ou au delà de limite de la borne pour chaque ligne de "demoBorne".
Pourtant SQL Server le fera par nécessité d'évaluer Row.
Par exemple à l'entier i1 qui serait la 1500° ligne un correspondant ie2 qui sera à la 1501° ligne.
On pourrait se contenter d'évaluer un numéro de ligne Row qu'entre les bornes.
Comme ça pour la Borne x nous aurions deux lignes ayant pour numéro de ligne 1 et 2 propre à la borne x et distinct de deux ayant pour numéro de ligne 1 et 2 propre une borne y.

(si vous ne suivez plus, je me suis fait **** pour rien...)

Pour atteindre ce résultat, on pourrait trasnformer notre CTE en sous-query "FROM (SELECT ....) AS SousQuery" mais cela aurait l'inconvénient d'amener de la redondance dans notre query (il faudrait l'écrire deux fois) et cela ne serait même pas possible si notre CTE était récursive (car si une CTE permet la récursivité, il n'est pas possible d'introduire de la récursivité dans un sous query).

Ainsi il peut être préférable (dans des cas plus croustillants que cet exemple trivial et barbare) de définir une fonction table.
Car une fonction table a un avantage redoutable vis à vis d'une CTE ou une View : il est parfois coûteux et ardu (voire impossible) de rendre une CTE ou un View aussi flexible qu'un SELECT paramétrable (et rien n'empêche de faire de la récursivité via des CTE dans une telle fonction).

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fOrderedNum]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fOrderedNum]
GO
 
CREATE FUNCTION dbo.fOrderedNum(@pMinValue INT, @pMaxValue INT)
RETURNS TABLE
AS
RETURN
(
	SELECT 
		i
		, ROW_NUMBER() OVER(ORDER BY i) AS Row
	FROM dbo.demoInt
	WHERE i BETWEEN @pMinValue AND @pMaxValue
)
GO

Notre query peut devenir :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 
SELECT
	B.mi
	, B.ma
	, i1.i
	, i2.i
FROM dbo.[demoBorne] AS B
CROSS APPLY fOrderedNum(B.mi, B.ma) AS i1
CROSS APPLY fOrderedNum(B.mi, B.ma) AS i2
WHERE i2.Row = i1.Row + 1

Ce query sera beaucoups plus rapide.
Et ce query est très lisible.

Encore une fois, cet exemple est trivial (et barbare) car, clairement : une telle réflexion ne sera nécessaire que dans des cas d'une complexité certainement rare.

Voilà, voilou.

[SQLpro]

L'exemple est biaisé par le fait de requêtes récursives inutiles. En effet dans ce cas il est possible de créer une table d'entiers préalable. Inutile des les générer. Donc en production ce cas est inintéressant !

A +

[Sergejack]

L'exemple est biaisé par le fait de requêtes récursives inutiles. En effet dans ce cas il est possible de créer une table d'entiers préalable. Inutile des les générer. Donc en production ce cas est inintéressant !

A +

C'est exactement ce que je fais. Je les génère dans une table et il n'y a pas de récursivité ailleurs.

De plus je dis et maintiens que ce cas est trivial. Si vous, vous le trouvez inintéressant alors synonime s'écrit avec y.

Si l'intérêt de ce que j'écris vous échappe je vais vous dire qui pourrait y être intéressé :

Les personnes qui
1) ont déjà rédiger des view complexes
2) ont dû faire mettre ces view en ralations avec d'autres éléments
3) ont constaté que le résultat n'était pas probant parce que Sql Server ne pouvait pas conclure à des optimisations suffisantes dans les sous couches (barbarisme) de la view
4) ont regretté de ne pas pouvoir forcer des conditions dans ces sous-couches
5) car ils avaient oublié qu'il était possible de faire des fonctions tables.
6) sont encore enclin à repenser leurs habitudes (l'êtes vous Sql Pro ?)

Et, moi même dernièrement en employant une fonction table j'ai boulverser la rapidité d'un de mes query (si vous voulez vous y frottez je suis prêt à mettre la version avant et après; mais attention, là on parlera de concret càd complexe).

Voilà

[SQLpro]

....
Et, moi même dernièrement en employant une fonction table j'ai boulverser la rapidité d'un de mes query (si vous voulez vous y frottez je suis prêt à mettre la version avant et après; mais attention, là on parlera de concret càd complexe).

Attention à faire des tests avec la bonne volumétrie. Si le volume est faible, disons quelques centaines de Mo, alors il y a des chances que la fonction s'avère plus rapide que certaines requêtes sur des tables mal indexées ou des prédicat non sargeable.

En revanche, se pose le problème global de l'optimisation ET le volumétrie. Pour test, toujours avoir une base qui dépasse largement la RAM du serveur...

Il existe bien entendu des contre exemples, mais il ne sont pas légion.

A +

[Waldar]

En regardant le plan d'exécution réel de votre test en lançant les deux requêtes bout à bout, celle avec la CTE consomme 3% et l'autre 97% !

EDIT : Par contre les statistiques sont meilleures avec la fonction table.

Sur le principe je suis d'accord, il ne faut pas se borner à une seule piste et explorer tous les outils possibles pour obtenir les performances attendues.

[Sergejack]

Attention à faire des tests avec la bonne volumétrie. Si le volume est faible, disons quelques centaines de Mo, alors il y a des chances que la fonction s'avère plus rapide que certaines requêtes sur des tables mal indexées ou des prédicat non sargeable.

En revanche, se pose le problème global de l'optimisation ET le volumétrie. Pour test, toujours avoir une base qui dépasse largement la RAM du serveur...

Il existe bien entendu des contre exemples, mais il ne sont pas légion.

A +

Le query que j'ai transcandé (comme j'y vais !) grâce aux fonctions tables se fait sur des DB en production depuis plus de 4 ans.
Je considère personnellement la quantité de donnée importante même si les tables concerné ne font sans doute pas 1GO sur le disque.

Mais si dans 1000 ans, la quantité de donnée inverse la tendance (ce dont je doute), je ferai un errata (sauf empêchement).