Discussion :

[cfeltz]

Bonjour,

J'ai quelques soucis d'optimisation sur une application en cours de développement. Celle-ci est divisée en deux parties bien distintes :
- une partie batch qui importe chaque nuit un fichier d'environ 200.000 lignes et réalise un certain nombre de traitement sur l'ensemble des infos de la base,
- une partie application web nécessitant l'utilisation de requêtes relativement complexes que nous avons encapsulés dans des vues (chaque utilisateur n'a accès qu'à un sous ensemble de la base de données) et qui permet de faire quelques mises à jours (généralement ciblées sur un nombre de ligne très restreint)

Je voudrais déjà qu'on me certifie un certain à priori que j'ai - à savoir qu'il est plus rapide de passer par l'utilisation des vues de réexécuter à chaque fois les requêtes qu'elles encapsulent.

De plus, j'ai déjà entendu parlé de vues indexées mais je n'en ai jamais utilisé. A priori, les vues devraient utiliser les mêmes index que ceux définis dans les tables utilisées. Je me trompe ? Dans le cas contraire que dois-je savoir en particulier avant de créer des index sur ces vues ? Dois-je les considérer comme des tables ?

Dois-je réaliser des actions particulières pour optimiser l'utilisation du web. Quite à dégrader les performances du batch ?

Merci d'avance pout tout élément de réponse.

Christophe Feltz

[tomttf]

Il est en effet possible de créer sur une vue un index unique en cluster ou non qui améliore l'accès aux données même si les requêtes sont très complexes.
Une vue à laquelle est associé un index en cluster unique est appelée vue indexée.

Dans le cas d'une vue non indexée, cette dernière est matérialisée au moment de l'exécution. Tous les calculs, tels que les jointures ou agrégations, sont réalisés lors de l'exécution de chaque requête référençant la vue.
Si on crée un index en cluster unique sur la vue, l'ensemble des résultats est immédiatement matérialisé et conservé en mémoire physique dans la base de données, évitant ainsi d'utiliser un temps précieux au moment de l'exécution.

Concernant l'optimisation jette un coup d'oeil aux droits comme tu as l'air d'avoir pas mal d'utilisateurs. Avoir des droits trop complexes ca n'aide pas pour les perfs.
Sinon, et d'une manière générale, utilise le profiler pour voir sur quoi porter tes efforts en terme d'optimisation.

[cfeltz]

Il me semblait en fait que la création d'une vue permettait en partie d'éviter l'exécution de la requête... ou plus précisemment de réduire ses temps d'exécution.
Cependant cela apparaît être le cas avec les vues indexées. Et donc avec un Index cluster. En général je n'utilise des index cluster que sur les clés de mes tables qui sont des auto-incréments.... mais je pense que dans ce cas c'est plus que justifié.
Dans mon cas je vais sans doute devoir mettre cet index cluster sur un (voir sur un couple) champ de ma vue... Cela risque de faire tomber les performances de mon batch car il fait bcp d'insertion (pas d'IHM.. donc pas trop grâve), mais comme ces champs ne sont jamais modifiés par l'application web il semblerait qu'il s'agit de la bonne solution.
Je vais également essayer de mettre un index non cluster sur un des autres champs qui est bcp utilisé dans les clauses WHERE.

En fait je pense qu'il y a encore qq points qui peuvent être améliorés question optimisation et le profileur me sera sans doute d'une grande utilitée (bien que je n'y aurais pas accès sur les machines de recette et de prod.). Mais je voulais déjà traiter ce premier point noir qui paraît évident.

Je suppose que chaque ordre d'insert dans les tables utilisées par les vues entrainera un traitement en rapport avec la vue. Est-il possible de lui dire en quelque sorte de ne pas s'en occuper durant le temps du batch (de nombreuses tables utilisées par les vues sont presque intégralement vidées puis réalimentées par le batch en fonction du fichier d'import).

Merci en tout cas pour ta réponse éclairée.
Si tu connais un site qui explique clairement le fonctionnement des vues et des vues indexées je suis preneur.

J'ai notamment, il y a peu, remarqué un comportement relativement étrange avec l'utilisation des vues que je n'arrive pas vraiment à m'expliquer.
J'avais supprimé l'ensemble de mes tables tout en concervant les vues. J'ai réalimenté mes tables avec les mêmes données et lorsque j'accédais aux données par le biais de mes vues certains champs étaient intervertis alors que je nomme toujours les champs (aucune utilisation de *). J'ai été obligé de supprimer également les vues pour les recréer.

Cordialement

Christophe Feltz

[SQLpro]

Il me semblait en fait que la création d'une vue permettait en partie d'éviter l'exécution de la requête... ou plus précisemment de réduire ses temps d'exécution.

Il n'y a aucune différence entre une vue et la requête directe à ce niveau.

Pour les vues indexées, le gain est intéresant dans certaines conditions :
1) le nombre de ligne du résultat de la vue est très inférieur au nombre de ligne des tables en jeu (restriction)
2) le volume global des données restitué est bien plus faible que le volume global de l'ensemble des colonnes en jeu (projection)
3) il y a des calculs d'agrégats.
Si aucune de ces conditions n'est respecté, la vue indexée peut être pire.

