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.
