Dépendamment de la clause Where, le temps d'interrogation de SQL Server peut expirer

Question

J'ai une requête qui utilise 3 fonctions et quelques vues différentes en dessous, qui sont trop complexes pour être postées ici. La chose étrange que je rencontre est que lorsque j'exécute la requête de niveau supérieur, avoir plus d'une clé de recherche fait que la requête prend environ une heure à s'exécuter, alors que diviser la requête en deux prend environ 5 secondes par requête.

Voici la requête de niveau supérieur :

Sélectionnez * 
à partir de dbo.vwSimpleInvoice i 
joindre dbo.vwRPTInvoiceLineItemDetail d sur i.InvoiceID = d.InvoiceID 

Quand j'ajoute cette clause where :

Où i.InvoiceID = 109581

La requête prend environ 3 secondes à s'exécuter. De même lorsque j'ajoute cette clause where :

Où i.InvoiceID = 109582

cela prend environ 3 secondes.

Cependant, quand j'ajoute cette clause where :

Où i.InvoiceID in (109581, 109582)

J'ai dû annuler la requête après environ 50 minutes, et elle ne renvoie jamais de résultats.

Cela se produit sur un serveur distant d'un client exécutant SQL Server 2008 R2 Express. Lorsque je l'exécute localement (également sur SQL Server 2008 R2 Express), je n'ai pas le délai massif, la dernière clause where prend environ 30 secondes à renvoyer. Cependant, le client a beaucoup plus de données que moi.

Des idées sur par où commencer à résoudre ce problème ?

Modifier :

Après les commentaires ci-dessous, j'ai reconstruit les index et statistiques, ce qui a amélioré les performances des 2 premières clauses where, mais n'a eu aucun effet sur la troisième. J'ai ensuite joué avec la requête, et découvert que si je la réécris comme suit :

Sélectionnez * 
à partir de dbo.vwSimpleInvoice i 
joindre  
    (Sélectionnez * à partir de dbo.vwRPTInvoiceLineItemDetail) d sur i.InvoiceID = d.InvoiceID 
Où i.InvoiceID in (109581, 109582)

Les performances reviennent à des niveaux attendus, environ 200 ms. Je suis maintenant plus perplexe que jamais quant à ce qui se passe...

Modifier 2 :

En fait, je me trompe. Ce n'était pas la réécriture de la requête de cette manière, j'ai accidentellement changé la clause Where pendant la réécriture en :

Où d.InvoiceID in (109581, 109582)

(Changé i en d).

Toujours un peu perdu quant à pourquoi cela fait une telle différence significative sur une Jointure Interne ?

---

Autre modification :

En jouant encore plus avec cela, je ne peux toujours pas le comprendre.

Sélectionnez InvoiceId à partir de tblInvoice Où CustomerID = 2000

renvoie :

80442, 4988, 98497, 102483, 102484, 107958, 127063, 168444, 168531, 173382, 173487, 173633, 174013, 174160, 174240, 175389

Sélectionnez * à partir de dbo.vwRPTInvoiceLineItemDetail
Où InvoiceID in 
(80442, 4988, 98497, 102483, 102484, 107958, 127063, 168444, 168531, 173382, 173487, 173633, 174013, 174160, 174240, 175389)

Exécute : 31 lignes retournées 110 ms

Sélectionnez * à partir de dbo.vwRPTInvoiceLineItemDetail
Où InvoiceID in 
(Sélectionnez InvoiceId à partir de tblInvoice Où CustomerID = 2000)

Exécute : 31 lignes retournées 65 minutes

Answer 1

Le problème que vous rencontrez est presque certainement dû à un plan de requête mis en cache, qui est approprié pour certaines versions de paramètres passés à la requête, mais pas pour d'autres (alias Parameter Sniffing).

Ceci est un incident fréquent, et est souvent exacerbé par des statistiques obsolètes et/ou des index mal fragmentés.

Première étape : assurez-vous d'avoir reconstruit tous vos index et que les statistiques sur les colonnes non indexées sont à jour. (Assurez-vous également que votre client a une tâche de maintenance des index régulièrement planifiée)

exec sp_msforeachtable "DBCC DBREINDEX('?')"
go

exec sp_msforeachtable "UPDATE STATISTICS ? WITH FULLSCAN, COLUMNS"
go

Ceci est la référence canonique : Lent dans l'Application, Rapide dans SSMS?

Si le problème persiste après la reconstruction des index et la mise à jour des statistiques, alors vous avez quelques options :

1. Utilisez du SQL dynamique (mais lisez ceci d'abord : The Curse and Blessings of Dynamic SQL)

2. Utilisez OPTIMIZE FOR

3. Utilisez WITH(RECOMPILE)