Форум "Web-мастер"

Технологии web-програмирования

#0 VictorT © 26.07.06 19:42:02 - 31.07.06 14:37:40

Отображение данных из двух таблиц мастер/детаилс - какой подход лучше?

Отображение данных из двух таблиц мастер/детаилс - какой подход лучше?

Как вы считаете, какой из этих двух подходов лучше (по быстродействию, по требуемым ресурсам), и когда (при каких условиях)? И почему?

// Первый подход: сначала запросом получаем данные из мастер-таблицы,
// потом в цикле из второй.
<?php
$result = querySQL("select * from master_table");
echo "<table>";
while ($row = mysql_fetch_array($result)) {
    echo "<tr>";
    echo "<td>";
    echo $row["master_field"];
    echo "</td>";
    echo "<td>";
    $result2 = querySQL("select * from detail_table where id=" .$row["id"]);
    while ($row2 = mysql_fetch_array($result2)) {
        echo $row2["detaild_field"];
        echo "<br />";
    }
    echo "</td>";
    echo "</tr>";
}
echo "</table>";
?>



// Второй подход: одним запросом получаем обьеденённые данные из двух таблиц,
// при этом в результате имеем избыточность данных (многократно повторяются
// данные из мастер-таблицы), которые не используются при выводе.
<?php
$result = querySQL("select * from master_table left join detail_table
on detail_table.id=master_table.id");
echo "<table>";
$row = mysql_fetch_array($result);
$last_id == $row["id"];
$detail_field = "";
mysql_data_seek($result, 0);
while ($row = mysql_fetch_array($result)) {
    $detail_field .= $row["detaild_field"];
    $detail_field .= "<br />";
    if ($last_id != $row["id"]) {
        echo "<tr>";
        echo "<td>";
        echo $row["master_field"];
        echo "</td>";
        echo "<td>";
        echo $detail_field;
        echo "</td>";
        echo "</tr>";
        $detail_field = "";
    }
    $last_id == $row["id"]
}
?>

З.Ы. Тут рассмотрен случай только с двумя таблицами, хотелось бы услышать рассуждения и для случая, когда когда их больше.
З.З.Ы. Рассуждения желательно обоснованные, а не типа "я делаю так, потому что мне так нравится".

#1 Mystic © 26.07.06 20:27:31

А что быстрее, скопировать один файл размером 1 Gb, или или 1048576 файлов размером 1024 байта?

Конечно, зависит от конкретной БД, но второй подход обычно лучше. Основная причина состоит в том, что во втором случае сервер БД имеет больше информации о том, что нужно получить в итоге, и в состоянии более эффективно выбрать план выполнения запроса. Ты это выполнил циклом, но цикл можно составить и в обратном порядке (а потом отсортировать) или построить хэш и использовать его. Плюс исключаются траты на рабор SQL, подготовку к выполнению (постановка в очередь, ...) и прочей ерунды.

Ну и во время цикла данные могут изменится, поэтому запрос может вернуть не то, что преполагалось.

P. S. Если сервер БД понимает параметры, то лучше передавать значения через параметры, а не формировать соответствующий SQL. Причина кроется в том, что одна из стадий выполения запроса --- построение плана. Обычно многие сервера кэшируют запросы, и если им пришел точно такой же запрос, то они не строят плян выполнения, а берут уже готовый. Oracle используется посимвольное совпадение запросов. Построения плана запроса в некоторых БД достаточно дорогая операция, которая может заблокировать других пользователей.

#2 VictorT © 26.07.06 20:43:50

> А что быстрее, скопировать один файл размером 1 Gb, или
> или 1048576 файлов размером 1024 байта?

В данном случае не имеет место быть равенство:
1 файл * размер Х = N файлов * размер Y

Тут имееn место быть неравенство:
1 файл * размер Х >> N файлов * размер Y

> Основная причина состоит в том, что во втором случае сервер
> БД имеет больше информации о том, что нужно получить в итоге,
> и в состоянии более эффективно выбрать план выполнения запроса
Но так-же во втором случае сервер БД должен получить и передать веб-серверу значительно больше данных.

> Ну и во время цикла данные могут изменится, поэтому запрос
> может вернуть не то, что преполагалось.
Не задумывался, спасибо, что обратил внимание.

> Если сервер БД понимает параметры, то лучше передавать значения
> через параметры, а не формировать соответствующий SQL.
В данном случае MySQL, насколько я знаю, не понимает, во всяком случае в старых версиях.

#3 Deep © 26.07.06 21:01:06

> > Ну и во время цикла данные могут изменится, поэтому запрос
>
> > может вернуть не то, что преполагалось.
ИМХО, это один из ключевых моментов для больших объемов данных. Если же он некритичен, а критично время выаполнения скрипта -- тогда я бы советовал первый вариант из сабжа.
// Первый подход: сначала запросом получаем данные из мастер-таблицы,
// потом в цикле из второй.

Почему? Обычно это выгоднее для пользователя. Если во втором случае весь запрос выдаст информацию через 2 минуты(а может и больше? есть хорошие шансы нарваться на таймаут!), то в первом случае данные от сервера начнут поступать практически сразу же и подгружаться в браузер по мере выполнения запроса.
Что беря во внимание нетерпеливость интернет-пользователей -- тоже очень важно.

#4 Mystic © 26.07.06 21:02:56

> В данном случае не имеет место быть равенство:
> 1 файл * размер Х = N файлов * размер Y

Почему не имеет? Давай посчитаем :)
1 файл * 1G = 1K файлов * 1M

