SQL запросы

Обсуждение запросов на SQL и проблем с ними.

начну с бонального и распространенного:

есть две связаные таблицы -
users
__________________________
id | name | vozrost | profesia
------------------------------
1 | Федя | 21 год | 2
2 | Нина | 18 лет | 3
3 | Гена | 24 года| 5

t_profesia
__________________________
id | prof_name
------------------------------
1 | Программист
2 | Строитель
3 | Доктор
4 | Артист
5 | Учитель

Вопрос - как составить запрос чтоб результат был таков:
__________________________
id | name | vozrost | profesia
------------------------------
1 | Федя | 21 год | Строитель
2 | Нина | 18 лет | Доктор
3 | Гена | 24 года| Учитель

[EvroStandart]

А в какой программе?

В мускле должно сработать:
SELECT u.id, u.name, u.vozrost, p.prof_name FROM users AS u
LEFT JOIN t_profesia AS p ON p.id=u.profesia

[_Lynx_]

можно и без джойнов обойтись
SELECT id, name, vozrost, (select prof_name from t_profesia where id=u.profesia) profesia
FROM users u

[uerter]

Сообщение от _Lynx_

можно и без джойнов обойтись
SELECT id, name, vozrost, (select prof_name from t_profesia where id=u.profesia) profesia
FROM users u

да но как правило то такие запросы тормозят!
такчто предыдущий вариант более подходящий

[nikotano]

SELECT u.id, u.name, u.vozrost, p.name FROM users u, t_profesia p WHERE u.profesia = p.id

Сообщение от nikotano

SELECT u.id, u.name, u.vozrost, p.name FROM users u, t_profesia p WHERE u.profesia = p.id

я думаю это оптимальный.

[_Lynx_]

uerter:
да но как правило то такие запросы тормозят!
такчто предыдущий вариант более подходящий

С джойнами тоже случаются тормоза на больших пересечениях

Попробовал на таблицах ~4млн. х 1млн.
Простые подсчёты:
мой вариант вариант 24 сек.
EvroStandart - 11 сек.
nikotano - 2мин.37сек.

.. да ладно, на какой пралформе тестил.. ?
это серьезный момент - о скорости..

Сообщение от _Lynx_

С джойнами тоже случаются тормоза на больших пересечениях

Попробовал на таблицах ~4млн. х 1млн.
Простые подсчёты:
мой вариант вариант 24 сек.
EvroStandart - 11 сек.
nikotano - 2мин.37сек.

- по твоим словам, EvroStandart - самы нормальный.. запрос составил..
EvroStandart - о как..

[Gr@nd@d]

Сообщение от _Lynx_

С джойнами тоже случаются тормоза на больших пересечениях

На самом деле вопрос стоит так - даешь ли ты оптимизацию запроса на откуп оптимизатору движка или "тыкаешь его носом". В данном случае важно есть ли индексы по связанным полям, вес индексов, наполненность таблицы и т.п. Если данных мало или наоборот много, оптимизатор может решить что ему проще отобрать нужные значения простым перебором. В общем случае варианты nokitano и evro почти одинаковы, но у nikotano более "универсальный" - считается что оптимизатор умнее и лучше знает что ему надо

Сообщение от Gr@nd@d

На самом деле вопрос стоит так - даешь ли ты оптимизацию запроса на откуп оптимизатору движка или "тыкаешь его носом". В данном случае важно есть ли индексы по связанным полям, вес индексов, наполненность таблицы и т.п. Если данных мало или наоборот много, оптимизатор может решить что ему проще отобрать нужные значения простым перебором. В общем случае варианты nokitano и evro почти одинаковы, но у nikotano более "универсальный" - считается что оптимизатор умнее и лучше знает что ему надо

- как же быть с результатами тестирования? проведенными _Lynx_
Почему "умный" отимизатор не додумался их оптимизировать и вывести равное время..?

[Gunslinger]

Стоит отметить что запросы EvroStandart и nikotano различаються. В наведеном примере они вернут одинаковый результат. Но н-р, если не указывать професию (тоесть поставить Null) у юзера, то запрос EvroStandart его выберет, а nikotano - нет.

В любом случае более правильным подходом в таких случаях (обьединения таблиц), лучше исспользовать джоины. И не только в соображениях производительности, но и в восприятии запроса человеком. Запросы с джоинами более читабельные (особенно если нужно обьединять несколько таблиц + разнообразные условия по выборке)

[yan_kos]

Gunslinger
Не согласен!!! запрос nikotano болиее универсальный и логически верный. Зачем нам записи пиплов без професий... ето раз.
Во вторых запросы через джоин болиее сложны в чтение
В третьих если юзать движок без внешн. ключей, таким образом ожно добавить ищо один пункт к однозначности запесей.

[Madness]

yan_kos
А что насчет теста _Lynx_??? Полностью согласен с Gunslinger.

В vbulletin повсеместно, например, используются join. Был один запрос как у nikotano, так в новой версии mysql он стал приводить к ошибке.

[nikotano]

Прогнал все три запроса по два раза в IBExpert.
Firebird 1.0.2.
Записей в основной таблице > 1100000, во второй ~ 350000.
Вот результаты:

nikotano

