mardi 8 avril 2014

Serveurs liés : mauvaise performance ?

On entend souvent qu'il faut éviter les serveurs liés dans les requêtes, et je suis plutôt d'accord avec cette assertion. J'ai néanmoins rencontré une anecdote amusante en mission :

La requête de base est la suivante (un peu modifiée pour qu'elle soit plus lisible an cachant les champs parce que ce sont les vraies tables)


L'index cluster de la table FtDelivery est composé de Id_DimDate, Id_DimProduct, champ1, ..., Id_DimTransactionType
Le champ ValidityFlag ne fait pas partie de l'index cluster

Cette requête s'exécute sur le serveur où se trouve la table dbo.testProduit en 46s selon le plan d'exécution suivant :


L'idée d'optimisation a été de rapatrier la table testProduit directement sur le serveur lié et d'exécuter la requête sur ce dernier.

On passe donc à la requête suivante :


On l'exécute sur le serveur qui était lié. Et là, surprise, elle met 14 min en utilisant le plan d'exécution ci-dessous :


Belle optimisation n'est-ce pas ? Je supprime les serveurs liés et je multiplie par 20 les temps d'exécution.

Alors évidemment, SQL Server me propose de créer un index sur les colonnes de ma clause WHERE avec un include des colonnes du SELECT, mais cela ne m'arrange pas (la table a déjà quelques index et elle est particulièrement volumineuse. Il y a de plus plusieurs requêtes de ce type à exécuter, donc un index par requête ma paraît difficilement envisageable)

Ce qu'il est amusant de remarquer ici, c'est que le serveur lié permet de supprimer la clause WHERE coûteuse de ma requête, ce qui rend la "remote query" de mon premier plan d'exécution beaucoup plus rapide que la requête complète du deuxième plan. Tellement plus rapide que le "nested loop" s'en sort mieux qu'une jointure standard.

En d'autres termes, le serveur lié permet d'optimiser la requête.

Bon c'est une vérité qui se vérifie dans ce cas précis, loin de moi l'idée de conseiller de mettre des serveurs liés partout. Surtout que sur ce même projet et de manière générale, c'est l'inverse qui se produit.

Mais le résultat semblant paradoxal et peu naturel, je trouvais sympa de le partager.

Mise à Jour :

Comme l'histoire des statistiques dans le commentaire de Romain me travaillait, j'ai ré-exécuté en étant sûr de les avoir mises à jour.
J'en ai profité pour supprimer le cache avant l'exécution des requêtes et c'est déjà plus cohérent. L'explication est aussi plus claire.

La requête avec serveur lié s'exécute en 12min35 à la première exécution et 45s à la seconde exécution.
La requête sans serveur lié s'exécute en 16min01 à la première exécution et 45s ensuite.
C'est déjà rassurant sur le fonctionnement du cache :p

La différence de temps d'exécution tient dans la 'remote query' (bien vu Romain, je ne l'avais pas regardé en détail, je pensais qu'elle supprimait la clause pas qu'elle la transformait)

Alors que dans le cas sans serveur lié SQL Server considère plus judicieux d'utiliser l'index cluster de la table FtDelivery, la 'remote query' fonctionne par boucle sur un paramètre pour le produit (au lieu du 'in' de la requête, il boucle en utilisant un '='). De ce fait, SQL Server considère plus rapide (et c'est le cas) d'utiliser un index sur le produit existant sur FtDelivery (cf plan d'exécution ci-dessous)



Les temps d'exécutions n'ont plus un rapport de 20, par contre, on reste bien sur un temps avec serveur lié plus rapide que sans. On constate aussi que c'est bien spécifique à la construction et l'indexation de la table.


2 commentaires:

  1. Salut David,
    En effet, le problème mérite d'être posé, mais dans les deux cas, la plupart du temps est passé sur la table ftdelivery.
    J'ai surtout l'impression qu'en ramenant la table sur la même base de donnée tu te retrouves avec des statistiques non à jour pour celle-ci. Il faudrait connaitre le plan d'exécution de la remote query de ton premier plan pour analyser non ?

    RépondreSupprimer
  2. Les stats sont updatées de la veille sur FtDelivery, Sur la table rapatriée, elles doivent être à jour vu que j'ai créé un index cluster dessus.
    J'essaierai de rajouter effectivement le plan d'exécution de la remote query

    RépondreSupprimer