Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Contribuez SQL Server Discussion :

[2005 et +] Les limites des CTE/View : pensons aux fonctions


Sujet :

Contribuez SQL Server

  1. #1
    Membre éprouvé

    Profil pro
    Inscrit en
    juillet 2006
    Messages
    1 448
    Détails du profil
    Informations personnelles :
    Localisation : Belgique

    Informations forums :
    Inscription : juillet 2006
    Messages : 1 448
    Points : 1 203
    Points
    1 203
    Par défaut [2005 et +] Les limites des CTE/View : pensons aux fonctions
    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.
    Most Valued Pas mvp

  2. #2
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    mai 2002
    Messages
    19 546
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 19 546
    Points : 46 248
    Points
    46 248
    Par défaut
    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 +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  3. #3
    Membre éprouvé

    Profil pro
    Inscrit en
    juillet 2006
    Messages
    1 448
    Détails du profil
    Informations personnelles :
    Localisation : Belgique

    Informations forums :
    Inscription : juillet 2006
    Messages : 1 448
    Points : 1 203
    Points
    1 203
    Par défaut
    Citation Envoyé par SQLpro Voir le message
    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.
    Most Valued Pas mvp

  4. #4
    Membre émérite

    Homme Profil pro
    Chargé de Développement et d'Analyse de données
    Inscrit en
    mars 2010
    Messages
    1 278
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Nord (Nord Pas de Calais)

    Informations professionnelles :
    Activité : Chargé de Développement et d'Analyse de données
    Secteur : High Tech - Opérateur de télécommunications

    Informations forums :
    Inscription : mars 2010
    Messages : 1 278
    Points : 2 798
    Points
    2 798
    Par défaut
    Citation Envoyé par Sergejack Voir le message
    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
    Etienne ZINZINDOHOUE
    Billets-Articles

  5. #5
    Membre éprouvé

    Profil pro
    Inscrit en
    juillet 2006
    Messages
    1 448
    Détails du profil
    Informations personnelles :
    Localisation : Belgique

    Informations forums :
    Inscription : juillet 2006
    Messages : 1 448
    Points : 1 203
    Points
    1 203
    Par défaut
    Citation Envoyé par zinzineti Voir le message
    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.
    Most Valued Pas mvp

  6. #6
    Membre émérite

    Homme Profil pro
    Chargé de Développement et d'Analyse de données
    Inscrit en
    mars 2010
    Messages
    1 278
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Nord (Nord Pas de Calais)

    Informations professionnelles :
    Activité : Chargé de Développement et d'Analyse de données
    Secteur : High Tech - Opérateur de télécommunications

    Informations forums :
    Inscription : mars 2010
    Messages : 1 278
    Points : 2 798
    Points
    2 798
    Par défaut
    Je me suis peut être mal exprimé, sorry
    Quand je parle d'exemple c'est par rapport à ce que tu affirmes ici :
    Citation Envoyé par Sergejack Voir le message
    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+
    Etienne ZINZINDOHOUE
    Billets-Articles

  7. #7
    Membre éprouvé

    Profil pro
    Inscrit en
    juillet 2006
    Messages
    1 448
    Détails du profil
    Informations personnelles :
    Localisation : Belgique

    Informations forums :
    Inscription : juillet 2006
    Messages : 1 448
    Points : 1 203
    Points
    1 203
    Par défaut
    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.
    Most Valued Pas mvp

  8. #8
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    mai 2002
    Messages
    19 546
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 19 546
    Points : 46 248
    Points
    46 248
    Par défaut
    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 +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  9. #9
    Membre éprouvé

    Profil pro
    Inscrit en
    juillet 2006
    Messages
    1 448
    Détails du profil
    Informations personnelles :
    Localisation : Belgique

    Informations forums :
    Inscription : juillet 2006
    Messages : 1 448
    Points : 1 203
    Points
    1 203
    Par défaut
    Citation Envoyé par SQLpro Voir le message
    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à
    Most Valued Pas mvp

  10. #10
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    mai 2002
    Messages
    19 546
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 19 546
    Points : 46 248
    Points
    46 248
    Par défaut
    Citation Envoyé par Sergejack Voir le message
    ....
    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 +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  11. #11
    Modérateur

    Homme Profil pro
    Consultant Teradata
    Inscrit en
    septembre 2008
    Messages
    7 929
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 42
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Consultant Teradata

    Informations forums :
    Inscription : septembre 2008
    Messages : 7 929
    Points : 15 862
    Points
    15 862
    Par défaut
    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.

  12. #12
    Membre éprouvé

    Profil pro
    Inscrit en
    juillet 2006
    Messages
    1 448
    Détails du profil
    Informations personnelles :
    Localisation : Belgique

    Informations forums :
    Inscription : juillet 2006
    Messages : 1 448
    Points : 1 203
    Points
    1 203
    Par défaut
    Citation Envoyé par SQLpro Voir le message
    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).
    Most Valued Pas mvp

Discussions similaires

  1. Réponses: 27
    Dernier message: 04/07/2011, 16h57
  2. [Débutant] Problème avec les limites des axes x et y
    Par alexov dans le forum MATLAB
    Réponses: 4
    Dernier message: 18/05/2011, 18h27
  3. Les limites des Applets
    Par dot-_-net dans le forum Applets
    Réponses: 1
    Dernier message: 04/09/2009, 10h38
  4. Productivité : Quelles sont les limites des solutions RAD automatiques / Frameworks ?
    Par benwit dans le forum Débats sur le développement - Le Best Of
    Réponses: 38
    Dernier message: 20/08/2009, 21h22
  5. Les Limites des moteurs de physique
    Par Lsong dans le forum Physique
    Réponses: 13
    Dernier message: 20/08/2007, 14h41

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo