[Article] SQL Server : Eviter les colonnes calculées comme la peste

Recherche :

Mot : Pseudo : Filtrer
Bas de page
Auteur	Sujet : [Article] SQL Server : Eviter les colonnes calculées comme la peste

Arjuna

Aircraft Ident.: F-MBSD

Bonjour,

Comme d'hab, j'ai rien à foutre (enfin si, mais justement, faut que je trouve un moyen d'optimiser ma base et du coup je fais plus ou moins de la R&D sur SQL Server), et j'ai donc décidé de regarder de plus près les "colonnes calculées".

Qu'est-ce qu'une "colonne calculée" ?
C'est une colonne, dans une table, qui est readonly (overridable par trigger instead of cependant) qui contien non pas une valeur, mais une formule.
Il y a deux types de colonnes calculées :
- Les "internes", qui font un calcul uniquement sur des champs de la même table, par exemple, dans une table "EcritureFinanciere", on aura des champs de données "credit", "debit" et une colonne calculée "balance" faisant la formule "credit - debit". Je n'ai pas testé ce type de champs, à mon avis, mon bench ne s'applique pas, d'autant plus qu'ils sont indexables, ce qui peut être très utile.
- Les "externes", qui portent non pas sur les champs de la table, mais sur une fonction, qui peut aller chercher des données n'importe où (obligé de passer par une fonction, les sous-requêtes étant interdites dans une instruction "create table" ).

A noter :
Microsoft ne communique presque pas sur cette fonctionnalité de SQL Server 2000. Il faut faire la démarche de chercher cette info dans la doc pour en trouver la syntaxe. Mise à part dans les articles sur les index, il n'y a aucune référence vers cette fonctionnalité.

Quand on regarde ça de loin, c'est assez allèchant : si j'affiche souvent "total facture" avec le numéro de facture, plutôt que de faire des jointures et un SUM() puis des group by dans tous les sens, j'appelle juste une colonne calculée dans ma table des commandes, et je retrouve le total mis à jour en fonction des écritures dans le détail.

J'ai donc décidé de faire un petit bench pour voir ce que ça donne.

Accrochez vos ceintures, me suis bien amusé pour le faire

Code :

set nocount on
/* ------ Création de l'environnement de test ------ */
PRINT 'Génération des objets de test'
PRINT 'Table tsttbl'
create table tsttbl (id numeric, val float)
PRINT 'Table tsttbl2'
create table tsttbl2 (id numeric, val float)
PRINT 'Fonction tstFunc'
go
create function tstFunc
(
@id numeric
)
returns numeric
as
begin
declare @tmpid numeric
select @tmpid = min(b.id) from tsttbl2 b, tsttbl a where a.id = @id and a.val > 0 and round(b.val, 4) = round(a.val, 4)
return isnull(@tmpid, 0)
end
go
PRINT 'Table tsttbl3'
create table tsttbl3 (id numeric, val as dbo.tstFunc(id))
create table #tmp (id numeric, val numeric)
declare @i int
PRINT 'Alimentation de la table tsttbl'
set @i = 1
while @i <= 10000
begin
insert into tsttbl (id, val) values (@i, cos(sqrt(@i)))
set @i = @i + 1
end
PRINT 'Alimentation de la table tsttbl2'
set @i = 1
while @i <= 10000
begin
insert into tsttbl2 (id, val) values (@i, sin(sqrt(@i)))
set @i = @i + 1
end
PRINT 'Alimentation de la table tsttbl3'
set @i = 1
while @i <= 10000
begin
insert into tsttbl3 (id) values (@i)
set @i = @i + 1
end
/* ------ Bench ------*/
PRINT 'Lancement du bench'
declare @t1 as float
declare @t2 as float
declare @t3 as float
declare @t4 as float
PRINT 'Lancement de la première requête (colonnes calculées)'
-- Temps avant la première requête
select @t1 = datepart(ms, getdate()) + datepart(s, getdate()) * 1000 + datepart(minute, getdate()) * 60000 + datepart(hh, getdate()) * 3600000
insert into #tmp (id, val) (
select top 100 id, val
from tsttbl3
where val != 0
)
-- Temps après la première requête
select @t2 = datepart(ms, getdate()) + datepart(s, getdate()) * 1000 + datepart(minute, getdate()) * 60000 + datepart(hh, getdate()) * 3600000
delete #tmp
PRINT 'Lancement de la seconde requête (requête classique)'
-- Temps avant la seconde requête
select @t3 = datepart(ms, getdate()) + datepart(s, getdate()) * 1000 + datepart(minute, getdate()) * 60000 + datepart(hh, getdate()) * 3600000
insert into #tmp (id, val) (select top 100 c.id, min(tmp.id2) val
from tsttbl c, (select a.id id1, b.id id2 from tsttbl2 b, tsttbl a where a.val > 0 and round(b.val, 4) = round(a.val, 4)) tmp
where c.id = tmp.id1
group by c.id)
-- Temps après la seconde requête
select @t4 = datepart(ms, getdate()) + datepart(s, getdate()) * 1000 + datepart(minute, getdate()) * 60000 + datepart(hh, getdate()) * 3600000
/* ----- Nettoyage ------ */
drop table #tmp
PRINT 'Supression de tsttbl3'
drop table tsttbl3
PRINT 'Supression de la fonction tstFunc'
drop function tstFunc
PRINT 'Supression de tsttbl2'
drop table tsttbl2
PRINT 'Supression de tsttbl'
drop table tsttbl
PRINT ''
/* ----- Affichage des résultats ------ */
PRINT 'Durée de la première requête :'
PRINT cast(round((@t2 - @t1) / 1000, 2) as varchar) + ' secondes'
PRINT 'Durée de la seconde requête :'
PRINT cast(round((@t4 - @t3) / 1000, 2) as varchar) + ' secondes'
set nocount off