> Но так-же во втором случае сервер БД должен получить и передать
> веб-серверу значительно больше данных.

Получить он должен меньше данных

Пусть таблица A имеет 1000 записей. Давай сравним размер текст запроса

SELECT * FROM A, B WHERE A.ID = B.ID (36 байт)

с размером запроса

SELECT * FROM A (15 байт)

плюс 1000 запросов

SELECT * FROM B WHERE ID = ? (28 байт)

равно 15 + 28*1000 = 28015 байт.

Во вторых, есть еще некоторая служебная информация, которая возвращается сервером при каждом запросе. И т. д.

#5 VictorT © 27.07.06 10:02:12

> Если же он некритичен, а критично время выаполнения скрипта
в общем-то да, у меня этот случай.

> Если во втором случае весь запрос выдаст информацию через
> 2 минуты(а может и больше? есть хорошие шансы нарваться
> на таймаут!),
Какие-то взятые с потолка цыфры и непонятно про таймаут. В Первом случае таймаута не может быть?

> в первом случае данные от сервера начнут поступать практически
> сразу же и подгружаться в браузер по мере выполнения запроса.
совсем не обязательно, например если используется gzip.

> Почему не имеет? Давай посчитаем :)
> 1 файл * 1G = 1K файлов * 1M
Потому что тут в конце не 1М, а меньше.

> Получить он должен меньше данных
Да, я не правильно выразился, имел в виду результат выполнения запроса.
Размерами текста запроса, я думаю, можно в данном случае пренебречь, они незначительны по сравнению с размерами результатов.

#6 Deep © 27.07.06 12:19:29

> совсем не обязательно, например если используется gzip.
тогда будет не "моментально", но все равно быстрее будет, потому как запаковать и оправить порции по 100 кб для пользователя однозначно быстрее чем та же операция с 1 Мб.

> Какие-то взятые с потолка цыфры и непонятно про таймаут.
цифры конечно от птолка, просто для наглядности. А вероятность таймаута при пересылке 1Мб намного больше чем для порций по 100кб. Все еще зависит от канала и мощности сервера, но тем не менее.

#7 VictorT © 27.07.06 13:04:32

> но все равно быстрее будет, потому как запаковать и оправить
> порции по 100 кб для пользователя однозначно быстрее чем
> та же операция с 1 Мб.
никакой "порционности" в обоих подходах не будет.
> А вероятность таймаута при пересылке 1Мб намного больше
> чем для порций по 100кб.
А, ты же не сказал, о таймауте чего ты говорил? Сервера БД или PHP? Таймаут сервера БД обычно значительно больше таймаута PHP.

