J'ai une question SQL avancée pour votre Gurus SQL Perf: -)
Je tente actuellement de comprendre un comportement dans une application plus grande, mais elle se résume à une requête contre ces deux tables:
utilisateurs Table - environ 750 entrées, userid ( varchar (50) ) comme pk groupé
-
actionlog Table - des millions d'entrées, comprend userid - mais pas de relation FK
pour une grille dans mon application ASP.NET, j'essaie d'obtenir tous les utilisateurs plus la date de leur dernière entrée de journal.
L'instruction SQL actuellement utilisée ressemble à quelque chose comme ceci: xxx et il renvoie les lignes à afficher - mais c'est assez lent (environ 20 secondes).
Ma première pensée était d'ajouter un Index sur le tableau Tableau sur userid et pour inclure la colonne horodatage de la colonne: xxx < P> Les lignes sont maintenant retournées très rapidement - moins de 2 secondes, avec 350 000 entrées dans la table ActionLog et mon index est tout simplement bien utilisé, car le plan d'exécution me montre. Tout semble bien. Maintenant, pour approximativement du scénario de production, nous avons chargé environ 2 millions de lignes dans la table ActionLog , dont 95% ou plus font référence à un utilisateur non existant (c'est-à-dire que ces lignes ont un userid qui n'existe pas dans les utilisateurs Table). Maintenant soudainement, la requête devient extrêmement lente (24 minutes!), et l'index n'est plus utilisé. J'ai supposé que depuis la grande majorité des entrées dans le activeLog table " t aligner avec un utilisateur existant, je verrais des gains de performance si j'utilise un index filtré - sur "Weed Out" toutes ces entrées désordonnées sans utilisateur correspondant - donc j'ai créé cet index (remplaçant l'autre Celui qui existait auparavant): xxx mais à ma déstabilisation - la requête est toujours à peu près la même - prend plus de 20 minutes à compléter. J'ai mis à jour les statistiques - pas de changement - toujours extrêmement lente.
chose drôle (pour moi) est: quand j'ai laissé tomber l'index et le reconstitué -> maintenant la requête était vraiment rapide (encore moins que 3 secondes). Wow!
mais dès que je commence à ajouter plus d'entrées, la requête "inclure" et devient vraiment très lente .......
Je ne comprends pas complètement Pourquoi cela se passe-t-il - je pensais qu'avec un indice filtré qui élimine toutes ces entrées "voyous", je verrais une bonne performance sur la recherche de la nouvelle entrée ActionLog pour les utilisateurs existants - mais cela ne semble être le cas. Pourquoi pas? Des idées? Les pensées? Choses à essayer ??
3 Réponses :
effacer le sous-sélection: puis pensez à obtenir les autres colonnes.
Ok - mais alors je recommanderais pas à l'aide de la liste des tables "séparées par la virgule" (ce style a été obsolète avec la norme SQL: 92 - plus de 20 ans il y a!) - Utilisez Bon Syntaxe de jointure ANSI!
enfer, oui. Mais comme je suis obligé d'utiliser une vieille merde au travail qui a des probs avec la syntaxe de jointure de la probeur, la merde va se passer :( merci!
Premier, Inclure code> ici n'est pas le meilleur choix. Vous triez par date d'entrée, mais les colonnes incluses ne sont pas triées. Une meilleure solution serait la suivante: CREATE NONCLUSTERED INDEX [IX_ActionLog_UserIdTimestamp] ON [dbo].[ActionLog]
([UserId], [Timestamp]);
Je ne sais pas quel avantage l'ajout de horodatage à l'index lui-même apporterait - car c'est la deuxième colonne de l'index, cet index ne pourra être utilisé pour le tri par horodatage ..... (Je vais l'essayer au bureau plus tard et faire rapport). Aussi: mettre à jour les statistiques ne semblait pas aider :-( (qui était un peu déroutant pour moi)
@marc_s, l'index triera par UserID, puis (dans chaque utilisateur) par horodatage. Cela vous permettra de trouver rapidement la dernière date (c'est-à-dire max ()) pour chacun d'eux. La colonne incluse ne fournit pas un tel avantage, Afair.
Roger - tu as absolument raison. Cet indice semble fonctionner mieux que ce que j'avais. Merci!
Vous rockez, Roger! Cela fonctionne comme un charme - même après que je insère (et réinsérez-vous) des millions de lignes plus factices - j'ai toujours une performance sous-seconde - WOW! Merci!
@marc_s, vous êtes la bienvenue :). Mais dans tous les cas, je recommanderais de garder un œil sur cet indice (et peut-être de la table en général), puisque celui-ci est naturellement sujette à la fragmentation - de nouvelles entrées pour les anciens utilisateurs tomberont toujours au milieu de celui-ci. Finalement, vous arriverez à un horaire de mise à jour / reconstruction qui gardera tout le monde heureux.
Essayez cette requête et voyez comment elle fonctionne avec votre index d'origine ou avec la modification suggérée par @Roger Wolf: si cela suce, je supprimerai la réponse :)
Il ne suce pas du tout - c'est aussi bon, sinon même mieux, que suggéra Roger. La performance est stable et très rapide - merci!
@marc_s ah ok, c'est bon à savoir. De toute évidence, je n'avais pas une grande quantité de données appropriées pour le tester. Bon de savoir qu'il a bien performé.
Pouvez-vous poster ou décrire le plan d'exécution lorsque vous déposez et recréez Index VS lorsque vous commencez à ajouter plus d'entrées et elle se tourbaisse? Est-ce juste décider de ne pas utiliser l'index après que des lignes sont ajoutées?
Essayez d'ajouter l'expression du filtre à votre sous-requête corrélée
A.USERID <> 'utilisateur' . Je pense que l'optimiseur a besoin de prendre en compte l'index filtré.Il y a quelques conseils sur la création et l'utilisation d'index filtrés sur MSDN. Vous pouvez essayer de spécifier
avec (index (IDX_USERID)) à la fin de la clause SELECT . Référence: créer des index filtrésJe suis curieux si l'ajout d'option (recompiler) a des effets après la reconstruction de l'index puisque vous avez déjà essayé de mettre à jour les statistiques. Mon seul autre essai serait de reconstruire la requête avec une jonction CTE sur UserID Obtenir l'horodatage souhaité que vous souhaitez d'abord dans l'espoir d'un meilleur plan d'exécution permanent.
@Mikaeleriksson: Oui, ça a fait le tour - merci! Mais j'ai appris que cela doit fonctionner sur SQL Server 2005 - les index filtrés ainsi sont sortis, malheureusement :-(