Discussion :

[xpander]

Bonjour,

J'entends souvent parlé des vues indexées, sans vraiment comprendre comment cela fonctionne. Quelqu'un peux t il m'expliquer ou me donner l'url d'un site ou est expliqué le pourquoi du comment des vues indexées ?
J'utilise SQL Seveur 2005.

Merci pour votre aide

Arnaud

[kagemaru]

Bonjour,

les vues indexées peuvent être intéressantes pour matérialiser les données résultantes de l'exécution d'une vue, permettant de ne pas à avoir à ré-exécuter le code de la vue à chaque fois.

en anglais : http://msdn.microsoft.com/en-us/libr...4(SQL.90).aspx
ou en français :
http://msdn.microsoft.com/fr-fr/libr...4(SQL.90).aspx

[SQLpro]

Pour une application intéressante et démonstrative du concept, lisez l'article que j'ai écrit à ce sujet :
http://sqlpro.developpez.com/optimisation/indexation/

A +

[xpander]

Merci pour ces infos ...

Je me rend compte à la suite de la lecture de cet article & quel test rapide que je complique la vie ... aujourd'hui j'ai un modele de données avec un table de fait de plusieurs millions de lignes qui n'est pas requetable en direct pour mon application web frontale (trop long) : j'ai donc creer plusieurs table que j'update toutes les nuits avec les nouvelles lignes en faisant les group qui vont bien pour avoir des réponses rapides sur l'application frontale ... l'utilisation de vues indexées me semble approprié dans ce genre de cas ....

J'ai une question concernants les insert update, comment cela se passe ? A chaque mise a jour de ma table de fait, les index des vues sont recalculés ? Ce qui doit prendre un temps considérable ? Quelle est la bonne méthode, desactivation / reactivation des index avant/apres chaque update / insert ?
A t il d'autres choses a savoir sur la maintenance de ce type d'index ?

merci pour votre aide

arnaud

[SQLpro]

j'ai donc creer plusieurs table que j'update toutes les nuits avec les nouvelles lignes en faisant les group qui vont bien pour avoir des réponses rapides sur l'application frontale ...

Ca c'est ce qu'il y a de pire !
Si vous avez des temps de réponse anormaux c'est que vous n'avez pas posé les bons index !

J'ai une question concernants les insert update, comment cela se passe ? A chaque mise a jour de ma table de fait, les index des vues sont recalculés ? Ce qui doit prendre un temps considérable ?

Non les index ne sont pas recalculés en totalité, mais seulement pour leur delta. et cela ne prend donc que très peu de temps !

A +

[xpander]

Merci pour ces infos.

Par contre je constate que l'on ne peut pas faire de left join si je veux indéxer ma vue ? Uniquement les inner join sont autorisés ?

merci
arnaud

[SQLpro]

Normal. Les vues indexées ont un certain nombre de restrictions due à leur techniques d'optimisation des calculs.

A +

[hussonl]

Bonjour,

je ressort un vieux post qui m'a permis d'obtenir des infos sur un thème qui m'intéressait, les "vues indexées".

J'ai lu avec attention l'étude d'indexation (écrite par SQLPro) et j'ai mis en pratique l'exemple.
Malheureusement, j'ai un résultat qui diffère sur justement la partie "Création d'une vue indexée".

En exécutant la requête :

Code :

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

1
2
3
select nombre, sexe
from V_EMP_SSC
where service='RH'

Au lieu d'obtenir le message :
Table 'V_EMP_SSC' ... logical reads 2 ...

J'ai cela comme message :
Table 'Worktable'. ... lectures logiques 0 ...
Table 'T_EMPLOYEE_EMP'. Nombre d'analyses 3, lectures logiques 35460, ...

Pourquoi n'est-ce pas le nom de la vue qui s'affiche ?

Avez-vous une idée de la raison du problème ?
Pour info, j'utilise SQL Server 2005 SP2.

Merci par avance,
Laurent

[SQLpro]