------ Performance info ------
Prepare time = 0ms
Execute time = 1m 5s 765ms
Avg fetch time = 0,06 ms
Current memory = 13 186 048
Max memory = 13 320 296
Memory buffers = 10 000
Reads from disk to cache = 184 898
Writes from cache to disk = 0
Fetches from cache = 4 525 077

------ Performance info ------
Prepare time = 15ms
Execute time = 45s 375ms
Avg fetch time = 0,04 ms
Current memory = 13 186 048
Max memory = 13 325 416
Memory buffers = 10 000
Reads from disk to cache = 184 898
Writes from cache to disk = 0
Fetches from cache = 4 525 077

_Lynx_

------ Performance info ------
Prepare time = 16ms
Execute time = 50s 937ms
Avg fetch time = 0,04 ms
Current memory = 13 178 880
Max memory = 13 325 416
Memory buffers = 10 000
Reads from disk to cache = 175 366
Writes from cache to disk = 6
Fetches from cache = 9 622 151

------ Performance info ------
Prepare time = 0ms
Execute time = 1m 11s 766ms
Avg fetch time = 0,06 ms
Current memory = 13 178 880
Max memory = 13 325 416
Memory buffers = 10 000
Reads from disk to cache = 175 366
Writes from cache to disk = 6
Fetches from cache = 9 622 151

EvroStandart

------ Performance info ------
Prepare time = 0ms
Execute time = 1m 6s 875ms
Avg fetch time = 0,06 ms
Current memory = 13 177 856
Max memory = 13 325 416
Memory buffers = 10 000
Reads from disk to cache = 175 271
Writes from cache to disk = 0
Fetches from cache = 9 621 045

------ Performance info ------
Prepare time = 16ms
Execute time = 46s 297ms
Avg fetch time = 0,04 ms
Current memory = 13 177 856
Max memory = 13 325 416
Memory buffers = 10 000
Reads from disk to cache = 175 271
Writes from cache to disk = 0
Fetches from cache = 9 621 045

Система:
Windows XP Prof
Intel P4 2.4 GHz, 512RAM.

kogan:
Но н-р, если не указывать професию (тоесть поставить Null) у юзера, то запрос EvroStandart его выберет, а nikotano - нет.

Это решается при помощи внешнего ключа.

[Gr@nd@d]

Сообщение от kogan

- как же быть с результатами тестирования? проведенными _Lynx_ Почему "умный" отимизатор не додумался их оптимизировать и вывести равное время..?

Потому! Это зависит от нескольких параметров:
какие данные задействованы в тесте (наполненность таблиц)
что за сервер - оптимизаторы "умные" поразному, одни больше, другие меньше
Какие ключи/индексы есть и в каком состоянии.
И т.п.
Я имел ввиду, что при равных условиях результаты будут близки.
Но один "универсальнее" - больше дает свободы оптимизатору, соответственно переносимее с платформы на платформу и менее зависит от данных и структуры.

PS: Погляди на среднее время фетча в предыдущем письме...

[Gunslinger]

yan_kos:
Не согласен!!! запрос nikotano болиее универсальный и логически верный. Зачем нам записи пиплов без професий... ето раз.

Если не надо то вместо Left join пишем inner join, и не будет здесь пиплов без професий. Тоесть всего-то меняем одно слово, а если нам вдруг понадобяться пиплы без професий, то в запросе nikotano придеться добавить еще одно условие, что менее универсально согласитесь.

yan_kos:
Во вторых запросы через джоин болиее сложны в чтение

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

nikotano:
Это решается при помощи внешнего ключа.

Всмысле?

[nikotano]

Gunslinger:
Всмысле?

Внешний ключ + NOT NULL.
Неточно выразился.

[Gunslinger]

nikotano:
Внешний ключ + NOT NULL.
Неточно выразился.

NOT NULL тебе не даст возможность ввести нулловое значение (и внешний ключ тут ни при чем), а иногда как раз нуловое и надо. Даже в нашем случае можно сказать, н-р, что если нулл, тогда значит професия не указана.

[nikotano]

Gunslinger:
NOT NULL тебе не даст возможность ввести нулловое значение (и внешний ключ тут ни при чем), а иногда как раз нуловое и надо. Даже в нашем случае можно сказать, н-р, что если нулл, тогда значит професия не указана.

Логично.
Но я обычно вношу системную запись "Не указано" с идентификатором 0 и ставлю 0 как дефолтовое значение.

Ну это дело вкуса.

[Gr@nd@d]

Сообщение от Gunslinger

Если не надо то вместо Left join пишем inner join, и не будет здесь пиплов без професий. Тоесть всего-то меняем одно слово, а если нам вдруг понадобяться пиплы без професий, то в запросе nikotano придеться добавить еще одно условие, что менее универсально согласитесь.

Позволь не согласиться.
Как раз условия в запросе более универсальны при переносе между разными платформами/версиями/и т.п.
Кроме того куча софта, использующего QBE умеет парсить/модифицировать именно условия.
Ну и кроме прочего, говоря join ты указываешь оптимизатору что к чему цепляется (left\right join). Что в общем случае не универсально.

PS: заметь - я не говорю, что join это плохо, сам пользуюсь но речь о универсальности идет, а это другая песня...