SQL Appel récursif parent / enfant ou union?

Ok, apparemment, basé sur les upvotes pour l'autre réponse, cela nécessite des explications supplémentaires. Exemple (fait avec MySQL car je l'ai à scope de main mais le principe est universel pour tout dialecte SQL):

CREATE TABLE Blah ( ID INT PRIMARY KEY, SomeText VARCHAR(30), ParentID INT ) INSERT INTO Blah VALUES (1, 'One', 0); INSERT INTO Blah VALUES (2, 'Two', 0); INSERT INTO Blah VALUES (3, 'Three', 1); INSERT INTO Blah VALUES (4, 'Four', 1); INSERT INTO Blah VALUES (5, 'Five', 4); 

Version de jointure gauche:

 SELECT a.ID, a.SomeText, COUNT(1) FROM Blah a JOIN Blah b ON a.ID= b.ParentID GROUP BY a.ID, a.SomeText 

Faux. Ignore le cas sans enfants.

Jointure externe gauche:

 SELECT a.ID, a.SomeText, COUNT(1) FROM Blah a LEFT OUTER JOIN Blah b ON a.ID= b.ParentID GROUP BY a.ID, a.SomeText 

Mauvais et la raison pour laquelle est un peu subtile. COUNT(1) count les lignes NULL alors que COUNT(b.ID) ne le fait pas. Donc, ce qui précède est faux, mais c'est correct:

 SELECT a.ID, a.SomeText, COUNT(b.ID) FROM Blah a LEFT OUTER JOIN Blah b ON a.ID= b.ParentID GROUP BY a.ID, a.SomeText 

Sous-requête corrélée:

 SELECT ID, SomeText, (SELECT COUNT(1) FROM Blah WHERE ParentID= a.ID) ChildCount FROM Blah a 

Aussi correct.

Ok, alors lequel utiliser? Les plans ne vous disent que beaucoup. Le problème des sous-requêtes par rapport aux jointures à gauche est ancien et il n'y a pas de réponse claire sans parsing comparative. Nous avons donc besoin de données:

 <?php ini_set('max_execution_time', 180); $start = microtime(true); echo "<pre>\n"; mysql_connect('localhost', 'scratch', 'scratch'); if (mysql_error()) { echo mysql_error(); exit(); } mysql_select_db('scratch'); if (mysql_error()) { echo mysql_error(); exit(); } $count = 0; $limit = 1000000; $this_level = array(0); $next_level = array(); while ($count < $limit) { foreach ($this_level as $parent) { $child_count = rand(0, 3); for ($i=0; $i<$child_count; $i++) { $count++; query("INSERT INTO Blah (ID, SomeText, ParentID) VALUES ($count, 'Text $count', $parent)"); $next_level[] = $count; } } $this_level = $next_level; $next_level = array(); } $stop = microtime(true); $duration = $stop - $start; $inserttime = $duration / $count; echo "$count users added.\n"; echo "Program ran for $duration seconds.\n"; echo "Insert time $inserttime seconds.\n"; echo "</pre>\n"; function query($query) { mysql_query($query); if (mysql_error()) { echo mysql_error(); exit(); } } ?> 

J'ai manqué de memory (32M) pendant cette course donc seulement fini avec 876.109 records mais bon ça va faire. Plus tard, lorsque je teste Oracle et SQL Server, je prends exactement le même set de données et je l'importe dans Oracle XE et SQL Server Express 2005.

Maintenant, une autre affiche a soulevé le problème de mon utilisation d'un wrapper de count autour des requêtes. Il a correctement souligné que l'optimiseur peut ne pas exécuter les sous-requêtes dans ce cas. MySQL ne semble pas si intelligent. Oracle est. SQL Server semble l'être aussi.

Je vais donc citer deux numbers pour chaque combinaison de database: le premier est enveloppé dans SELECT COUNT(1) FROM ( ... ) , le second est raw.

Installer:

• MySQL 5.0 utilisant PremiumSoft Navicat ( LIMIT 10000 dans la requête);
• SQL Server Express 2005 à l'aide de Microsoft SQL Server Management Studio Express;
• Oracle XE utilisant PL / SQL Developer 7 (limité à 10 000 lignes).

Jointure externe gauche:

 SELECT a.ID, a.SomeText, COUNT(b.ID) FROM Blah a LEFT OUTER JOIN Blah b ON a.ID= b.ParentID GROUP BY a.ID, a.SomeText 

• MySQL: 5.0: 51.469s / 49.907s
• SQL Server: 0 ⁽¹⁾ / 9s ⁽²⁾
• Oracle XE: 1.297s / 2.656s

