Discussion :

[Babyneedle]

Bonjour,

Je révise le code d'un développeur junior et je vois cette 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
18
19
20
21
22
23
24
25
26
27
28
29
30
31
ALTER FUNCTION [dbo].[RM_GetAvgDaysToPay] 
(
	-- Add the parameters for the function here
	@CompanyID int
)
RETURNS float
AS
BEGIN
	-- Declare the return variable here
	DECLARE @AvgDaysToPay float
 
	-- Add the T-SQL statements to compute the return value here
	DECLARE @Ansonia float
	DECLARE @Freight float
 
	SET @Ansonia = (SELECT TOP(1) aci.AvgDaysToPay FROM AnsoniaCreditInfo aci INNER JOIN CompanyDocketNumbers cdn ON aci.DocketPrefix = cdn.Prefix AND aci.DocketNumber = cdn.DocketNumber WHERE cdn.CompanyID = @CompanyID AND cdn.Purpose IN (1,3) ORDER BY cdn.Purpose)
 
	SET @Freight = (SELECT TOP(1) ci.AvgDaysToPay FROM CreditInformation ci INNER JOIN CompanyDocketNumbers cdn ON ci.DocketPrefix = cdn.Prefix AND ci.DocketNumber = cdn.DocketNumber WHERE cdn.CompanyID = @CompanyID AND ci.DocketNumber IS NOT NULL AND ci.DocketPrefix IS NOT NULL AND cdn.Purpose IN (1,3) ORDER BY cdn.Purpose)
 
	SET @AvgDaysToPay =
		CASE
			WHEN @Ansonia IS NULL AND @Freight IS NULL THEN NULL
			WHEN @Ansonia IS NOT NULL AND @Freight IS NOT NULL THEN CONVERT(float,(@Ansonia + @Freight)) / 2
			WHEN @Ansonia IS NOT NULL THEN @Ansonia
			WHEN @Freight IS NOT NULL THEN @Freight
		END
 
	-- Return the result of the function
	RETURN @AvgDaysToPay
 
END

Je me dis, plutôt que de lancer 2 requêtes et placer des valeurs en mémoire, je vais faire un seule et unique requête:

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
ALTER FUNCTION [dbo].[RM_GetAvgDaysToPay2] 
(
	-- Add the parameters for the function here
	@CompanyID int
)
RETURNS float
AS
BEGIN
	RETURN 
	(	SELECT TOP 1 CASE
					WHEN aci.AvgDaysToPay IS NULL OR ci.AvgDaysToPay IS NULL THEN (ISNULL(aci.AvgDaysToPay, 0) + ISNULL(ci.AvgDaysToPay, 0))
					ELSE (aci.AvgDaysToPay + ci.AvgDaysToPay) / 2
				END AS AvgValue
		FROM CompanyDocketNumbers cdn 
		LEFT JOIN AnsoniaCreditInfo aci ON cdn.DocketNumber = aci.DocketNumber AND cdn.Prefix = aci.DocketPrefix
		LEFT JOIN CreditInformation ci ON cdn.DocketNumber = ci.DocketNumber AND cdn.Prefix = ci.DocketPrefix
		WHERE cdn.CompanyID = @CompanyID
		AND cdn.Purpose IN (1,3) 
		ORDER BY cdn.Purpose)
 
END

Je lance les fonctions côte-à-côte en utilisant les bon vieux:

Code :

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

1
2
SET STATISTICS IO ON
SET STATISTICS TIME ON

1 - Les deux plans d'exécution sont identiques
2 - Les statistiques de TIME et IO sont à toute fin pratique semblables

Mon environnement:

Instance SQL Server 2008R2
Base en mode 90 (2005)

Ma question:

Est-ce que SQL Server arrive à optimiser le code des fonctions (et procédure stockées) à un point tel qu'il peut joindre deux requêtes distinctes en une seule?

Ou bien c'est mon optimisation 'manuelle' qui n'est pas bonne?

Je travaille avec MSSQL depuis la version 7 et j'ai peut-être des habitudes d'optmisation qui sont devenue superflues depuis.

Merci de m'éclairer à ce sujet.

Aussi, si vous avez des sources intéressantes sur la façon sont MSSQL compile les fonctions et l'évolution de ce processus depuis la version 7, j'apprécierais énormément.

[darkelend]

Bonjour,
Il faut éviter de faire des select dans des fonctions scalaires. Ta requête sera exécutée à chaque ligne. C'est totalement contre performant. Soit tu peux faire une fonction table soit une jointure.
Concernant ta question, je n'ai plus le temps de te répondre, je viens de recevoir une demande urgente désolé...

