La requête avec une sous-requête UNION prend beaucoup de time

J'ai eu un problème étrange avec certaines requêtes qui dépendent d'une sous-requête. Ils s'exécutent rapidement, jusqu'à ce que j'utilise une instruction UNION dans la sous-requête. Ensuite, ils courent à l'infini, j'ai donné après 10 minutes. Le scénario que je suis en train de décrire n'est pas le scénario original avec lequel j'ai commencé, mais je pense qu'il élimine beaucoup de problèmes possibles tout en produisant le même problème. Donc, même si c'est une requête inutile, supporter avec moi!

J'ai une table

tblUser - 100,000 rows tblFavourites - 200,000 rows

Si j'exécute:

 SELECT COUNT(*) FROM tblFavourites WHERE userID NOT IN (SELECT uid FROM tblUser);

… puis il court en less d'une seconde. Cependant, si je le modifie pour que la sous-requête ait un UNION, il fonctionnera pendant au less 10 minutes (avant que j'abandonne!)

 SELECT COUNT(*) FROM tblFavourites WHERE userID NOT IN (SELECT uid FROM tblUser UNION SELECT uid FROM tblUser);

Un changement inutile, mais il devrait donner le même résultat et je ne vois pas pourquoi cela devrait prendre plus de time?

Placer la sous-requête dans une vue et l'appeler à la place a le même effet.

Des idées pour lesquelles cela serait? J'utilise SQL Azure.

Problème résolu. Voir ma réponse ci-dessous.

UNION fait vraiment un DISTINCT sur tous les champs dans l'set de données combinées. Il filter les dupes dans les résultats finaux.

Est-ce que Uid est indexé? Sinon, cela peut prendre beaucoup de time en tant que moteur de requête:

Génère le premier set de résultats
Génère le deuxième set de résultats
Filtre tous les dupes (ce qui représente la moitié des loggings) dans une table de hachage

Si les duplicates ne sont pas une préoccupation (et en utilisant IN signifie qu'ils ne seront pas), alors utilisez UNION ALL qui supprime l'étape de sorting / filtrage coûteuse.

UNION génère des valeurs uniques, de sorte que le moteur DBMS crée des sortings. Vous pouvez utiliser en toute security UNION ALL dans ce cas.

Les UNION sont généralement implémentés via des tables temporaires en memory. Vous êtes essentiellement en train de copyr votre tblUser deux fois en memory, sans aucun index . Ensuite, chaque ligne dans tblFavourites génère un scan complet de la table sur 200 000 lignes – soit 200Kx200K = 40 milliards de scans à deux lignes (parce que le moteur de search doit get l'UID des deux lignes de la table)

Si votre tblUser a un index sur uid (ce qui est certainement vrai car toutes les tables dans SQL Azure doivent avoir un index clusterisé), alors chaque ligne dans tblFavourites subit une search d'index très rapide, résultant en seulement 200Kxlog (100K) = 200Kx17 = 200K row parsings, chacune avec 17 comparaisons d'index b-tree (ce qui est beaucoup plus rapide que la lecture de l'UID d'une ligne sur une page de données), donc cela équivaut à environ 200Kx (3-4) ou 1 million de scans à deux lignes. Je crois que les nouvelles versions de SQL Server peuvent également build une table de hachage temporaire contenant seulement les uid, donc essentiellement il descend à 200K scans de lignes (en supposant que la table de hachage soit sortingviale).

Vous devez également générer votre plan de requête à vérifier.

Essentiellement, la requête non-UNION tourne environ 500 000 fois plus vite si tblUser a un index (devrait être sur SQL Azure).

Il s'avère que le problème était dû à l'un des index … tblFavourites contenait deux foreign keys à la key primaire (uid) dans tblUser:

 userId otherUserId

Les deux colonnes avaient la même définition et les mêmes index, mais j'ai découvert que l'échange userId pour otherUserId dans la requête d'origine a résolu le problème.

Iran:

 ALTER INDEX ALL ON tblFavourites REBUILD

… et le problème est parti. La requête s'exécute maintenant presque instantanément.

Je ne sais pas trop ce qui se passe dans les coulisses de Sql Server / Azure … mais je peux seulement imaginer que c'était un index endommagé ou quelque chose? Je mets à jour les statistics fréquemment, mais cela n'a aucun effet.

Merci!

—- METTRE À JOUR

Ce qui précède n'était pas entièrement correct. Cela a réglé le problème pendant environ 20 minutes, puis il est revenu. J'ai été en contact avec le support de Microsoft pendant plusieurs jours et il semble que le problème soit lié au tempDB. Ils travaillent sur une solution à leur fin.

Je viens de rencontrer ce problème. J'ai environ 1 million de lignes à traverser et puis j'ai réalisé que certains de mes identifiants étaient dans une autre table, donc j'ai uni pour get la même information dans un "NOT EXISTS". Je suis passé de la requête prenant environ 7 secondes à traiter seulement 5000 lignes après une minute environ. Cela semblait aider. Je déteste absolument la solution, mais j'ai essayé une multitude de choses qui se retrouvent toutes dans le même plan d'exécution extrêmement lent. Celui-ci m'a donné ce dont j'avais besoin dans environ 18 secondes.

  DECLARE @PIDS TABLE ([PID] [INT] PRIMARY KEY) INSERT INTO @PIDS SELECT DISTINCT [ID] FROM [STAGE_TABLE] WITH(NOLOCK) INSERT INTO @PIDS SELECT DISTINCT [OTHERID] FROM [PRODUCTION_TABLE] WITH(NOLOCK) WHERE NOT EXISTS(SELECT [PID] FROM @PIDS WHERE [PID] = [OTHERID] SELECT (columns needed) FROM [ORDER_HEADER] [OH] WITH(NOLOCK) INNER JOIN @PIDS ON [OH].[SOME_ID] = [PID]

(Et oui, j'ai essayé "WHERE EXISTS IN …" pour la sélection finale … jointure interne était plus rapide) S'il vous plaît laissez-moi dire encore une fois, je pense personnellement que c'est vraiment moche, mais j'utilise cette jointure deux fois dans mon proc, donc ça va me faire gagner du time à long terme. J'espère que cela t'aides.

Cela n'a-t-il pas plus de sens de reformuler les questions de

"UserIds qui ne figurent pas dans la list combinée de tous les ID qui apparaissent dans cette table et / ou cette table"

"UserIds qui ne sont pas sur cette table ET ne sont pas sur cette table non plus

 SELECT COUNT(*) FROM tblFavourites WHERE userID NOT IN (SELECT uid FROM tblUser) AND userID NOT IN (SELECT uid FROM tblUser);