Discussion :

[StringBuilder]

Bonjour,

Je souhaite connaître les différences de performances (et la raison) entre les différentes solutions suivantes :

Différence de performances entre :
- fonction qui retourne une table
- procédure stockée qui effectue un SELECT
- procédure stockée qui alimente une table temporaire

Fonction qui retourne une table :

Code sql :

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

1
2
3
4
5
6
7
8
9
10
11
 
create function GetCliSigleFromYear(@year int)
returns @res table (cli_sigle varchar(6))
as
begin
	insert into @res select cli_sigle from client where year(cli_naissance) = @year;
	return;
end;
go
 
select * from GetCliSigleFromYear(1990);

=> J'ai pu constater que c'était, et de loin, beaucoup plus lent que les deux autres solutions. Pourquoi ? Pour moi, une fonction ne modifiant pas les données dans la base, elle devrait pourtant être rapide, puisqu'il y a moins de verrous et de vérifications de cohérence à effectuer (??)

Procédure stockée qui fait un select (et donc, ne devrait rien retourner) :

Code sql :

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

1
2
3
4
5
6
7
8
9
 
create procedure SelectCliSigleFromYear(@year int)
as
begin
	select cli_sigle from client where year(cli_naissance) = @year;
end;
go
 
exec SelectCliSigleFromYear 1990;

=> D'après mes tests, c'est ce qu'il y a de plus rapide. Pourquoi ? Déjà, je ne comprends même pas pourquoi ça "retourne" quelque chose !

Procédure stockée qui alimente une table temporaire :

Code sql :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
create procedure FillCliSigleFromYear(@year int)
as
begin
	truncate table ##CliSigle;
	insert into ##CliSigle (cli_sigle) select cli_sigle from client where year(cli_naissance) = @year;
end;
go
 
create table ##CliSigle (cli_sigle varchar(6));
exec FillCliSigleFromYear 1990;
select * from ##CliSigle;
drop table ##CliSigle;

=> Celle là se situe entre les deux, au détail près qu'on démultiplie les accès à la base, notamment lorsque le code est exécuté depuis une application cliente. Comment est-ce possible qu'il soit plus rapide de créer des objets (même temporaires) en base, que d'alimenter directement une variable ???

Sémantiquement, la première solution me semble donc la meilleure. Cependant, d'après mes tests, c'est, et de loin, la plus lente... Pourquoi. Quelle autre solution privilégier ?

[xp_cmdshell]

Bonjour
Voir aussi SQL 2008 – Table Value Parameters Performance

[Serguei_TARASSOV]

StringBuilder,

Vu le code, je dirais qu'une vue ou la fonction table incluse sera beaucoup mieux que la fonction

Code :

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

1
2
3
4
5
6
7
8
CREATE FUNCTION GetCliSigleFromYear(@year int)
RETURNS TABLE
AS
RETURN
   SELECT cli_sigle FROM client WHERE year(cli_naissance) = @year;
go
 
SELECT * FROM GetCliSigleFromYear(1990);

Voir aussi ma notice Table temporaire vs table variable ce que corresponde "fonction table vs table temporaire".

Si la fonction table incluse / vue (choix #1 toujours) ne peut pas être utilisée, il n'y a pas de solution unique et propre puisque cela dépende des volumes et des complexité des requêtes dont les fonctions/tables temporaires font la partie.