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

SQL Server : Eviter les colonnes calculées comme la peste [Article] - SQL/NoSQL - Programmation

Marsh Posté le 22-07-2005 à 16:54:35    

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 :D
 

Code :
  1. set nocount on
  3. /* ------ Création de l'environnement de test ------ */
  4. PRINT 'Génération des objets de test'
  5. PRINT 'Table tsttbl'
  6. create table tsttbl (id numeric, val float)
  7. PRINT 'Table tsttbl2'
  8. create table tsttbl2 (id numeric, val float)
  9. PRINT 'Fonction tstFunc'
  10. go
  11. create function tstFunc
  12. (
  13. @id numeric
  14. )
  15. returns numeric
  16. as
  17. begin
  18. declare @tmpid numeric
  19. 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)
  20. return isnull(@tmpid, 0)
  21. end
  22. go
  23. PRINT 'Table tsttbl3'
  24. create table tsttbl3 (id numeric, val as dbo.tstFunc(id))
  25. create table #tmp (id numeric, val numeric)
  26. declare @i int
  28. PRINT 'Alimentation de la table tsttbl'
  29. set @i = 1
  30. while @i <= 10000
  31. begin
  32. insert into tsttbl (id, val) values (@i, cos(sqrt(@i)))
  33. set @i = @i + 1
  34. end
  36. PRINT 'Alimentation de la table tsttbl2'
  37. set @i = 1
  38. while @i <= 10000
  39. begin
  40. insert into tsttbl2 (id, val) values (@i, sin(sqrt(@i)))
  41. set @i = @i + 1
  42. end
  44. PRINT 'Alimentation de la table tsttbl3'
  45. set @i = 1
  46. while @i <= 10000
  47. begin
  48. insert into tsttbl3 (id) values (@i)
  49. set @i = @i + 1
  50. end
  52. /* ------ Bench ------*/
  53. PRINT 'Lancement du bench'
  54. declare @t1 as float
  55. declare @t2 as float
  56. declare @t3 as float
  57. declare @t4 as float
  59. PRINT 'Lancement de la première requête (colonnes calculées)'
  60. -- Temps avant la première requête
  61. select @t1 = datepart(ms, getdate()) + datepart(s, getdate()) * 1000 + datepart(minute, getdate()) * 60000 + datepart(hh, getdate()) * 3600000
  62. insert into #tmp (id, val) (
  63. select top 100 id, val
  64. from tsttbl3
  65. where val != 0
  66. )
  68. -- Temps après la première requête
  69. select @t2 = datepart(ms, getdate()) + datepart(s, getdate()) * 1000 + datepart(minute, getdate()) * 60000 + datepart(hh, getdate()) * 3600000
  71. delete #tmp
  73. PRINT 'Lancement de la seconde requête (requête classique)'
  74. -- Temps avant la seconde requête
  75. select @t3 = datepart(ms, getdate()) + datepart(s, getdate()) * 1000 + datepart(minute, getdate()) * 60000 + datepart(hh, getdate()) * 3600000
  76. insert into #tmp (id, val) (select top 100 c.id, min(tmp.id2) val
  77. 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
  78. where c.id = tmp.id1
  79. group by c.id)
  81. -- Temps après la seconde requête
  82. select @t4 = datepart(ms, getdate()) + datepart(s, getdate()) * 1000 + datepart(minute, getdate()) * 60000 + datepart(hh, getdate()) * 3600000
  84. /* ----- Nettoyage ------ */
  86. drop table #tmp
  87. PRINT 'Supression de tsttbl3'
  88. drop table tsttbl3
  89. PRINT 'Supression de la fonction tstFunc'
  90. drop function tstFunc
  91. PRINT 'Supression de tsttbl2'
  92. drop table tsttbl2
  93. PRINT 'Supression de tsttbl'
  94. drop table tsttbl
  95. PRINT ''
  98. /* ----- Affichage des résultats ------ */
  99. PRINT 'Durée de la première requête :'
  100. PRINT cast(round((@t2 - @t1) / 1000, 2) as varchar) + ' secondes'
  101. PRINT 'Durée de la seconde requête :'
  102. PRINT cast(round((@t4 - @t3) / 1000, 2) as varchar) + ' secondes'
  104. set nocount off


 
Le résultat :
 

Code :
  1. Génération des objets de test
  2. Table tsttbl
  3. Table tsttbl2
  4. Fonction tstFunc
  5. Table tsttbl3
  6. Alimentation de la table tsttbl
  7. Alimentation de la table tsttbl2
  8. Alimentation de la table tsttbl3
  9. Lancement du bench
  10. Lancement de la première requête (colonnes calculées)
  11. Lancement de la seconde requête (requête classique)
  12. Supression de tsttbl3
  13. Supression de la fonction tstFunc
  14. Supression de tsttbl2
  15. Supression de tsttbl
  17. Durée de la première requête :
  18. 61.77 secondes
  19. Durée de la seconde requête :
  20. 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 :D)
 
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:

Reply

Marsh Posté le 22-07-2005 à 16:54:35   

Reply

Marsh Posté le 22-07-2005 à 16:58:20    

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 ==!
Reply

Marsh Posté le 22-07-2005 à 17:07:16    

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  :D

Reply

Marsh Posté le 22-07-2005 à 17:08:16    

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 :D

Reply

Marsh Posté le 25-07-2005 à 08:13:00    

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

Reply

Marsh Posté le 25-07-2005 à 09:18:01    

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.

Reply

Marsh Posté le 25-07-2005 à 09:27:29    

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 :
  1. # insert into #tmp (id, val) (
  2. # select top 100 id, val
  3. # from tsttbl3
  4. # 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
Reply

Marsh Posté le 25-07-2005 à 09:43:42    

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.

Reply

Marsh Posté le 25-07-2005 à 09:46:31    

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 :D). 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 :/

Reply

Marsh Posté le 25-07-2005 à 11:32:28    

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 ;)

Reply

Marsh Posté le 25-07-2005 à 11:32:28   

Reply

Marsh Posté le 25-07-2005 à 11:48:54    

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

Reply

Marsh Posté le 25-07-2005 à 12:39:55    

Code :
  1. Génération des objets de test
  2. Table tsttbl
  3. Table tsttbl2
  4. Fonction tstFunc
  5. Table tsttbl3
  6. Alimentation de la table tsttbl
  7. Alimentation de la table tsttbl2
  8. Alimentation de la table tsttbl3
  9. Lancement du bench
  10. Lancement de la seconde requête (requête classique)
  11. Lancement de la première requête (colonnes calculées)
  12. Supression de tsttbl3
  13. Supression de la fonction tstFunc
  14. Supression de tsttbl2
  15. Supression de tsttbl
  17. Durée de la seconde requête :
  18. 27.95 secondes
  19. Durée de la première requête :
  20. 61.02 secondes


 
Grossomodo les mêmes résultats donc. ;)

Reply

Marsh Posté le 25-07-2005 à 13:01:59    

merci ;)

Reply

Sujets relatifs:

Leave a Replay

Make sure you enter the(*)required information where indicate.HTML code is not allowed