Все ваши запросы предполагают, что порядок ввода родительских дочерних узлов является последовательным. Если дочерний элемент одного из первых узлов вводится в конце, а его ID или PK выше, запрос не работает.

могу найти соответствующий пример там.

Я пытаюсь вернуть подмножество таблицы, и для каждой строки в этой таблице я хочу проверить, сколько у нее дочерних элементов, и вернуть это число как часть набора результатов.

Столбцы родительской таблицы: PK_ID, Column1, Column2, FK1

Для каждого FK1 в наборе результатов выберите count (*) из child_table.

Конечный набор результатов

3, col1text, col2text, 1 (ребенок)
5, col1texta, col2texta, 2 (ребенок)
6, col1textb, col2textb, 0 (ребенок)
9, col1textc, col2textc, 4 (ребенок)

Я пытаюсь найти лучший способ ссылки на столбец в наборе результатов в другом запросе, а затем снова объединить их. Использование T-SQL

 cletus26 янв. 2009 г., 08:54
Добавлены тесты SQL Server и Oracle.
 Brettski26 янв. 2009 г., 03:32
Значение в FK1 - это дочерний PK, поэтому в нем будет только одна строка с этим значением.
 Brettski26 янв. 2009 г., 03:29
Мне трудно понять, чего вы пытаетесь достичь, чтобы найти ответ. Если FK1 является внешним ключом для хранения дочерних ключей, то для любой строки, у которой FK = 1, будет столько же, сколько FK1 = 1. Не могли бы вы привести пример sql для ясности?

Ответы на вопрос(5)

Объяснение, почему @cletus не так.

Во-первых, реквизит при проведении исследований.

Во-вторых, вы делаете это неправильно.

Объяснение:

Исходный запрос:

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

Результат:

    "Seq Scan on blah a  (cost=0.00..145180063607.45 rows=2773807 width=4)"
    "  SubPlan"
    "    ->  Aggregate  (cost=52339.61..52339.63 rows=1 width=0)"
    "          ->  Seq Scan on blah  (cost=0.00..52339.59 rows=10 width=0)"
    "                Filter: (parentid = $0)"

Что происходит, когда вы добавляете «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
    "Aggregate  (cost=52339.59..52339.60 rows=1 width=0)"
    "  ->  Seq Scan on blah a  (cost=0.00..45405.07 rows=2773807 width=0)"

Заметили разницу?

Оптимизатор достаточно умен, чтобы понять, что ему не нужно выполнять подзапрос. Так что коррелированные подзапросы не такие быстрые; это то, что не делать это быстро :-).

К сожалению, он не может сделать то же самое для левого внешнего соединения, так как количество результатов не определяется предварительно при первом сканировании.

Урок 1: Планы запросов говорят вам о многом. Плохой дизайн эксперимента приводит к неприятностям.

Урок № 1.1: Если вам не нужно объединяться, не делайте этого.

Я создал тестовый набор данных примерно из 2,7 миллионов запросов.

Левое внешнее соединение - без оболочки - на моем ноутбуке работало 171 757 мс.

Коррелированный подзапрос ... Я обновлю, когда он закончится, я нахожусь на 700K мс, и он все еще работает.

Урок № 2: Когда кто-то говорит вам посмотреть на план запроса и утверждает, что он показывает алгоритмический порядок различий ... посмотрите на план запроса.

 cletus26 янв. 2009 г., 08:53
Добавлены Oracle и SQL Server.
 SquareCog26 янв. 2009 г., 06:25
Не могли бы вы опубликовать время выполнения и объяснить планы для обоих запросов? Вполне возможно, что MySQL делает что-то умное, чего не делает Postgres, на котором я работал. Очень маловероятно, учитывая природу двух, но возможно ..
 cletus26 янв. 2009 г., 06:18
-1 На самом деле я проверил и версию (с оберткой COUNT и без нее (1)), и продолжительность была примерно одинаковой. Существует разница между измерением фактических результатов и теоретическим планом объяснения.
 cletus26 янв. 2009 г., 07:44
MySQL вполне может быть менее умным, чем SQL Server, когда нужно решить, нужно ли ему выполнять подзапросы. Я все же проверил это. Я пытался заставить мой SQL Server Express работать и импортировать большой объем данных, и это был кошмар, но я также опубликую это, если / когда он когда-либо будет работать.
 SquareCog26 янв. 2009 г., 06:15
Я убил коррелированный подзапрос после того, как он выполнялся в течение 50 минут без ответа. Имейте в виду, что левое внешнее соединение прошло чуть менее 3 минут.

по-видимому, основываясь на возражениях для другого ответа, это требует дальнейшего объяснения. Пример (сделано с MySQL, потому что это удобно, но принцип универсален для любого диалекта 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);

Левая версия соединения:

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

Неправильно. Игнорирует дело без детей.

Левое внешнее соединение:

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

Неправильно и причина почему несколько тонкая.COUNT(1) счетчикиNULL строки в то время какCOUNT(b.ID) не делает. Таким образом, вышеприведенное неверно, но это правильно:

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

Коррелированный подзапрос:

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

Тоже правильно.

, так что использовать? Планы только так много тебе скажут. Вопрос оподзапросы против левых соединений это старый, и нет четкого ответа без сравнения его. Итак, нам нужны некоторые данные:

<?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();
    }
}
?>

У меня не хватило памяти (32M) во время этого прогона, так что в итоге оказалось только 876 109 записей, но это подойдет. Позже, когда я тестирую Oracle и SQL Server, я беру один и тот же набор данных и импортирую его в Oracle XE и SQL Server Express 2005.

Теперь другой постер поднял вопрос о том, чтобы я использовал оболочку count для запросов. Он правильно указал, что оптимизатор может не выполнять подзапросы в этом случае. MySQL не кажется таким уж умным. Оракул есть. SQL Server вроде бы тоже.

Поэтому я приведу две цифры для каждой комбинации базы данных и запроса: первая обернута вSELECT COUNT(1) FROM ( ... )Вторая сырая.

Настроить:

MySQL 5.0 с использованием PremiumSoft Navicat (LIMIT 10000 в запросе);SQL Server Express 2005 с использованием Microsoft SQL Server Management Studio Express;Oracle XE с использованием PL / SQL Developer 7 (ограничено 10 000 строк).

Левое внешнее соединение:

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,469 с / 49,907 сSQL Server: 0(1) / 9с(2)Oracle XE: 1,297 с / 2,665 с

(1) Практически мгновенно (подтверждая другой путь выполнения)
(2) Впечатляет, учитывая, что он возвращает все строки, а не 10000

Просто идет, чтобы показать ценность реальной базы данных. Кроме того, удаление поля SomeText оказало значительное влияние на производительность MySQL. Также не было большой разницы между лимитом 10000 и отсутствием его с MySQL (повышение производительности в 4-5 раз). У Oracle было это только потому, что PL / SQL Developer сорвался, когда он достиг 100M использования памяти.

Коррелированный подзапрос:

SELECT ID, SomeText, (SELECT COUNT(1) FROM Blah WHERE ParentID= a.ID) ChildCount
FROM Blah a
MySQL: 8,844 с / 11,10 сSQL Server: 0 с / 6 сOracle: 0,046 с / 1,563 с

Таким образом, MySQL лучше в 4-5 раз, Oracle примерно в два раза быстрее, а SQL Server, пожалуй, лишь немного быстрее.

Дело остается:коррелированная версия подзапроса быстрее во всех случаях.

Другое преимущество коррелированных подзапросов заключается в том, что они синтаксически чище и их легче расширять. Под этим я подразумеваю, что если вы хотите сделать подсчет в куче других таблиц, каждая из них может быть легко и просто включена в качестве другого элемента выбора. Например: представьте учет клиентов по счетам, в которых эти счета были либо не оплачены, либо просрочены, либо оплачены. С подзапросом это просто:

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

Совокупная версия намного страшнее.

Сейчас я не говорю, что подзапросы всегда превосходят совокупные объединения, но достаточно часто они таковы, что вы должны проверить это. В зависимости от ваших данных, размера этих данных и вашего поставщика RDBMS разница может быть очень значительной.

 Sean Bright26 янв. 2009 г., 04:45
+1 заненормальный последующее исследование!
 Bill Karwin26 янв. 2009 г., 03:58
@cletus: используйте COUNT (b.parentID) вместо COUNT (1). Где b НЕДЕЙСТВИТЕЛЬНО из-за ЛЕВОГО ВНЕШНЕГО СОЕДИНЕНИЯ, это не повлияет на счет. Кроме того, ЛЕВЫЕ СОЕДИНЕНИЯ всегда являются внешними соединениями.
 cletus26 янв. 2009 г., 04:25
Тесты добавлены и ошибка исправлена.
 SquareCog26 янв. 2009 г., 03:49
count (b.id) даст правильный результат с левым внешним соединением. Это также приведет к одному последовательному сканированию и одному сканированию индекса. Коррелированные подзапросы вынуждают вложенный цикл, что означает на порядок худшую производительность.
 Amy B26 янв. 2009 г., 04:09
Ваш оригинальный ответ был намного лучше (и правильный).

что порядок ввода родительских дочерних узлов является последовательным. Если дочерний элемент одного из первых узлов вводится в конце, а его ID или PK выше, запрос не работает.

у для MySQL. Я уверен, что время работы значительно улучшится. Не проверял, но я бы сказал, что MySQL проходит через все строки, чтобы определить количество. Это означает, что за эти 59 секунд выполняется 10–40 миллиардов (количество строк в таблице * 10000).

Предположим, что SQL Server и Oracle создают индекс на лету. Если они это сделают, это будет только от 1 до 4 миллионов.

I верить вот что вы пытаетесь сделать:

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
 Amy B26 янв. 2009 г., 03:11
Похоже, это обрабатывает ноль детей для меня. +1
 Sean Bright26 янв. 2009 г., 03:08
Ах, верно. Хороший улов.
 cletus26 янв. 2009 г., 03:20
И левое внешнее объединение не сделает этого, потому что вы получите счетчик 1 для пустой строки, если вы не попытаетесь отфильтровать это. Правильное решение - ответ, который я отправил.
 cletus26 янв. 2009 г., 03:07
-1 Не обрабатывает ноль детей.
 cletus26 янв. 2009 г., 03:16
Попробуй. Если вы присоединитесь слева, когда детей нет, строки не будет, и в ней не будет «0 детей». Это просто не появится.

Ваш ответ на вопрос