Tout dépend de votre version de SQL Server.
La substitution automatique des tables aux vues n'est opérationnelle que dans les versions DEVELOPPER et ENTERPRISE. SI vous êtes en version standard, web, ou Express, cette fonctionnalité n'est pas prise en charge. Néanmoins vous pouvez utiliser directement ces vues indexées.

A +

[hussonl]

J'utilise pour l'instant SQL-Server 2005 Standard.

Donc, dois-je déduire de votre réponse que les vues indexées sont utilisables, mais que je ne profite pas de cette évolution en terme de performances ???

Et que si je veux en bénéficier, il me faut obligatoirement migrer vers une des 2 versions Developper ou Entreprise !!

Merci en tout cas,
Laurent

[SQLpro]

Developper étant fait pour développer et enterprise pour produire.

Comprenez que, comme dans le cas de l'indexation online, du recalcul des stats asynchrone, du partitionnement.... la substitution automatique des vues indexées produit des plans de requêtes très complexes et donc un effort de calcul important qu'une petite version (monoproc avec 2 Go de RAM par exemple) ne serait pas à même de produire sainement.
En fait, les résultats pourrait même être inverse : le système passerait trop de temps à ces calculs au détriment de la "vraie" production.
C'est pourquoi ce genre de fonctionnalité est réservée à de grosses base et donc de gros serveur (au moins quadri proc avec 32 Go de RAM...)

Par exemple quand je voit des développeurs sauter à pied joint sur le partitionnement de tables dans MySQL je me dit que nous avons encore de très beaux jours en tant que conseil. En effet, faire du partitionnement de table, n'a de réel intérêt que si une table dépasse la capacité d'un disque du serveur, ce que MySQL par ses limitation est incapable de faire. Cependant les plans de requêtes ainsi que les couts de ces requêtes sont nettement augmenté par cette disposition !
Heureusement que cela est impossible sur la version standard de SQL Sevrer !

A +

[hussonl]

Merci bien pour les conseils.

Je ne suis effectivement pas dans la catégorie des "grosses BDDs" et "gros serveurs".

Serveur bi-proc avec 4 Go de RAM (seulement)
La table principale comporte environ 60 millions de lignes.
La base fait environ 80 Go

En fait, je cherchais une solution pour accélérer mes requêtes.
J'ai constaté la semaine dernière que si je faisais une simple requête SELECT COUNT(*) directement sur la table source, j'obtenais le résultat en 1 à 2 secondes.
Mais lorsque je faisais la même requête par le biais d'une simple vue de cette table, le résultat sortait en 4min environ.

Initialement, j'avais mis en place des vues pour renommer les champs et surtout pour être moins dépendant de la structure de base de la BDD pour laquelle je n'ai pas de maitrise.
Notre fournisseur de logiciel/BDD est libre de modifier la base à sa convenance et vu que j'accède à leur données via un outil de reporting, je ne voulais pas avoir à tout reconfigurer les états à chaque changement de structure de BDD.
D'où l'explication pour les vues.

Du coup, j'explore tout ce qui me semble être une bonne solution.

[SQLpro]

Serveur bi-proc avec 4 Go de RAM (seulement)
La table principale comporte environ 60 millions de lignes.
La base fait environ 80 Go

80 Go n'est pas à mon sens intéressant par exemple pour faire du partitionnement, car il est probable qu'aucune table (sans ses index) ne devrait attendre 73 Go qui est la taille des plus petits disque de serveurs actuellement dispo.
En sus une table ne se mesure pas en ligne, mais en volume. 60 millions de lignes de INT ne font que 228 Mo et tient donc en mémoire.

En revanche, pour votre problème de perf, je trouve que 4Go c'est bien peu pour une telle base. Commencez par adjoindre de la RAM. Votre version de SQL Server peut aller jusqu'à plus soif. Envisagez au moins 8 Go (mais je pense plus, il faudrait auditer). C'est cela avec des bons index qui sera le plus important dans l'immédiat.

En fait, je cherchais une solution pour accélérer mes requêtes.
J'ai constaté la semaine dernière que si je faisais une simple requête SELECT COUNT(*) directement sur la table source, j'obtenais le résultat en 1 à 2 secondes.
Mais lorsque je faisais la même requête par le biais d'une simple vue de cette table, le résultat sortait en 4min environ.

