Discussion :

[Pol63]

bonjour

j'ai souvent des tables avec des colonnes dont la valeur dépend d'autre colonne de la meme ou d'autre tables
j'ai donc en général des triggers pour faire les mises à jour

j'ai voulu tenter les colonnes calculées, que j'ai envie de mettre avec persistance car nos tables étant appelées assez souvent par plusieurs clients, j'ai pas envie de perdre de performance
pour des colonnes dont la valeur dépend de colonnes de la meme table, ca marche bien
mais pour les autres, apparemment il faudrait utiliser des fonctions en passant en paramètre un identifiant pour aller rechercher des infos dans d'autres tables, or pour la persistance, il veut des choses déterministes ...

si vous avez une solution faire des colonnes calculées sans utiliser de trigger je suis preneur
(en fait j'ai plusieurs triggers par table, que je ne peux pas mixer et qui font qu'au final pour une mise à jour j'arrive à plus de 15 appels de triggers, dont plusieurs fois les même)

merci

[elsuket]

Bonjour,

En supposant que les fonctions soient déterministes, il resterait quand même un problème de performance, car l'utilisation de fonction provoque un comportement RBAR, et je ne suis pas certain que vous y gagneriez par rapport au trigger.
Si les tables de base sont faiblement mises à jour, il est envisageable de passer par des fonctions.

Pour passer outre les triggers et les fonctions, ne vous est-il pas possible d'utiliser une vue (indexée ?) ?

@++

[Pol63]

les tables sont beaucoup plus souvent lues que mises à jour, et en général une ligne à la fois, donc l'utilisation dans ce cas de fonctions ne serait pas trop pénalisant

une vue avec un ch1*ch2 / ch3 ca sera quand meme moins performant que de lire une valeur directement, je ne pense pas qu'en passant par une vue, les résultats soient mis en cache !

et les triggers after insert,update, ca m'en fait un peu trop à mon gout et ca nous plante certaines choses
(on a aussi des triggers de "trace" sur certaines tables dont celles qui posent soucis, en insère dans une table d'historique tout ce qui est fait, avec en plus l'iduser et la date, donc si ce trigger est appelé plusieurs fois ca rempli un peu trop pour rien (en plus pas dans le bon ordre), ce qui est le cas en ce moment avec nos multiples triggers)

[elsuket]

une vue avec un ch1*ch2 / ch3 ca sera quand meme moins performant que de lire une valeur directement

A voir dans le cas d'une vue indexée

je ne pense pas qu'en passant par une vue, les résultats soient mis en cache !

ça j'en mettrais pas ma main à couper

n a aussi des triggers de "trace" sur certaines tables dont celles qui posent soucis, en insère dans une table d'historique tout ce qui est fait

Si vous êtes sous SQL Server 2008, peut-être que les fonctionnalités Change DataTracking ou Change Data Capture peuvent convenir à votre problématique.
Ces deux fonctionnalités sont peu gourmandes en ressources.

@++

[Pol63]

sql server 2005, et pas toujours en version entreprise (donc pas de vues indexées)

[elsuket]

Exact.
En revanche je reste assez certain ce que vous obtiendriez des performances correctes en utilisant une vue (n'oubliez pas l'option WITH SCHEMABINDING)

@++

[Pol63]

autant que j'ai pris du galon sur certaines choses, pour les vues je ne sais pas du tout ce qu'il se passe derrière ...

m'enfin je vais essayer de me renseigner et faire quelques coup de profiler pour comparer diverses choses ...

[SQLpro]

sql server 2005, et pas toujours en version entreprise (donc pas de vues indexées)

mais si, mais si, quelque soit la version, vous diposez des vues indexées. En revanche ce que vous n'avez que dans la version Enterprise, c'est la substitution automatique par le moteur de requête d'une requête se basant sur les tables par une requête utilisant une vue indexées en substitution, dans le cas ou elle est mathématiquement équivalente !

Et comme on vous le dit, c'est la meilleure et le plus performante des solution, car SQL Server travaille à ce niveau par différence, et non par recalcul !

A +

[Pol63]

moi qui croyais qu'une vue était une simple requete ... je viens de voir qu'en effet on peut ajouter des index, et meme des triggers
et qu'on pourrait meme faire des insert et des update sur des vues !

moi ce que j'aimerais bien savoir, ca n'a pas l'air de se trouver dans msdn, c'est ce qui se passe en coulisse
y a t il des données stockées pour la vue ?? si oui sont elles supprimées à l'arret du moteur sql ? c'est en ram ? sur le disque ?
un index sur une vue, il se modifie en temps réel en fonction des changements dans les tables liées ??


le schemabinding c'est juste pour éviter de faire des conneries sur les tables sans le répercuter sur la vue ou ca apporte quelque chose d'autre ?

[SQLpro]

moi qui croyais qu'une vue était une simple requete

En principe oui, une vue est une simple requête dont on a donné un nom. une vue ne contient pas de données. Les appels à une vue lancent la requête sous-jacente.

... je viens de voir qu'en effet on peut ajouter des index, et meme des triggers et qu'on pourrait meme faire des insert et des update sur des vues !

Les vues ont toujours été "misajourables" à condition que les mises à jour portent sur une seule table à la fois, que les lignes impactées peuvent être retrouvées et qu'il n'y ait pas de transformation de données. Cela fait partie des règles basique des bases de données édictées depuis l'origine par Codd. LIsez le papier que j'ai écrit à ce sujet : http://sqlpro.developpez.com/SGBDR/ReglesCodd/

Pour les vues indexées SQL Server (ou matérialisées Oracle) le principe est de faire de la dé-normalisation automatique en proposant de stocker les données de la vue dans une structure (index) qui peut être synchrone (SQL Server) ou asynchrone (Oracle). Ceci est plus récent et date d'une dizaine d'années.

Enfin, pour des vues complexes qui ne sont pas en principe "misajourables" il est possible de définir des triggers INSTEAD OF afin de faire croire à la possibilité de mise à jour de manière transparente. Dans ce dernier cas on décompose les données des tables inserted et deleted en autant de sous ensemble qu'il y a de tables et on effectue les véritables INSERT UPDATE ou DELETE dans les tables appropriées.
C'est ce que je prône depuis des années dans le concept de développement base de données épaisse, et permet de se passer de tout ORM en plaçant ces éléments du côté du SGBDR. En effet, l'ensemble des vues qui constitue le 4e niveau de schéma de l'ensemble des schémas d'une conception BD, est celui qui doit être utilisé par le développeur et montre des objets complexes à partir de vues ayant des liens multitabulaires.

moi ce que j'aimerais bien savoir, ca n'a pas l'air de se trouver dans msdn, c'est ce qui se passe en coulisse
y a t il des données stockées pour la vue ?? si oui sont elles supprimées à l'arret du moteur sql ? c'est en ram ? sur le disque ?
un index sur une vue, il se modifie en temps réel en fonction des changements dans les tables liées ??

Dans SQL Server les données de la vue indexées sont synchrones et recalculées par différences. C'est pourquoi le calcul doit être déterministe (par exemple pas de calcul d'âge) et précis (par exemple pas d'utilisation de types REAL ou FLOAT). L'index étant une structure persistante, l'arrêt de la machine n'a aucune incidence.
En principe l'un des intérêt majeur des vues est le précalcul des agrégats.

le schemabinding c'est juste pour éviter de faire des conneries sur les tables sans le répercuter sur la vue ou ca apporte quelque chose d'autre ?

C'est une directive de compilation qui informe les objets participant à la vue, la procédure ou la fonction, qu'ils ne doivent pas être modifiés (ALTER, DROP) car le risque est de voir cette vue, procédure ou fonction ne plus fonctionner.

A +

[Pol63]

dans mon cas :
table article avec une colonne coeffble
table elementlist avec une colonne stocknet

actuellement j'ai un colonne stockble dans elementlist et 2 triggers pour la gérer, un sur article en cas de update(coeffble) et un sur elementlist en cas de update(stocknet)

si je retire la colonne stockble de elementlist et que je veux gérer ca par une vue (donc suppression des 2 triggers) quel est le mieux :

- une vue qui reprend tout elementlist + la stocknet * coeffble via une jointure ?
- un vue qui a juste l'id de elementlist et stockble avec un index sur l'id et avec stockble en colonne incluse ? (je dis ca parce que si j'ai bien compris, le fait de mettre un index, on se retrouverait avec une matérialisation et une mise à jour automatique de cet index en cas de modification du coeffble ou du stocknet) et pour l'affichage quand j'ai besoin du stockble je fais une jointure entre elementlist et la vue sur l'id

(le but est d'avoir un select rapide, les mises à jours sont beaucoup moins fréquentes))




autre question qui n'a rien à voir
en cas d'insert/update sur une table, j'ai cru comprendre que la mise à jour est faite en ram, et que c'est un autre thread qui s'occupe de mettre à jour sur le disque et dans le journal de transaction
est-ce bien le cas, ou est-ce que la requete est synchrone avec tout ca et donc plus longue (et les index, ils sont recalculés à quels moment ?)

le soucis c'est que pour bien utiliser un outils je pense qu'il faut connaitre comment il fonctionne en interne, pas seulement comment on utilise ses "membres publiques"
mais sur msdn c'est pas toujours évident de trouver ce genre d'infos ...

[SQLpro]

dans mon cas quel est le mieux :

- une vue qui reprend tout elementlist + la stocknet * coeffble via une jointure ?
- un vue qui a juste l'id de elementlist et stockble avec un index sur l'id et avec stockble en colonne incluse ? (je dis ca parce que si j'ai bien compris, le fait de mettre un index, on se retrouverait avec une matérialisation et une mise à jour automatique de cet index en cas de modification du coeffble ou du stocknet) et pour l'affichage quand j'ai besoin du stockble je fais une jointure entre elementlist et la vue sur l'id

Faîtes les deux, mesurez les IO avec SET STATISTICS IO ON. Sans la description des types des colonnes et la bouille des requêtes c'est impossible à trancher !

autre question qui n'a rien à voir
en cas d'insert/update sur une table, j'ai cru comprendre que la mise à jour est faite en ram, et que c'est un autre thread qui s'occupe de mettre à jour sur le disque et dans le journal de transaction
est-ce bien le cas, ou est-ce que la requete est synchrone avec tout ca et donc plus longue (et les index, ils sont recalculés à quels moment ?)

Un SGBDR travaille en principe exclusivement en RAM. Le disque n'est qu'un épiphénomène rendu nécessaire pour gérer la persistance. Donc les données de l'index sont mis à jour comme tout index au moment de la mise à jour de la table.

le soucis c'est que pour bien utiliser un outils je pense qu'il faut connaitre comment il fonctionne en interne, pas seulement comment on utilise ses "membres publiques"

C'est exactement ce que je dis en intro de mon cours sur l'optimisation de SQL Server que je donne à Orsy !

A +

[Pol63]

je suis passé par sqlprofiler pour avoir les infos :

2 requetes executées chacune 2x (1ere : select de toute la table, 2eme select d'une ligne)

stats pour la 2eme execution pour chaque type de select:

en laissant la colonne dans la table :
36ms 6 reads
0ms 2 reads

avec la vue complete plus le champ calculé :
47ms 20 reads
0ms 4 reads

avec une jointure left entre la table et la vue indexée
37ms 160 reads
0ms 6 reads


donc avec une vue je suis plus long en duration et il y a plus de reads
et je vais garder la vue complète non indexée qui fait moins de reads, les temps restent acceptables


merci à vous 2 pour toutes ses infos !

[SQLpro]

Attention : le temps écoulé n'a aucune signification car il p)rend en compte le réseau et l'affichage IHM.

A +

[Pol63]

sql server au meme en droit que studio management (où sont exécutées les requetes) et meme endroit que profiler (sur serveur de test)
donc ces 2 variables jouent peu dans mon cas ...

[SQLpro]

Si, le seul temps valable est le CPU time.... Vous oubliez que le temps de réponse va aussi considérer les différentes interruption et mise en sommeil du processus. En principe la différence pour d'aussi petites requêtes est de l'ordre de 10 à 100 MILLE !

A +