Discussion :

[k'n1]

Salut a tous,

apres bien longtemps sans trop me faire entendre, je reviens pour poser quelques questions.. je bosse sur du SQL en ce moment, mais je ne suis pas encore familier avec tout ca, notamment MS SQL Server.

Par avance mes excuses si le sujet est traite de facon identique ailleurs, je n'ai rien trouve en FAQ / lien PDF / recherche google (ou presque). J'ai tente de faire un titre aussi clair que possible pour aider ceux qui chercheraient sur google la meme chose que moi...

Passons au sujet. Voila ce que j'ai appris au cours de mes recherches :

Type des fonctions

• Scalar UDF : on renvoie des type de base (entier, varchar...), pratique dans une definition de table pour creer un attribut qui s'informe automatiquement (un peu redondant en volume de donnees mais permet de simplifier ses requetes d'insertion puis de selection - selon moi)
• Inline Table Value UDF : on renvoie une table, alternative aux vues
• Multi Statement UDF : on retourne une table "faite maison", avantage d'une grande flexibilite et complexite face a une vue.

inconvenient fonctions :

• on ne peut pas faire appel a des fonctions indeterministes dans une fonction. on peut passer outre ceci en mettant la fonction indeterministe en parametre d'entree si on en a besoin un nombre fini de fois.
• problemes de performance ? (a confirmer car j'ai juste lue une phrase sans argumentaire)
• probleme des fonctions indeterministes

avantage fonctions :

• on peut faire des calculs sur les parametres d'entree dans la ligne d'apel a la fonction (contrairement a une procedure)
• Plus de possibilites a pas mal de niveaux, notamment des appels tres flexibles (appel dans une clause select, dans une clause from pour les 2 derniers types, dans une definition de table...)

Conclusion
A vrai dire, de ce que j'en ai lu, j'ai limite l'impression que ces fonctions sont vraiment geniales. Eventuellement je vois un petit soucis du cote des getdate() qui limitent a des sections de code pas enorme, mais a part ca...

voila, je suis preneur de toute information complementaire pour confirmer ou infirmer ce que j'ai trouve, en esperant que ca vous inspire (notamment les plus experimentes ^_^), merci d'avance

[SQLpro]

Limitation des fonctions :

Une fonction ne peut JAMAIS comporter une transaction !

C'est normal car une fonction peut être utilisée dans une requête qui est elle même une transaction.

Utilisation des fonctions :

La présence d'une fonction dans une requête interdit toute optimisation possible du prédicat comportant la fonction.

La présence d'ne fonction table dans une requête interdit toute optimisation sur les lignes de la table produite par la fonction

C'est donc très contre performant en général d'utiliser les fonctions...

Les fonctions doivent donc être réservées à l'obtention de "petites" données :
valeur scalaires, tables de faible volume (quelques lignes).


A +

PS : si vous venez à mon cours d'optimisation de MS SQL Server à ORSYS, vous y découvrirez ce genre de choses... http://www.orsys.fr/pdfCours/SQO.pdf

[k'n1]

Ok, je vois, merci pour les infos.

Du coup, pour peu que l'on conserve des faibles flux de donnees et des petits bouts de code, on peut se servir de tout ca sans perte de performance n'est-ce pas ?

je vais lire le PDF pour mieux voir de quoi il en retourne..

En ce qui concerne le cours, merci mais je crois que j'aurais du mal a parcourir 6000 kilometre chaque soir pour venir y assister

[k'n1]

Utilisation des fonctions :

La présence d'une fonction dans une requête interdit toute optimisation possible du prédicat comportant la fonction.

La présence d'ne fonction table dans une requête interdit toute optimisation sur les lignes de la table produite par la fonction

J'ai une autre interrogation a ce propos : si tu as 2 structures totalement identiques deja optimisees, et que tu decides de les reunir en une seule fonction pour alleger un peu le code, ton optimisation est fait n'est-ce-pas ?

par consequent, quand tu parles d'impossibilite d'optimisation, tu consideres le cas ou l'on ecrit une fonction a usage general (et donc contenant des infos inutiles notamment) ?

[SQLpro]

Soit plus précis. C'est quoi une structure ???

A +

[k'n1]

Autant pour moi, je n'utilise pas vraiment le bon vocabulaire

Tu parle d'impossibilite d'optimisation, mais si tu as 2 portions de codes identiques (contenues dans des procedures differentes), et que tu factorise tout ca en creant une fonction dont l'unique utilite est de regrouper ces lignes de code similaires, tu ne perds rien en efficacite n'est-ce pas (et donc ton code est aussi optimal que si tu n'avais rien fait, mais ca permet de normaliser un peu ton code - comme dans n'importe quel langage de prog en fait), n'est ce pas ?

Par contre maintenant que je me relis, j'ai un peu de mal a comprendre le lien entre ce que je dis et ce que je cite -_-... jolie memoire ^_^

[rudib]

Bonjour,

Un moteur SQL ne peut vraiment être comparé à un compilateur ou un interpréteur de langage procédural/objet. La différence est que le moteur SQL a des informations sur les données qui doivent être traitées et sur le système sur lequel il s'exécute. Par exemple il maintient des statistiques de distribution de valeurs dans des index ou des colonnes, qui lui permettent, avec un code SQL donné, de construire un plan d'exécution optimisé.

Sachant cela, il est important en écrivant ton code de faciliter le travail de l'optimiseur pour qu'il puisse créer le meilleur plan d'exécution possible. Si tu écris un seul SELECT, l'optimiseur a la possibilité de comprendre les relations entre les tables. Si tu déplaces une partie de ce SELECT dans une fonction, tu vas obliger l'optimiseur à passer dans ta fonction pour chaque ligne retournée par le SELECT, ce qui peut être très contre-productif.

C'est principalement en cela que les fonctions sont à double tranchant.

[SQLpro]

effectivement ce que dit rubid est PRIMORDIAL !

Je suis tombé exactement sur ce que k'n1 pense être vrai :

si tu as 2 portions de codes identiques (contenues dans des procedures differentes), et que tu factorise tout ca en creant une fonction dont l'unique utilite est de regrouper ces lignes de code similaires, tu ne perds rien en efficacite n'est-ce pas

En l'occurence une procédure comportait de nombreuses requêtes complexes dont trois d'entres elles avaient une sous requête commune. Chacune des requêtes faisait au moins 250 lignes et la sous requête commune près d'une centaine.
Le développeur à alors pensé à utiliser une fonction table multi instruction et encapsuler ainsi la sous requête pensant améliorer sensiblement les performances du traitement. C'est bien entendu en production que l'on s'est rendu compte que cette procédure prenait un temps anormalement long...

En fait l'utilisation dans un tel cas d'une fonction faisait produire une table temporaire dénuée de tout index, obligeait à des écritures du disque et de la copie des données en cache... Bref, tout ce qu'il faut faire pour pourrir lamentablement les performances d'une base de données...
En réintégrant la sous requête originelle dans les requêtes principales, nous avons divisé par 3 les temps de réponse...

A +

[k'n1]

Haaa ok, oui effectivement je l'avais pas vu comme ca.

En fait je ne parlais pas de sous-requete mais un truc du genre

select A
select B
select C

Prendre le "select B" parce qu'on l'a deja ailleur et le coller dans une fonction.

Mais oui, pour une sous-requete, je comprend le principe.

Merci beaucoup pour ces infos tres utiles !

[rudib]

Salut,

Pour faire ce que tu décris (encapsuler un SELECT), tu n'as pas nécessairement besoin d'une fonction, tu peux créer une procédure stockée que tu appelles avec un EXEC.

Ca peut être une meilleure décision: certains ont noté des baisses de performance dans les fonctions (bien qu'elles soient cachées et pré-optimisées comme les sp), et des difficultés à tracer leur activité... C'est du ouïe-dire, je n'ai pas de données claires sur le sujet.
En restant sur les valeurs sûres, tu te simplifies la vie.

Et dans ce cas, effectivement, tu utilises un principe de modularisation de ton code qui peut être utile pour les performances. Cela te permet de profiter de plan d'exécution optimisés dans certains cas.
Dans un exemple comme

Code :

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

1
2
3
4
5
6
7
 
IF ...
    SELECT A
ELSE IF ...
    SELECT B
ELSE
    SELECT C

dans une sp, le premier appel à la sp détermine sa compilation. Si tu tombes dans la cas où tu fais un SELECT C, le plan d'exécution des deux premiers SELECT sera aussi caché, avec les paramètres que tu as envoyé qui vont pour le cas C... Ce qui peut être très contre-performant.

En encapsulant ces SELECT dans une autre sp, tu t'assures que chaque plan sera compilé au bon moment. Et en plus, si tu veux l'appeler avec un WITH RECOMPILE, tu ne recompileras que ce SELECT, au lieu de toute la sp appelante.

[k'n1]

Hmm, en realite, je n'attendais pas vraiment d'infos supplementaires, j'ai eu tout ce qui correspondait a ma question.

Rudib, ta reponse est interessante, merci pour les infos, meme si c'est plus tout a fait du domaine de ce que je demandais (je ne cherche pas a encapsuler quoi que ce soit, je cherchais juste a produire un petit rapport vite fait sur les avantages et inconvenients, et modes d'utilisations, des fonctions par rapport aux procedures pour un collegue..).

Merci quand meme

je vais mettre "resolu" pour eviter de nouvelles confusions, desole..