Le résultat :

Code :

Génération des objets de test
Table tsttbl
Table tsttbl2
Fonction tstFunc
Table tsttbl3
Alimentation de la table tsttbl
Alimentation de la table tsttbl2
Alimentation de la table tsttbl3
Lancement du bench
Lancement de la première requête (colonnes calculées)
Lancement de la seconde requête (requête classique)
Supression de tsttbl3
Supression de la fonction tstFunc
Supression de tsttbl2
Supression de tsttbl
Durée de la première requête :
61.77 secondes
Durée de la seconde requête :
28.25 secondes

Interprétation :
Contrairement à ce qu'on pouvait espérer, la requête numéro deux, avec jointures et sous-requêtes pourries est plus de deux fois plus rapide que la version avec colonne calculée.
Au départ, j'avais même testé sur l'ensemble des lignes (environ 2800 réponses), et l'écart était encore plus flagrant (mais j'avais pas envie de faire tourner le bench pendant 2 heures )

Ce que fait ce test :
- Déjà, je n'utilise que des tables non indexées, sans PK ni autre fioritures, qui pourraient "aider" l'une ou l'autre des requêtes. Je voulais un environnement le plus objectif possible, donc sans la moindre optimisation.
- De la même façon, pour des raisons évidentes, je ne voulais pas avoir des données dans la colonne "val" ordonnées de la même façon que la colonne "id". C'est pourquoi j'ai utiliser cos(sqrt(@i)) pour la première table et sin(sqrt(@i)) pour la seconde. J'obtiens donc des données non cycliques qui ne suivent pas de façon évidente l'évolution de @i.

Ensuite, j'ai décidé de partir des fonctions COS et SIN car évoluant toutes deux dans un ensemble réduit, j'avais de grandes chances d'avoir des valeurs égales d'une table à l'autre, même en présence du SQRT().

Le principe des requêtes est le suivant :
-> Obtenir un certain nombre de valeurs de Y en fonction de X pour lequels cos(sqrt(X)) = sin(sqrt(Y))

Pourquoi j'insère les lignes dans une table temporaire ?
-> C'est juste pour faire joli, je n'ai pas trouvé de moyen dans SQL Server pour ne pas afficher le résultat d'une requête... J'ai tester avec l'ordre des deux requêtes inversé, ça ne change pas les résultats, donc ça n'influe pas sur les temps de traîtement.

Voilà voilà

Harko, t'as encore un truc à mettre en favoris :whistle:

Publicité

cesarr89

Arjuna a écrit :

Harko, t'as encore un truc à mettre en favoris :whistle:

C'est pas le seul..
En tout cas merci Arjuna :jap:

---------------
!== Force et honneur ==!

betsamee

Asterisk Zeperyl

c'est bon a savoir
merci d'en faire profiter les autres ca pourra nous eviter des surprises desagreables

mais bon moi de toutes facons j'utilise la "bouse" MySQL

Arjuna

Aircraft Ident.: F-MBSD

c'est vrai que l'avantage de MySQL, c'est que vu qu'il ne sait rien faire, il ne risque pas de faire quelquechose de mal

megadub

Faudrait essayé avec des indexes pour voir ce que ça donne en condition "normale" d'utilisation

Arjuna

Aircraft Ident.: F-MBSD