(1) Virtuellement instantané (confirmant le path d'exécution différent)
(2) Impressionnant considérant qu'il returnne toutes les rangées, pas 10 000

Va juste montrer la valeur d'une vraie database. En outre, la suppression du champ SomeText a eu un impact significatif sur les performances de MySQL. Aussi, il n'y avait pas beaucoup de différence entre la limite de 10000 et ne pas l'avoir avec MySQL (amélioration des performances par un facteur de 4-5). Oracle l'avait juste parce que PL / SQL Developer barfaré quand il a atteint 100M d'utilisation de la memory.

Sous-requête corrélée:

 SELECT ID, SomeText, (SELECT COUNT(1) FROM Blah WHERE ParentID= a.ID) ChildCount FROM Blah a 

• MySQL: 8.844s / 11.10s
• SQL Server: 0s / 6s
• Oracle: 0.046s / 1.563s

Ainsi, MySQL est mieux par un facteur de 4-5, Oracle est environ deux fois plus rapide et SQL Server est sans doute un peu plus rapide.

Le sharepointmeure: la version de la sous-requête corrélée est plus rapide dans tous les cas.

L'autre avantage des sous-requêtes corrélées est qu'elles sont syntaxiquement plus propres et plus faciles à étendre. Par ceci je veux dire que si vous voulez faire un count dans un tas d'autres tables, chacun peut être inclus comme un autre élément de sélection proprement et facilement. Par exemple: imaginez un logging des clients aux factures où ces factures étaient impayées, en retard ou payées. Avec une sous-requête simple:

 SELECT id, (SELECT COUNT(1) FROM invoices WHERE customer_id = c.id AND status = 'UNPAID') unpaid_invoices, (SELECT COUNT(1) FROM invoices WHERE customer_id = c.id AND status = 'OVERDUE') overdue_invoices, (SELECT COUNT(1) FROM invoices WHERE customer_id = c.id AND status = 'PAID') paid_invoices FROM customers c 

La version agrégée est beaucoup plus laide.

Maintenant, je ne dis pas que les sous-requêtes sont toujours supérieures aux jointures agrégées, mais il est souvent nécessaire de les tester. En fonction de vos données, de la taille de ces données et de votre fournisseur de SGBDR, la différence peut être extrêmement importante.

Je crois que c'est ce que vous essayez de faire:

 SELECT P.PK_ID, P.Column1, P.Column2, COUNT(C.PK_ID) FROM Parent P LEFT JOIN Child C ON C.PK_ID = P.FK1 GROUP BY P.PK_ID, P.Column1, P.Column2 

Une explication de pourquoi @cletus est faux.

Tout d'abord, les accessoires sur la search.

Deuxièmement, vous le faites mal.

Explication:

Requête originale:

 EXPLAIN SELECT ID, (SELECT COUNT(1) FROM Blah WHERE ParentID= a.ID) as ChildCount FROM Blah a 

Résultat:

     "Seq Scan sur bla a (coût = 0.00..145180063607.45 lignes = 2773807 largeur = 4)"
     "SubPlan"
     "-> Agrégat (coût = 52339.61..52339.63 lignes = 1 largeur = 0)"
     "-> Seq Scan sur blah (coût = 0.00..52339.59 lignes = 10 largeur = 0)"
     "Filtre: (parentid = $ 0)"

Que se passe-t-il lorsque vous returnnez dans "select count (1)":

 EXPLAIN SELECT count(1) FROM ( SELECT ID, (SELECT COUNT(1) FROM Blah WHERE ParentID= a.ID) as ChildCount FROM Blah a) as bar 

     "Agrégat (coût = 52339.59..52339.60 lignes = 1 largeur = 0)"
     "-> Seq Scan sur bla a (coût = 0.00..45405.07 lignes = 2773807 largeur = 0)"

Remarquez la différence?

L'optimiseur est assez intelligent pour voir qu'il n'a pas besoin de faire la sous-requête. Donc, ce n'est pas que les sous-requêtes corrélées sont rapides; c'est que ne pas les faire est rapide :-).

Malheureusement, il ne peut pas faire de même pour une jointure externe gauche, puisque le nombre de résultats n'est pas prédéterminé par le premier balayage.

Leçon n ° 1: Les plans de requête vous disent beaucoup de choses. La mauvaise design de l'expérience vous met dans le pésortingn.

Leçon # 1.1: Si vous n'avez pas besoin de faire une jointure, par tous les moyens, ne le faites pas.

J'ai créé un set de données de test d'environ 2,7 millions de requêtes.

La jointure externe gauche – sans le wrapper – a couru 171,757 ms sur mon ordinateur portable.

La sous-requête corrélée … Je mettrai à jour quand elle se terminera, je suis à 700K ms et ça fonctionne toujours.

Leçon n ° 2: Quand quelqu'un vous dit de regarder le plan de requête, et prétend qu'il montre un ordre de différence algorithmique … regardez le plan de requête.

Avez-vous déjà essayé d'append un index à l'ID parent pour MySQL. Je suis sûr que les time d'exection s'amélioreront énormément. Je n'ai pas testé mais je dirais que MySQL passe par toutes les lignes pour déterminer le nombre. Ce qui signifie qu'il fait 10 – 40 milliards (nombre de lignes dans la table * 10000) des searchs dans ces 59 secondes.

Supposons que SQL Server et Oracle créent un index à la volée. S'ils le font, ce ne serait que de 1 à 4 millions.

Vos requêtes supposent toutes que l'ordre dans lequel les nœuds fils parents sont entrés est séquentiel. Si un enfant de l'un des premiers nœuds est entré à la fin et que son ID ou PK est plus élevé, la requête ne fonctionne pas.