Je suppose que chaque ordre d'insert dans les tables utilisées par les vues entrainera un traitement en rapport avec la vue. Est-il possible de lui dire en quelque sorte de ne pas s'en occuper durant le temps du batch (de nombreuses tables utilisées par les vues sont presque intégralement vidées puis réalimentées par le batch en fonction du fichier d'import).

Non, mais rien ne vous empêche de supprimer la vue préalablament et la reconstruire après.

Si tu connais un site qui explique clairement le fonctionnement des vues et des vues indexées je suis preneur.

http://www.sqlteam.com/item.asp?ItemID=1015

A +

[cfeltz]

Pour les vues indexées, le gain est intéresant dans certaines conditions :
1) le nombre de ligne du résultat de la vue est très inférieur au nombre de ligne des tables en jeu (restriction)
2) le volume global des données restitué est bien plus faible que le volume global de l'ensemble des colonnes en jeu (projection)
3) il y a des calculs d'agrégats.
Si aucune de ces conditions n'est respecté, la vue indexée peut être pire.

Salut, merci pour les infos.
Petite précision concernant mes vues : il s'agit de jointures...
En gros, je récupère tous les éléments de ma table <Elements> puis je fais une jointure avec une table définissant mes droits (défini par un ensemble de 400 'types' d'utilisateurs pour simplifier : en réalité le calcul est nettement plus compliqué et entraine l'utilisation d'une UNION et d'un enchaînement de plusieurs jointures).

Afin d'aléger au maximum le volume des données transférées, seules les deux codes permettant de définir le 'type' d'utilisateur et l'identifiant de la table <Elements> sont renvoyés par la vue.
Il me suffit alors au final (depuis l'application web) de faire une jointure entre la vue contenant le triplet de codes et la table <Elements> pour connaître l'ensemble des infos des éléments que l'utilisateur à le droit de voir.

Il faut également savoir que l'application web n'ajoute pr d'éléments (le process d'ajout tournant la nuit par batch : import BCP).

Non, mais rien ne vous empêche de supprimer la vue préalablament et la reconstruire après.

Bonne idée en effet, ou plus simplement, supprimer l'index, puis le remettre à la fin des traitements.

Je me pose cependant une question. Les mises à jour de la table <Elements> n'impactant pas la vue seront ralenties par le fait qu'il s'aisse d'une vue indexée ?

[SQLpro]

non.

A +

[cfeltz]

Bonjour,
je viens de mettre en place mes vues indexées... mais ce n'est pas très concluant... ou alors j'ai loupé qqch.

En fait, j'ai volontairement créer deux vues identiques, l'une avec index cluster et l'aure sans.

Je les exécute en même temps avec affichage du plan d'exécution et à ma surprise le plan est exactement le même... et le temps et partagé : 50% / 50%.

Cependant, l'index est bien créé... ca s'en ressent lors des INSERT.
Pour info, mes vues sont ressemble à ça :



CREATE VIEW dbo.VueTest
WITH SCHEMABINDING
AS
SELECT 'GEST' AS P, A.a,
E.e,
E.f
FROM A
INNER JOIN B ON B.b = A.b
INNER JOIN C ON C.b = A.b
INNER JOIN D ON D.d = C.d
INNER JOIN E ON E.d = D.d
WHERE (E.g = B.g AND B.h = 'test')

et l'index est créé ainsi

CREATE UNIQUE CLUSTERED INDEX IDXTest
ON VueTest (P, a, e, f)

[SQLpro]

Comme vous pouvez le constater par vous même, votre vue n'a aucune des conditions que j'ai cité pour quelle soit efficace par une indexation.

A +

[cfeltz]

Ok, merci....
Je m'en doutais un peu mais je voulais faire le test ... la vue ne comporte que 4 champs contre une quinzaine dans chaque table.
Mais je suis étonné de voir que le plan d'exécution est le même...
Merci en tout cas pour les conseils.
++