MS SQL Server
  1. MS SQL Server
  3. Comment accélérer un COUNT de valeurs groupées à travers une INNER JOIN de trois grandes tables?
Intereting Posts
créer une database avec des champs dynamics Mise à jour SQL à partir de l'instruction select Pourquoi cette requête EF prend-elle autant de time? sql select query ne fonctionne pas dans la procédure stockée Où les requêtes SQL Server Management Studio s'exécutent-elles réellement? SqlDataReader HasRows = True mais pas de données Erreur de package SSIS: Échec de la connection pour l'user 'NT AUTHORITY \ ANONYMOUS LOGON Nom d'object / colonne non valide après la réouverture Trouver toutes les contraintes de foreign keys dans la database référençant une certaine key primaire Impossible d'ouvrir la connection à la database à partir de la valeur de configuration SQL Server 2005 Comment puis-je configurer une table d'audit qui enregistre le nom de colonne mis à jour? Nom de la colonne de rlocation dans les requêtes dynamics Calcul de plus de 24 heures dans SQL Server 2008 Est-il possible de find les extensions des images stockées dans une database en tant que données binarys? Ajout automatique DateTime en cours dans TableField

Comment accélérer un COUNT de valeurs groupées à travers une INNER JOIN de trois grandes tables?

Quelqu'un pourrait-il me diriger vers les directeurs généraux pour accélérer ma requête ci-dessous?

J'ai une requête de travail qui agrège un nombre de valeurs de "propriété" groupées sur cinq colonnes. Mais il faut plus de vingt minutes pour courir.

Le count est agrégé sur trois tables de données "Case" associées qui ont chacune environ 500 000 lignes, et elles sont liées en utilisant une key composite "UserID" plus "CaseNumber". (CaseNumbers sont uniques par user.) J'utilise SQL Server 2005.

Mes problèmes keys semblent être:

  1. J'ai besoin de "Grouper" après avoir joint les trois tables car chacune contient uniquement au less une des colonnes que je regroupe (donc les recommandations discutées ici et ici ne semblent pas s'appliquer).

  2. Ma gamme de permutations possibles dans mon set de résultats souhaité (le produit des cinq gammes de colonnes) est grande (~ 200 000 possibilités).

Je suis capable d'get des résultats d'un ordre de grandeur plus rapidement si je limite ma "gamme". Donc, je pourrais, par exemple, redessiner cette requête comme une boucle "foreach" qui récupère un mois à la fois. Mais je préférerais concevoir une approche basée sur des sets.

J'ai créé une version similaire de cette requête sans la table temporaire, et une autre version avec de petites tables temporaires pour chaque valeur "range", et les vitesses résultantes étaient également lent.

En fin de count, je veux get un count du nombre total de permutations de "Catégories" fois "properties" à travers chaque "Cas" dans la database, regroupés par mois et user. Chaque "UserID" + "CaseNumber" est uniquement lié à un mois et une année, et peut être lié à deux ou trois "Catégories" ou "properties", auquel cas je voudrais countr chaque permutation de properties * Catégories.

Le jeu de résultats ressemblerait à ceci:

Jeu de résultats

Clés primaires:

  • "CaseMaster" a une key primaire composite contre "UserID" et "CaseNumber".

  • "CaseCategory" a une key primaire composite contre "UserID" et "CaseNumber" et "CategoryID".

  • "CaseProperty" a une key primaire composite contre "UserID" et "CaseNumber" et "OtherID" (pas PropertyID).

  • "CaseNumber" est "varchar". Le rest est "char".

Voici mon brouillon de requête: 