Le souci avec les index, c'est que d'une fonction à l'autre, le sgbd en tire plus ou moins partie. Donc si on obtiens des résultats différents, ça ne veut pas forcément dire grand chose.

De toute façon, je fais un "round" sur le seul champ qui vaudrait le coup d'être indexé. A partir de là, je ne suis même pas sûr qu'il saurait utiliser l'index.

megadub

Désolé j'ai pas bien lu mais il y a un truc que je ne saisis pas (je ne connais pas SQL Server donc j'ai du mal ).

En quoi les champs sont calculés dans le 1° cas :
c'est bien ça la requête :

Code :

# insert into #tmp (id, val) (
# select top 100 id, val
# from tsttbl3
# where val != 0

Et la deuxiéme requête est très différente, elle fait quoi exactement ?

Je vais essayer de reconstituer la manip sous Oracle pour mieux comprendre

Edit : t'aurait pas oublié tstfunc dans la 1° requête ? T'as essayé chacune des requêtes dans 2 sessions séparées : La 2° requête récupére les blocs en mémoire au lieu du disque normalement non ? Essaye : req1 + req2 + req1 et tu compares les tps de req2 et la 2° exécution de req1

Message édité par megadub le 25-07-2005 à 09:30:27

Arjuna

Aircraft Ident.: F-MBSD

T3 a un champ "VAL" dont la définition est "tstFunc(id)"

=> Lors de la constulation de la colonne VAL, ce dernier vaut le résultat de la fonction "tstFunc()" avec pour paramètre l'ID de la ligne courrante.

Au départ, j'espérais qu'il calcule cette valeur au moment de l'insert (en effet, sous SQL Server, une fonction n'a pas le droit de retourner un résultat qui évolue en fonction du temps, donc c'est une valeur statique), et la mette à jour uniquement lorsque les éléments appelés par la fonction sont mis à jour.

Mais nan, il n'en est rien, il ne stocke pas le résultat de la fonction, mais ne l'éxécute qu'au moment de la requête Select.

Ainsi, entre la seconde requête qui fait un gros calcul, MAIS dans une jointure, et la première qui fait appel à autant de fois la fonction qu'elle retourne de ligne ben... y'a pas photo, c'est la jointure toute pourrie qui gagne.

megadub

j'insiste, je pense que tu n'as pas pris en compte la lecture des blocs en mémoire. Inverse les 2 requêtes et tu verras que la requête soit disant lente est bien plus rapide (normalement ). Ceci étant, il est évident qu'ajouter une couche supplémentaire ralenti le traitement mais là l'écart me parait anormalement élevé. Si c'est pas une subtilité de l'accés mémoire, peut-être est-ce un bug

Arjuna

Aircraft Ident.: F-MBSD

Non non, y'a pas de problème de lectures mémoire machin. Pour preuve, j'ai lancé les requêtes deux fois de suite plusieurs fois et ça n'a rien changé (moins de 10%).

M'enfin si tu y tiens... Je te refais le bench rien que pour des beaux yeux en inversant les deux requêtes

Publicité

megadub

Bah vu ce que tu me dis sur les PS dans l'autre topic je suis moins étonné

Arjuna

Aircraft Ident.: F-MBSD

Code :

Génération des objets de test
Table tsttbl
Table tsttbl2
Fonction tstFunc
Table tsttbl3
Alimentation de la table tsttbl
Alimentation de la table tsttbl2
Alimentation de la table tsttbl3
Lancement du bench
Lancement de la seconde requête (requête classique)
Lancement de la première requête (colonnes calculées)
Supression de tsttbl3
Supression de la fonction tstFunc
Supression de tsttbl2
Supression de tsttbl
Durée de la seconde requête :
27.95 secondes
Durée de la première requête :
61.02 secondes

Grossomodo les mêmes résultats donc.

megadub

merci

FORUM HardWare.fr

Programmation

SQL/NoSQL

[Article] SQL Server : Eviter les colonnes calculées comme la peste

Sujets relatifs
Pb de calcul avec une requête SQL	[Résolu] Condition+SQL
[SQL]fonction de comparaison de chaines	SGBD / ASP : Page tester des procédures stockées SQL Server depuis ASP
[ SQL ] Regrouper et sommer plusieurs lignes selon critères	prob comptage enregistrements SQL
[SQL]trigger simple avant insertion	ACCESS requete SQL
[Vb 2005] Listview - Empecher redimentionnement des colonnes ?
Plus de sujets relatifs à : [Article] SQL Server : Eviter les colonnes calculées comme la peste

Page générée en 0.076 secondes