MS SQL Server
  1. MS SQL Server
  3. Aidez-nous à optimiser cette requête avec une énorme instruction IN
Intereting Posts
J'ai besoin de mettre à jour la requête de Mssql vers Mysql dans le server LINKED comment nous supprimons une ligne de la table parent lorsque la table enfant est connectée avec la table paren par la key forigen Requête SQL pour get des loggings Comparaison des performances des procédures stockées ex et nouvelle version Définir un schéma dans une procédure Filtrer la colonne datetime dans le server MS SQL 2008 avec une valeur de string analysée Faire pivoter les noms de colonne en double et get toutes les valeurs pour les colonnes Compter et combiner des rangées SELECT DISTINCT Inside Performances de la clause WHERE IN LINQ Left Join L'équivalent SQL via DefaultIfEmpty ne renvoie pas les résultats Confus à propos de la configuration des parameters pour une requête SQL SQL Server et la matérialisation intermédiaire? Ajouter une contrainte unique à la combinaison de deux colonnes Comment modifier la longueur de colonne d'une key primaire dans SQL Server? Compter les loggings couvrant plusieurs périodes

Aidez-nous à optimiser cette requête avec une énorme instruction IN

J'ai un insert qui utilise une condition vérifiant un NOT IN. Il y a environ 230k lignes dans la sous-requête NOT IN. 

INSERT INTO Validate.ItemError (ItemId, ErrorId, DateCreated) ( SELECT ItemId, 10, GetUTCDate() FROM Validate.Item INNER JOIN Refresh.Company ON Validate.Item.IMCompanyId = Refresh.Company.IMCompanyId WHERE Refresh.Company.CompanyId = 14 AND ( IMAccountId IS NULL OR NOT IMAccountId IN ( SELECT RA.IMAccountId FROM Refresh.Account RA INNER JOIN Refresh.BalancePool BP ON RA.BalancePoolId = BP.BalancePoolId WHERE BP.CompanyId = 14 ) ) )

Quand je lance cette opération telle qu'elle est, cela prend environ 30 minutes et plus (oui!). Le nombre de valeurs dans la table Validate.Item peut être de 150 lignes à plus de 200 Ko, ce qui vous permet de voir à quel point cela peut être douloureux.

Il y a des indices sur tous les champs pertinents dans les arrays, et aucun n'est trop fragmenté.

Ma première pensée a été de le faire en morceaux, et de le lancer dans une boucle WHILE: 

 DECLARE @StartId int, @EndId int, @MaxId int SELECT @MaxId = MAX(AccountId) FROM Refresh.Account SET @StartId = 1 SET @EndId = 1000 WHILE (@StartId < @MaxId) BEGIN INSERT INTO Validate.ItemError (ItemId, ErrorId, DateCreated) ( SELECT ItemId, 10, GetUTCDate() FROM Validate.Item INNER JOIN Refresh.Company ON Validate.Item.IMCompanyId = Refresh.Company.IMCompanyId WHERE Refresh.Company.CompanyId = 14 AND ( IMAccountId IS NULL OR NOT IMAccountId IN ( SELECT RA.IMAccountId FROM Refresh.Account RA INNER JOIN Refresh.BalancePool BP ON RA.BalancePoolId = BP.BalancePoolId WHERE BP.CompanyId = 14 AND RA.AccountId BETWEEN @StartId AND @EndId ) ) ) SET @StartId = @StartId + 1000 SET @EndId = @EndId + 1000 END

Le faire de cette façon me donne un time d'environ une minute par boucle; multipliez cela par 230 fois et nous avons un nombre encore plus ridicule.

S'il vous plaît dites-moi que vous avez une meilleure idée de comment optimiser cela. Sans cette requête, le process entier ne prend que 8 secondes; c'est juste la taille de la table Refresh.Account qui jette tout dans le chaos.

TIA!

Valkyrie

Est-ce que l'aide de NOT EXISTS aide ici? 

 (SELECT ItemId, 10, GetUTCDate() FROM Validate.Item INNER JOIN Refresh.Company ON Validate.Item.IMCompanyId = Refresh.Company.IMCompanyId WHERE Refresh.Company.CompanyId = 14 AND (IMAccountId IS NULL OR NOT EXISTS (SELECT TOP 1 RA.IMAccountId FROM Refresh.Account RA INNER JOIN Refresh.BalancePool BP ON RA.BalancePoolId = BP.BalancePoolId WHERE BP.CompanyId = 14 AND RA.IMAcccountID = Validate.Item.IMAccountId)))

Je ne suis pas sûr, si la requête est correcte.

Mais, j'utilise NOT EXISTS avec TOP 1 à l'intérieur de la sous-requête.
En outre, la sous-requête limite l'logging en ajoutant un AND RA.IMAcccountID = Validate.Item.IMAccountId supplémentaire AND RA.IMAcccountID = Validate.Item.IMAccountId .

EDIT: J'espère que vous avez l'idée de ce que j'essaie de faire.
Au lieu de le vérifier sur toutes les lignes à l'intérieur de Refresh.Account, je limite les lignes et essaye de find au less 1 ligne correspondante avec IMAccountID correspondant – qui ne devrait pas exister selon votre requête originale (qui utilise NOT IN ... ).

Débarrassez-vous de la condition OR .

Il ajoute un fullscan et empêche l'optimiseur d'utiliser un ANTI JOIN qu'il utiliserait autrement.

Cette requête renvoie le même: 

 SELECT ItemId, 10, GetUTCDate() FROM Validate.Item INNER JOIN Refresh.Company ON Validate.Item.IMCompanyId = Refresh.Company.IMCompanyId WHERE Refresh.Company.CompanyId = 14 AND NOT EXISTS ( SELECT RA.IMAccountId FROM Refresh.Account RA INNER JOIN Refresh.BalancePool BP ON RA.BalancePoolId = BP.BalancePoolId WHERE BP.CompanyId = 14 AND RA.IMAccounID = Validate.Item.IMAccountId )

Utilisez NOT EXISTS à la place: 

 ...OR NOT EXISTS (SELECT 1 FROM Refresh.Account RA INNER JOIN Refresh.BalancePool BP ON RA.BalancePoolId = BP.BalancePoolId WHERE BP.CompanyId = 14 AND RA.IMAccountId = xxx.IMAccountId)))

La sous-requête suivant EXISTS ne returnnera que le premier critère satisfaisant à l'logging. (N'oubliez pas de replace xxx par l'alias de la bonne table)

Plutôt que de faire un "pas dans", pourriez-vous simplement faire une jointure gauche à la table pertinente et vérifier les keys nuls? Je ne sais pas si la requête est correcte à 100%: 

 INSERT INTO Validate.ItemError (ItemId, ErrorId, DateCreated) SELECT ItemId, 10, GetUTCDate() FROM Validate.Item INNER JOIN Refresh.Company ON Validate.Item.IMCompanyId = Refresh.Company.IMCompanyId LEFT JOIN Refresh.Account INNER JOIN Refresh.BalancePool BP ON BP.BalancePoolId = RA.BalancePoolId ON Refresh.Account.IMAccountId = Validate.Item.IMAccountId WHERE Refresh.Company.CompanyId = 14 AND Validate.Item.IMAccountId IS NULL OR Refresh.Account.IMAccountId IS NULL