[Babyneedle]

Bonjour,
Il faut éviter de faire des select dans des fonctions scalaires. Ta requête sera exécutée à chaque ligne. C'est totalement contre performant. Soit tu peux faire une fonction table soit une jointure.
Concernant ta question, je n'ai plus le temps de te répondre, je viens de recevoir une demande urgente désolé...

C'est bon à savoir. Depuis 2007, je n'ai utilisé que des ORM pour l'accès aux données alors mes souvenirs de l'utilisation de procédures stockées et fonctions sont plutôt vagues.

Mon réflexe serait de tout migrer vers LINQ + Entity Framework comme pour le reste du projet...

[darkelend]

Mon réflexe serait de tout migrer vers LINQ + Entity Framework comme pour le reste du projet...

Pour moi en terme de performance, je dirais que l'idéal serait procédures stockées dans la base de données + ADO.NET ou LINQ et entity pour les exécuter. Mais il faut se méfier d'entity car si tu lui demandes de faire n'importe quoi, il le fera

[SQLpro]

Contrairement à une pure requête SQL, les fonction de sont jamais optimisable. En effet, une requête est optimisable car SQL possède des statistiques de distribution des données pour chaque table et chaque index. Il peut donc faire des simplification en estimant à l'avance (avant d'exécuter) qu'utiliser tel algorithme ou lire tel ou tel index sera plus performant... Mais il est en revanche impossible de prédire quel sera le résultat d'une fonction... C'est pourquoi pour toute fonction SQL Server donne une évaluation de coût qui est toujours la même et ne dépend pas du coup réel de l'exploitation de cette fonction.
Les coûts de requête à base d'UDF sont donc toujours faux et les UDF constituent en fait de véritables "tous noirs" pour l'optimisation, donnant ainsi l'illusion que la façon d’écrire importe peu...
Comme le dit Darkelend, c'est à éviter... Mais il y a bien pire... Les ORM en particulier !

À me lire : http://sqlpro.developpez.com/cours/b...s-epaisses.pdf

A +

[Babyneedle]

Contrairement à une pure requête SQL, les fonction de sont jamais optimisable. En effet, une requête est optimisable car SQL possède des statistiques de distribution des données pour chaque table et chaque index. Il peut donc faire des simplification en estimant à l'avance (avant d'exécuter) qu'utiliser tel algorithme ou lire tel ou tel index sera plus performant... Mais il est en revanche impossible de prédire quel sera le résultat d'une fonction... C'est pourquoi pour toute fonction SQL Server donne une évaluation de coût qui est toujours la même et ne dépend pas du coup réel de l'exploitation de cette fonction.
Les coûts de requête à base d'UDF sont donc toujours faux et les UDF constituent en fait de véritables "tous noirs" pour l'optimisation, donnant ainsi l'illusion que la façon d’écrire importe peu...
Comme le dit Darkelend, c'est à éviter... Mais il y a bien pire... Les ORM en particulier !

À me lire : http://sqlpro.developpez.com/cours/b...s-epaisses.pdf

A +

Premièrement, merci pour les réponses. Effectivement, ça donne l'impression de trous noirs et c'est pour moi une raison suffisante pour les bannir.

Si on ne tient compte que de la perspective DBA, c'est vrai que les ORM c'est l'enfer. C'est souvent une permission implicite à faire n'importe quoi.

Par contre, je ne suis pas d'accord quand on regarde l'ensemble du tableau. Pour avoir essayé plusieurs approches, je ne crois pas pouvoir me passer des ORM pour les raisons suivantes:

- Il ne fait aucun sens de compliquer le code pour les outils internes qui ne nécessitent pas de performance, par exemple des outils web qui servent à changer de la configuration.

- Les ORM permettent l'utilisation d'outils et patterns qui n'existent pas au niveau BD. Par exemple, pour nos recherches intensives à partir du site web, nous avons ajouté une couche d'interception (Windsor Castle) qui nous permet de redéfinir le support de données au runtime selon l'origine géographique de la requête. Ça rend les scénarios de caches, de support map/reduce et de CDN beaucoup plus flexibles. Au niveau sharding, MSSQL n'a pas suivi la parade.

- Dans le contexte de ressources humaines à Montréal au Canada, les DBA d'expériences sont rares, très rares. Alors il faut déléguer de la responsaiblité à des developpeurs.

- J'ai essayé quelques frameworks de tests unitaires pour MSSQL mais nous sommes encore loin (à moins que je me trompe) de ce qui existe pour le code au niveau de l'implémentation et de l'intégration à TFS par exemple. L'utilisation d'ORM permet un code coverage plus grand.