USE MyDB -- Drop Temp Table if it Exists IF OBJECT_ID('tempdb..#DataRange') IS NOT NULL DROP TABLE #DataRange SELECT [UserID] ,[Year] ,[Month] ,[CategoryID] INTO #DataRange FROM [MyDB].[dbo].[IndexTable] -- Aggregate a COUNT of "property" values joined across three large "Case" tables. SELECT range.[UserID] AS [UserID] ,range.[Year] AS [Year] ,range.[Month] AS [Month] ,range.[CategoryID] ,cp.[PropertyID] ,COUNT(cp.[PropertyID]) AS [PropertyCount] FROM ( -- (1) Get the range of possible permutations. (SELECT [UserID] ,[Year] ,[Month] ,[CategoryID] FROM #DataRange) range -- (2) Join against Dates AND Categories in the "Case Master" AND "Case Category" tables. INNER JOIN ( SELECT cm.[CaseNumber] AS [CaseNumber] ,cm.[UserID] AS [UserID] ,cm.[Year] AS [Year] ,cm.[Month] AS [Month] ,cc.[CategoryID] AS [CategoryID] FROM ((SELECT [CaseNumber] ,[UserID] ,(CASE WHEN value1 = 'A' THEN datepart(year, date1) ELSE datepart(year, date2) END) AS Year, ,(CASE WHEN value2 = 'B' THEN datepart(month, date1) ELSE datepart(month, date2) END) AS Month FROM [MyDB].[dbo].[CaseMaster]) cm INNER JOIN (SELECT [CaseNumber] ,[UserID] ,[CategoryID] FROM [MyDB].[dbo].[CaseCategory]) cc ON cm.UserID = cc.UserID AND cm.CaseNumber = cc.CaseNumber) ) case ON range.[UserID] = case.[UserID] AND range.[Year] = case.[IncYear] AND range.[Month] = case.[IncMonth] AND range.[WebCategoryID] = case.[WebCategoryID] -- (3) Join against a "Property" fields in the "Case Property" table. INNER JOIN ( SELECT [CaseNumber] ,[UserID] ,[property1] AS [PropertyID] FROM [MyDB].[dbo].[CaseProperty] ) cp ON range.UserID = cp.UserID AND case.CaseNumber = cp.CaseNumber AND cp.[PropertyID] IN (SELECT [PropertyID] FROM [MyDB].[dbo].[PropertyTypes]) ) GROUP BY range.[UserID], range.[Year], range.[Month], range.[CategoryID], p.[PropertyID] DROP TABLE #DataRange GO

Indices Vérifiez le plan de requête pour tous les indices nécessaires.

Si les sont en place, il est trop lent et vous avez besoin de cela pour être plus rapide, find le goulot d'étranglement et acheter le bon matériel pour le réparer, OU find un autre moyen d'get datatables (caching en memory etc.) travailler pour cette requête.

Ainsi, à un moment donné, il y a une raison pour laquelle l'parsing de données volumineuses nécessite un matériel coûteux. Smae raison je twig juste 5tb SSD dans mon server de database.

Cela dit, vous avez probablement un goulot d'étranglement de débit sur tempdb – et qui aime un bon RAID 0 de SSD;)

Compte tenu de certaines hypothèses:

  • La requête touche (agrégats) sur pratiquement chaque ligne de chaque table, et
  • Les tables sont vraiment grandes (se joint à trois tables avec 500k lignes dans chaque qualificatif), et
  • La requête est exécutée fréquemment OU si la requête doit s'exécuter rapidement lorsqu'elle est appelée

Vous pourriez alors regarder une situation d'entrepôt de données (datamart, table de rapport). Certains des concepts de base derrière ceci sont:

  • Concevoir les tables pour supporter les requêtes de reporting (olap), pas les requêtes write / update (oltp)
  • À des moments précis dans le time (tous les jours? Toutes les heures?), Vous actualisez l'entrepôt, en chargeant toutes datatables qui ont été ajoutées au système depuis la dernière actualisation. (Ou, recharger tout à partir de zéro à chaque fois, mais ce n'est pas idéal)
  • Conçu correctement, les requêtes de rapports peuvent s'exécuter très rapidement

Dans les cas où vous ne traitez que trop de données, les requêtes de ce type ne peuvent tout simplement pas être exécutées rapidement – pensez à un rapport "fin de journée" ou à une opération du jour au lendemain. L'avantage de l'entrepôt ici est que ces requêtes de longue durée ne seront pas exécutées sur la transaction normale, donc vous ne recevez pas de situations de blocage, de blocage ou de blocage (tant que vous n'exécutez pas les requêtes en même time vous essayez de charger les tables.) En outre, datatables sous-jacentes ne changeront pas pendant l'exécution de la requête.

L'indexing semble avoir été le coupable, en particulier l'ordre des éléments dans ma key primaire composite dans le tableau "Case Property".

Pour une raison quelconque, la table "Case Property" a été créée (pas par moi!) Avec les éléments keys dans l'ordre "UserID" -> "OtherID" -> "CaseNumber" au lieu de "UserID" -> "CaseNumber" -> "AutreID".

La commutation de la command, sans autres modifications, a accéléré ma requête de 16 minutes à 1 seconde pour le même set de plages.

Sinon, l'ajout de la clause "ON" sortingviale suivante après INNER JOIN de la propriété "Case Case" a également accéléré la requête de 16 minutes à 11 secondes sans la correction de key, même si cette clause n'a aucun impact sur le jeu de résultats sur la plage de toutes les valeurs possibles. 

 AND cp.[OtherID] IN (SELECT [OtherID] FROM [MyDB].[dbo].[OtherIDLookupTable])

Merci TomTom et Philip pour les suggestions et les idées utiles!