Tuning SQL - Jointure complète

[Socap]

Bonjour,

J'ai une requête qui prend, à mon avis, trop de temps à d'exécuter par rapport à ce qu'il pourrait en être avec une véritable optimisation. Voici la requête en question, qui s'exécute en un peu plus de 2 minutes :

Code :

1
2
3
4
5
6
7
SELECT
  A.idA,
  B.idB,
  A.champDetail1,
  B.champDetail2
FROM A INNER JOIN B
ON A.idB = B.idB

La table A contient environ 16 millions d'enregistrements, tandis que B en contient 3 millions.
A.idA est la PK de A, et B.idB est la PK de B. Il y a sémantiquement une relation de clé étrangère entre A.idB et B.idB, mais, pour des raisons annexes, cette clé étrangère n'est pas implémentée.
La colonne A.idB a cependant été indexée (nonclustered, autres options par défaut).

Voici l'explain plan de la requête :



Je ne comprends pas pourquoi un scan de la PK de A est réalisé, plutôt qu'un scan de l'index positionné sur A.idB.

Un update statistics a évidemment été réalisé manuellement sur A ainsi que B.

Je ne suis pas un spécialiste du tuning SQL-Server, et j'ai uniquement quelques notions de base sous Oracle.

Merci pour votre aide, je reste à votre disposition pour toute info nécessaire !

[iberserk]

Vous avez un index sur idB (table A)mais il ne permet pas à SQL SERVER de couvrir votre requête puisqu'il doit chercher dans son index cluster (IDA table A) les colonnes champ1 et IDA(tout deux présents dans le SELECT).

SQL doit estimer que le cout de recherche dans l'index cluster est plus lourd que de parcourir votre index sur IDB...

Tout dépend également de la dispersion de IDB dans votre table A...
Crée un index sur A.IDB avec en colonne include champ1 et IDA:

CREATE NONCLUSTERED INDEX IX_IDB ON A (IDB) INCLUDE (champDetail1,IDA)
Vous obtenez un index parfaitement couvrant pour la table A de la requete...

[Socap]

Merci pour cette explication. Effectivement, la création de cet index permet son utilisation dans la requête soumise.

Par contre, côté perfs, j'ai le même temps d'exécution qu'avant, donc pas de réelle avancée par rapport à mon problème initial.

Est-ce que ce temps d'exécution semble cohérent ?

[iberserk]

Tout dépend de votre architecture matériel (RAM, CPU, DISQUE...)

[iberserk]

Pouvez vous montrer le nouveau plan d’exécution?Le détails des IO (SET STATISTICS IO ON)


Nous avons parlé de la TABLE A mais il vous manque clairement le même genre d'index sur la TABLE B...

Gardez en outre à l'esprit que si vous remontez plusieurs millions de lignes dans votre SELECT, le résultat de la requête est remonté via le réseau.... d'ou un temps important dû au transit du résultat sur votre poste.

D'où effectuez vous la requête? sur le Server hébergeant votre SGBD? sur un poste client avec SSMS?

[Socap]

Voici le plan d'exécution réel :



Et les stats I/O :

Table 'A'. Nombre d'analyses 1, lectures logiques 91862, lectures physiques 1019, lectures anticipées 69909, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.
Table 'B'. Nombre d'analyses 1, lectures logiques 28186, lectures physiques 4230, lectures anticipées 18844, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.

La requête est effectivement exécutée depuis un client ssms distant. Mais je suis allé l'exécuter directement sur le ssms installé sur le serveur bdd, et le temps d'exécution était le même.

J'ai également testé la création d'un index similaire sur B :

Code :

CREATE nonclustered INDEX idx_B ON B (idB) include (champDetail2)

Et voici le nouvel explain plan :



Mais le temps d'exécution reste le même.

[iberserk]

Le nombre d'io me parait important pour un index couvrant dont la clé d'index est une FK (soit dis en passant: vu le résultat vos données n'étaient pas en cache (lectures physique: jouez la requête deux fois de suite))?

Peut-on voir la structure de vos table(typages etc.)

Autre question : dans quel cadre utilisez vous cette requête? je doute que vous vouliez afficher plusieurs million de lignes comme çà?