Cela peut être du à différentes problématiques : utilisez vous les préfixes de schéma systématiquement (table et vue) ? Lancez vous plusieurs fois la requête ? Les statistiques sont elles à jour ? La base est-elle en optimisation simple ou forcée ?...

Initialement, j'avais mis en place des vues pour renommer les champs et surtout pour être moins dépendant de la structure de base de la BDD pour laquelle je n'ai pas de maitrise.
Notre fournisseur de logiciel/BDD est libre de modifier la base à sa convenance et vu que j'accède à leur données via un outil de reporting, je ne voulais pas avoir à tout reconfigurer les états à chaque changement de structure de BDD.
D'où l'explication pour les vues.

Du coup, j'explore tout ce qui me semble être une bonne solution.

Continuez dans cette voie. C'est une bonne pratique !

A +

[hussonl]

Malheureusement je suis avec Windows Server 2003 en 32bits, donc pas plus de 4Go possible.
Mais j'ai une licence 64 bits donc je pourrais réinstaller l'OS sur mon serveur hors exploitation cet été.

Vu que j'utilise VMWare, c'est relativement facile de rajouter de la RAM sur une VM (surement moins performant qu'une machine physique, mais j'avais de toutes façons les mêmes problèmes l'année dernière avant de passer en virtuel).
Pour le stockage, on est avec un SAN, donc pas trop de soucis de ce côté là ;-)

Je vais étudier plus en détails dans les prochains jours toutes vos pistes.

Merci encore.

P.S. : j'envisageais aussi la piste de l'audit, donc, si vous connaissez un "bon" auditeur (sur Rhone-Alpes et plus précisément la Savoie), je suis preneur ;-)

[SQLpro]

Malheureusement je suis avec Windows Server 2003 en 32bits, donc pas plus de 4Go possible.

Non, en 32 bits vous pouvez aller jusqu'à 64 Go avec AWE. Lisez l'article que j'ai écrit à ce sujet : http://blog.developpez.com/sqlpro/p5...-awe/#more5858
Il ne faut pas oublier que les disques stockant encore en 32 bits, les lectures et écritures physiques sont plus rapides en 32 bits qu'en 64 bits (conversion oblige), ce qui pour une base fortement transactionnelle est un avantage !

...
Vu que j'utilise VMWare, c'est relativement facile de rajouter de la RAM sur une VM (surement moins performant qu'une machine physique, mais j'avais de toutes façons les mêmes problèmes l'année dernière avant de passer en virtuel).

Voir l'article que j'ai écrit à ce sujet : http://blog.developpez.com/sqlpro/p8...tion/#more8234
MS est encore pire que moi sur le sujet, car dans les techdays 2010 est passé un slide dans lequel les ingé de MS recommande de na pas virtualiser si plus de 50 users et autres conditions dont vous faites visiblement partit !

Pour le stockage, on est avec un SAN, donc pas trop de soucis de ce côté là ;-)

S'il n'est pas bien géré c'est souvent la pire des choses. Lisez les articles que j'ai écrit à ce sujet :
http://blog.developpez.com/sqlpro/p8...t-le-stocakge/
http://blog.developpez.com/sqlpro/p5...fichiers-et-t/

Je vais étudier plus en détails dans les prochains jours toutes vos pistes.

Merci encore.

P.S. : j'envisageais aussi la piste de l'audit, donc, si vous connaissez un "bon" auditeur (sur Rhone-Alpes et plus précisément la Savoie), je suis preneur ;-)

pas donné, mais souvent très intéressant. Lisez ce que j'ai écrit à ce sujet sur mon site professionnel :
http://www.sqlspot.com/sites/sqlspot...eDonnees-3.pdf

A +

[hussonl]

Merci bien pour tous ces documents que j'ai lu en long, large, travers hier.

Ça me permet d'y voir plus clair et surtout, j'aurais plus de facilité (moins de difficulté ??) à comprendre ce qu'un éventuel auditeur pourrait me proposer comme axes d'améliorations.