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