#8 Mystic © 27.07.06 14:12:32

>> Почему не имеет? Давай посчитаем :)
>> 1 файл * 1G = 1K файлов * 1M
> Потому что тут в конце не 1М, а меньше.

Почему меньше? Проще говоря, я имел в виду тот факт, что скопировать один файл размеров в 1Gb будет быстрее, чем скопировать тучу маленьких файлов, суммарным размером в 1Gb. Примерно та же аналогия и с БД. Конечно в случае конкретной БД надо проводить эксперимент, но в большинстве случаев один запрос должен быть производительнее многих. Причем чем интеллектуальнее БД, тем больше будет преимущество.

#9 VictorT © 27.07.06 14:50:45

> Почему меньше?
потому что во втором подходе мы многократно получаем одни и те же данные (из примари тейбл).

> Проще говоря, я имел в виду тот факт, что скопировать один
> файл размеров в 1Gb будет быстрее, чем скопировать тучу
> маленьких файлов, суммарным размером в 1Gb.
Я понял, что ты имел в виду, но в данном случае вопрос стоит немного по другому. Что быстрее, скопировать файл размером 1Gb, или много файлов суммарным размером 0.5Gb (к примеру).
> Конечно в случае конкретной БД надо проводить эксперимент,
Как раз уже минут 15 занимаюсь подготовкой эксперемента.

#10 VictorT © 27.07.06 20:05:58

Следственный эксперемент для моих условий показал незначительное преимущество по быстродействию второго подохода по сравнению с первым. Тестовая задача задача вторым способом выполняется около 5-ти секунд, первым - около 5.5 секунд.

#11 Kortez © 28.07.06 08:24:50

Теорию по этому вопросу не изучал, но на практике выяснил, что при достаточно объёмных таблицах левый джойн работает ощутимо быстрее. По крайней мере, в случае с IB.

#12 VictorT © 28.07.06 09:13:39

> левый джойн работает ощутимо быстрее
быстрее чего?

#13 Kortez © 28.07.06 11:19:53

> быстрее чего?

быстрее, чем конструкции типа "where parent_id = id"

помнится, Джонмен на мастаках даже объяснял природу этого явления, а может быть и не Джонмен, а может и не этого, но данные вылезают быстрее

#14 VictorT © 28.07.06 11:25:56

> #13 Kortez ©
Это да, так и есть, во всяком случае не медленее точно, потому рекомендуется для обьеденеия таблиц всегда использовать join, а where только для фильтрации. Оптимизатор тогда лучше понимает. Только ведь в данной теме не об этом речь.

#15 Kortez © 28.07.06 13:46:03

> Только ведь в данной теме не об этом речь

и об этом тоже. запросы-то у тебя разные, а в первом варианте ещё и запрос в цикле, как справедливо подметил Мистик.
Это очень ресурсоёмко. не стоит забывать, что клиентской части тоже требуется куча времени для подготовки запроса к БД. будь то БДЕ, Fib, mod_mysql...
кому-то больше, кому-то меньше, но всё равно нужно.

#16 Andrey © 28.07.06 14:19:35

>потому рекомендуется для обьеденеия таблиц всегда использовать join,
>а where только для фильтрации
Кем рекомендуется? Может ими еще рекомендуется смешивать в одном запросе объединения через джоины с не явными объеденениями в секции where? )
Кстати, ИМХО Кортез имел ввиду, что джоин всёравно быстрее отработает, чем запрос к родителю и куча маленьких запростов по чайлдам.

А вот слона никто и не заметил )

select a.* from a where a.cond = 1
select b.* from a, b where a.ref = b.ref and a.cond = 1

А господам озаботившимся "Ну и во время цикла данные могут изменится, поэтому запрос может вернуть не то, что преполагалось" советую более глубоко ознакомится с понятием "уровень изоляции транзакции", а именно с уровнем "Repeatable Read". А злобным буратинам пользующимся MySQL-ем... вобщем им я даж посоветовать ничего не могу )

>помнится, Джонмен на мастаках даже объяснял природу этого явления
А чего там объяснять, Мистик в посте #1 почти все описал. Могу добавить только что еще и затраты на общение по сети между сервером и клиентом значительно больше при передаче кучи маленьких запросов ибо у каждого есть своя обертка.

#17 VictorT © 28.07.06 15:33:42

> Кем рекомендуется?
Честно говоря не помню, где читал, но на мой взгляд такое обьеденение ещё и просто напросто читабельнее. Нужно поискать?

> Может ими еще рекомендуется смешивать в одном запросе объединения
> через джоины с не явными объеденениями в секции where?
Нет, смешивать "ими" (

) не рекомендуется.

> А вот слона никто и не заметил )
> select a.* from a where a.cond = 1
> select b.* from a, b where a.ref = b.ref and a.cond = 1
Что-то всё равно не заметил

#19 VictorT © 28.07.06 15:49:00

> А злобным буратинам пользующимся MySQL-ем... вобщем им я
> даж посоветовать ничего не могу )
Злобным буратинам (вроде меня) можно посоветовать перейти с таблиц типа MyISAM на таблицы типа InnoDB, которые поддерживают транзакции(правда работа с этим типом медленнее)

#20 Andrey © 29.07.06 19:21:26

Слон заключается в том что оба метода предолженых выше избыточны или по количеству запросов к БД, или по количеству данных возвращаемых клиенту.

Я же предлогаю компромисный вариант:
- избыточные данные не возвращаются (всего 2 запроса, и каждый возвращает исключительно запрашиваемые данные, без дублирований)
- количество запросов для любого набора дочерних записей будет равно 2

А на тему ссылки... Даж незнаю что сказать... это ведь описание от MSSQL, а на сколько я понимаю афтор сабжа злобный буратина (пользуется MySQL) и к MSSQL имеет мало отношения ) А единственное из того что написано в стате, что я могу посчитать преймуществом, это то что типа оптимизатор лучше понимает запросы с джонами. Честно сказать, для меня сомнительно что лучше (ну не думаю я что мелкомягкие на столько безсистемно подходят к составлению плана, наверняка должны быть явные хинты, хотя... короче хз, но доверия не внушает), ну да ладно, это на совести авторов статьи. А все остальное там это обьяснение работы джоинов + попытка привить стиль привычный автору статьи.

#21 VictorT © 30.07.06 11:32:34

> Слон заключается в том что оба метода предолженых выше избыточны
> или по количеству запросов к БД, или по количеству данных
> возвращаемых клиенту.
Согласен, потому и возник вопрос, какую из двух избыточностей (зол) выбрать.

> Я же предлогаю компромисный вариант:
> - избыточные данные не возвращаются (всего 2 запроса, и
> каждый возвращает исключительно запрашиваемые данные, без
> дублирований)
> - количество запросов для любого набора дочерних записей
> будет равно 2
Точно, хороший вариант, отказаться от обоих зол, спасибо. Как-то сразу не присмотрелся к запросам, не понял, что имеется в виду. Надо только теперь подумать, как лучше обработать єто на клиентской стороне.

<?php
$result = querySQL("select * from master_table order.by id");
$result2 = querySQL("select detail_table.* from master_table left join detail_table
on detail_table.id=master_table.id order.by master_table.id");
echo "<table>";
$row2 = mysql_fetch_array($result2);
while ($row = mysql_fetch_array($result)) {
        echo "<tr>";
        echo "<td>";
        echo $row["master_field"];
        echo "</td>";
        echo "<td>";
        while ($row["id"] != $row2["id"]) {
            echo $row["detail_field"];
            echo "<br />";
            $row2 = mysql_fetch_array($result2);
        }
        echo "</td>";
        echo "</tr>";
}
?>

Проверить пока для тех же условий, насколько выростит производительность, руки не дошли :(

#23 VictorT © 31.07.06 14:37:40

преимущество по скорости второго метода над первым оказалось около десятка процентов, третьего над втором - несколько десятков процентов.

Написать ответ