Достаточно типичные и простые задачи выливаются в нагромождение запросов и при больших объемах они еще и могут неадекватно тормозить.
Пример — две таблицы, в первой допустим продажа клиенту, с полями дата, клиент,.... В связанной детализация продажи — товар, количество, цена.
Нужен отчет типа клиент и самая дорогая позиция в чеке. И вот элементарно просто такой простой случай не реализуем.
С вложенными запросами могут быть тормоза в сложных случаях. С созданием временной таблицы и затем вторым запросом выборка из нее время более адекватно, но красивости нет. Неужели SQL настолько ничтожен, что элементарное так ужасно получить?
P.S. Ну а нужно что то типа left join top 1, т.е. выбрать не все, а присоединить что-то одно с учетом сортировки например.
Здравствуйте, _ilya_, Вы писали:
__>Достаточно типичные и простые задачи выливаются в нагромождение запросов и при больших объемах они еще и могут неадекватно тормозить. __>Пример — две таблицы, в первой допустим продажа клиенту, с полями дата, клиент,.... В связанной детализация продажи — товар, количество, цена. __>Нужен отчет типа клиент и самая дорогая позиция в чеке. И вот элементарно просто такой простой случай не реализуем.
...
__>P.S. Ну а нужно что то типа left join top 1, т.е. выбрать не все, а присоединить что-то одно с учетом сортировки например.
RANK() или ROW_NUMBER() поверх обычного джойна с фильтром по результату ranking function
Здравствуйте, _ilya_, Вы писали:
__>Достаточно типичные и простые задачи выливаются в нагромождение запросов и при больших объемах они еще и могут неадекватно тормозить. __>Пример — две таблицы, в первой допустим продажа клиенту, с полями дата, клиент,.... В связанной детализация продажи — товар, количество, цена. __>Нужен отчет типа клиент и самая дорогая позиция в чеке. И вот элементарно просто такой простой случай не реализуем.
Что-то типа этого нужно, что ли?
;with p as (
select itemId, clientID, price, row_number() over (partition by clientID order by price desc) rn
from t1
join t2 on t1.id = t2.t1id
)
select itemID, clientID, price
from p
where rn = 1
И что в этом нереализуемого и чем это хуже придуманного тобой (пример, кстати, напиши полностью)?
Здравствуйте, _ilya_, Вы писали:
__>Достаточно типичные и простые задачи выливаются в нагромождение запросов и при больших объемах они еще и могут неадекватно тормозить. __>Пример — две таблицы, в первой допустим продажа клиенту, с полями дата, клиент,.... В связанной детализация продажи — товар, количество, цена. __>Нужен отчет типа клиент и самая дорогая позиция в чеке. И вот элементарно просто такой простой случай не реализуем.
__>С вложенными запросами могут быть тормоза в сложных случаях. С созданием временной таблицы и затем вторым запросом выборка из нее время более адекватно, но красивости нет. Неужели SQL настолько ничтожен, что элементарное так ужасно получить?
__>P.S. Ну а нужно что то типа left join top 1, т.е. выбрать не все, а присоединить что-то одно с учетом сортировки например.
Здравствуйте, _ilya_, Вы писали:
__>...
__>С вложенными запросами могут быть тормоза в сложных случаях. С созданием временной таблицы и затем вторым запросом выборка из нее время более адекватно, но красивости нет. Неужели SQL настолько ничтожен, что элементарное так ужасно получить?
__>P.S. Ну а нужно что то типа left join top 1, т.е. выбрать не все, а присоединить что-то одно с учетом сортировки например.
Здравствуйте, _ilya_, Вы писали:
__>Достаточно типичные и простые задачи выливаются в нагромождение запросов и при больших объемах они еще и могут неадекватно тормозить.
При больших объёмах всё может тормозить...
__>Пример — две таблицы, в первой допустим продажа клиенту, с полями дата, клиент,.... В связанной детализация продажи — товар, количество, цена. __>Нужен отчет типа клиент и самая дорогая позиция в чеке. И вот элементарно просто такой простой случай не реализуем.
JOIN+GROUP BY
__>С вложенными запросами могут быть тормоза в сложных случаях.
C чего ты взял ?
С созданием временной таблицы и затем вторым запросом выборка из нее время более адекватно, но красивости нет. Неужели SQL настолько ничтожен, что элементарное так ужасно получить?
Здравствуйте, MasterZiv, Вы писали:
__>>С вложенными запросами могут быть тормоза в сложных случаях.
MZ>C чего ты взял ?
Вообщем все переписано на left join и временные таблицы. Скорость — даже не ожидал что так быстро будет. Код конечно многостраничный, чтобы притянуть вот то поле из вложенной вот в ту вложенную и только первое (одно значение), а если связей нет то NULL. Весь запрос разрастается на десяток страниц и это ад, если не осведомленному в таком мракобесии разбираться. Тривиальная штука выливается в трудно осмысленный код когда его еще и много.
Вложенный запрос вида select * from #T99 as fignia where fignia.link in (select link from #T98) неадекватно убивает сервер даже на сотни тысячах записей. На дохлом тест сервере 20 минут, на рабочем и вроде как крутом 1 час 40 в не рабочее время! Заодно порешали аппаратную проблему с рабочим сервером. Left join (top 1) (sort by) — типа присоединить один отсортировав выборку по таким то полям — крайне не хватает для лаконичности, наверно и на скорость бы повлияло особенно если нужно присоединить одно первое попавшееся поле.
Здравствуйте, _ilya_, Вы писали:
__>Вложенный запрос вида select * from #T99 as fignia where fignia.link in (select link from #T98) неадекватно убивает сервер даже на сотни тысячах записей. На дохлом тест сервере 20 минут, на рабочем и вроде как крутом 1 час 40 в не рабочее время! Заодно порешали аппаратную проблему с рабочим сервером. Left join (top 1) (sort by) — типа присоединить один отсортировав выборку по таким то полям — крайне не хватает для лаконичности, наверно и на скорость бы повлияло особенно если нужно присоединить одно первое попавшееся поле.
Сдается мне, что если вы возьмете в штат хоть одного человека, умеющего в РСУДБ, это всё превратится в изящный код, который легко
читать и поддерживать. Ну и время выполнения уменьшится, разумеется.
Здравствуйте, _ilya_, Вы писали:
__>... Left join (top 1) (sort by) — типа присоединить один отсортировав выборку по таким то полям — крайне не хватает для лаконичности, наверно и на скорость бы повлияло особенно если нужно присоединить одно первое попавшееся поле.
Вы до сих пор не назвали ни СУБД ни версию, чтобы правильно ответить на ваш вопрос. А в SQL Server 2005+, например, для "лаконичности" есть конструкция OUTER APPLY, которая позволяет реализовать вашу задачу:
select *
from dbo.T1 t1
outer apply
(
select top 1 *
from dbo.T2 t2
where t1.T1Id = t2.T1Id
order by t2.Name
) a
Здравствуйте, _ABC_, Вы писали:
_AB>Сдается мне, что если вы возьмете в штат хоть одного человека, умеющего в РСУДБ, это всё превратится в изящный код, который легко _AB>читать и поддерживать. Ну и время выполнения уменьшится, разумеется.
Знающих в MS SQL таких пока 0 и таких вакансий 10 лет не открывалось, есть только спецы по Oracle но они не помошники. Без всякого одного, теперь это выполняется за 2 минуты — в результате 150тыс записей с тучей left join и временных таблиц. Видимо иначе никак не будет адекватно быстро работать. На багах учатся... Код конечно разросся и мало читабелен, но стал крайне шустро работать. Из базы в 40GB выбрать много связанного с "нерешенным" для sql left join first 1 выглядит более чем достаточная скорость. Изящности точно не будет — временные таблицы, выборка из них первого во временную, далее включение в основной запрос.
O>Вы до сих пор не назвали ни СУБД ни версию, чтобы правильно ответить на ваш вопрос. А в SQL Server 2005+, например, [/sql]
SQl — MS 2012, но промежуточный движок и Oracle и PostgreSql поддерживает, так что запросы ограничены скорее крайне древним диалектом SQL. Если не все SQL такие конструкции переваривают, то и нет поддержки даже если при таком уникальном сервере такая конструкция работоспособна.
Вообще без временных таблиц возможно ли присоединить к выборке из связанной таблицы не все, а только 1 элемент с такой то сортировкой, если связь многие ко многим?
Такое ощущение что изначально такое не заложено в язык и только временные таблицы (или подзапросы, которые жутко тормознее) спасают.
Здравствуйте, _ilya_, Вы писали:
__>Знающих в MS SQL таких пока 0 и таких вакансий 10 лет не открывалось, есть только спецы по Oracle но они не помошники. Без всякого одного, теперь это выполняется за 2 минуты — в результате 150тыс записей с тучей left join и временных таблиц. Видимо иначе никак не будет адекватно быстро работать.
Вот это "видимо" крайне сомнительно. Крайне. Я это к тому, что не надо обвинять инструмент, который ты не знаешь и делать выводы по нему.
Здравствуйте, _ABC_, Вы писали:
_AB>Вот это "видимо" крайне сомнительно. Крайне. Я это к тому, что не надо обвинять инструмент, который ты не знаешь и делать выводы по нему.
Я просто не вижу простого решения присоединить таблицу и если есть соответствие, то выбрать только одно значение а не все (с учетом моей заданной сортировки). Это в классическом SQL, разные подварианты SQL серверов видимо имеют такие возможности, но нифига не обобщен стандарт на такое.
Чтобы на всех платформах было просто и лаконично — выбрать например все продажи и максимальную цену из чека... Чего проще, то — все из первой и при наличии связи например max(price) из связанной второй таблицы?
Вся такая не лаконичность и разность платформ, выливается в то, что например спецы по Oracle которым много денег платят, не отвечают за MS SQL, а если в MS SQL такой запрос писать без временных таблиц, а на сильно тормозящих вложенных подзапросах, то все выливается в мегафейл. Тут и к оптимизатору SQL сервера тоже вопросы... написано 2 запроса — через left join и через вложенный запрос. Разница по скорости — порядок, а результат и замысел естественно один ибо запрос одинаков по сути и возвращает всегда одинаковый результат.
Здравствуйте, _ilya_, Вы писали:
__>Вся такая не лаконичность и разность платформ, выливается в то, что например спецы по Oracle которым много денег платят, не отвечают за MS SQL
А специалисты по Java не отвечают за .Net...
__>а если в MS SQL такой запрос писать без временных таблиц, а на сильно тормозящих вложенных подзапросах, то все выливается в мегафейл.
А если использовать современный синтаксис T-SQL, то временные таблицы по-прежнему нужны?
Если нет, то какие претензии к СУБД?
__>Тут и к оптимизатору SQL сервера тоже вопросы... написано 2 запроса — через left join и через вложенный запрос. Разница по скорости — порядок, а результат и замысел
Почему left join противопоставляется вложенному запросу? Вложенный запрос может быть присоединен через left join.
Покажи, что ты имеешь в виду, пожалуйста. Тестовые данные для твоего примера.
create table t1 (id int identity primary key, clientID int, _date date)
create table t2 (id int identity primary key, t1ID int, itemID int, price money)
alter table t2 add constraint fk_t1ID foreign key (t1ID) references t1(id)
insert into t1 (clientID, _date)
select 1, '2017-01-01' union all
select 2, '2017-01-01'
insert into t2(t1ID, itemID, price)
select 1, 1, 100 union all
select 1, 2, 200 union all
select 2, 3, 110 union all
select 2, 4, 220
Здравствуйте, _ABC_, Вы писали:
__>>Вся такая не лаконичность и разность платформ, выливается в то, что например спецы по Oracle которым много денег платят, не отвечают за MS SQL _AB>А специалисты по Java не отвечают за .Net...
__>>Тут и к оптимизатору SQL сервера тоже вопросы... написано 2 запроса — через left join и через вложенный запрос. Разница по скорости — порядок, а результат и замысел _AB>Почему left join противопоставляется вложенному запросу? Вложенный запрос может быть присоединен через left join. _AB>Покажи, что ты имеешь в виду, пожалуйста. Тестовые данные для твоего примера. _AB>[/sql]
Диалекты SQl не должны так откровенно разделять специалистов... Думаю что если загнется один из подвидов, то что будут делать те, кто не в курсе остальных разновидностей? Может им стоит знать немного и про конкурирующие диалекты? По факту они не знают и не умеют ничего — тот же MS SQL DTS крайне полезное открытие, для тех кто знает Oracle если проблемы с типами при переносе данных, когда тупо из MS не могут забрать данные.
По скорости left join против вложенного подзапроса, select * from xren as t1 where t1.id in (select id from t2) крайне медленно работает. Видимо подзапросы вовсе не оптимизируются с привязкой к верхнеуровнему запросу и когда много вложений там ад с таким. Собственно видимо устаревшая конструкция классического sql и временные таблицы полностью заменили эту классику как подзапросы. Но совместимость должна обеспечиваться, ибо древний язык.
Здравствуйте, _ilya_, Вы писали:
__>Диалекты SQl не должны так откровенно разделять специалистов...
Кому не должны?
Почему разные языки программирования имеют право откровенно разделять специалистов, а СУБД не могут?
__>Думаю что если загнется один из подвидов, то что будут делать те, кто не в курсе остальных разновидностей? Может им стоит знать немного и про конкурирующие диалекты?
Для общего развития — разумеется стоит. Для работы — особо не нужно.
__>По факту они не знают и не умеют ничего — тот же MS SQL DTS крайне полезное открытие
SSIS (бывший DTS) не относится к диалекту языка (Transact-SQL) вообще никак.
__>По скорости left join против вложенного подзапроса, select * from xren as t1 where t1.id in (select id from t2) крайне медленно работает.
Начнем с того, что
select *
from xren as t1
where t1.id in (select id from t2)
Это вовсе не то же самое, что
select *
from xren as t1
left join t2 on t1.id = t2.id
where t1.id in (select id from t2)
И даже если left join заменить на inner join, всё равно это разные по смыслу запросы, которые могут
давать один и тот же результат лишь в частном случае. Поэтому совершенно логично, если в общем случае
у этих двух запросов будут разные планы с разной производительностью.
__>Видимо подзапросы вовсе не оптимизируются с привязкой к верхнеуровнему запросу и когда много вложений там ад с таким.
Оптимизируются. Но SQL — интерпретируемый язык и оптимизатору нужно выбирать между скоростью подбора плана и его оптимальностью.
Я не думаю, что ты бы хотел, чтобы сервер каждый раз выбирал оптимальный план по несколько секунд.
Поэтому для сложных запросов часты ситуации, когда план выбран с причиной "good enough plan found" или даже "timeout".
Исправить ситуацию можно разными способами, начиная от тривиального упрощения запросов, до подсказок оптимизатору на
уровне check constraint и статистик, или прямых указаний хинтов (что, в свою очередь, может привести к проблемам в будущем).
__>Но совместимость должна обеспечиваться, ибо древний язык.
А все программисты должны писать на C.
Здравствуйте, _ilya_, Вы писали:
__>SQl — MS 2012, но промежуточный движок и Oracle и PostgreSql поддерживает, так что запросы ограничены скорее крайне древним диалектом SQL. Если не все SQL такие конструкции переваривают, то и нет поддержки даже если при таком уникальном сервере такая конструкция работоспособна.
__>Вообще без временных таблиц возможно ли присоединить к выборке из связанной таблицы не все, а только 1 элемент с такой то сортировкой, если связь многие ко многим? __>Такое ощущение что изначально такое не заложено в язык и только временные таблицы (или подзапросы, которые жутко тормознее) спасают.
Так если нужно поддерживать реализацию для нескольких СУБД, значит нужно стремиться к использованию стандарта ANSI SQL. В частности в SQL:2003 были введены оконные функции в лице row_number. Я очень бегло посмотрел, но PostgreSQL и Oracle их по-моему поддерживают, зависит конечно от версии. Собственно самым первым ответом в этом топике, было предложено использовать данный подход, а именно:
select *
from dbo.T1 t1
left join
(
select *, row_number() over(partition by t2.T1Id order by t2.Name) as n
from dbo.T2 t2
) t2 on t1.T1Id = t2.T1Id and t2.n = 1
__>Достаточно типичные и простые задачи выливаются в нагромождение запросов и при больших объемах они еще и могут неадекватно тормозить. __>Пример — две таблицы, в первой допустим продажа клиенту, с полями дата, клиент,.... В связанной детализация продажи — товар, количество, цена.
__>С вложенными запросами могут быть тормоза в сложных случаях. С созданием временной таблицы и затем вторым запросом выборка из нее время более адекватно, но красивости нет.
Стесняюсь спросить, а в продажах индекс по ID продажи какой-нибудь есть?
А то видел как-то жалобы на производительность в похожем случае, и оказалась такая оказия.
Здравствуйте, _ilya_, Вы писали: __>Знающих в MS SQL таких пока 0 и таких вакансий 10 лет не открывалось, есть только спецы по Oracle но они не помошники. Без всякого одного, теперь это выполняется за 2 минуты — в результате 150тыс записей с тучей left join и временных таблиц.
Я уже лет 20 не видел ситуаций, где временные таблицы были бы как-то оправданы.
Вам остро не хватает человека, способного читать и анализировать execution plan.
Без него ваши попытки обречены — через пару лет ваш код с с тучей left join и временных таблиц станет беспричинно тормозить.
Уйдемте отсюда, Румата! У вас слишком богатые погреба.
__>Вложенный запрос вида select * from #T99 as fignia where fignia.link in (select link from #T98) неадекватно убивает сервер даже на сотни тысячах записей.
У меня опыт весьма специфический. Но по нему у меня получалось, что
1. При большом результате лучше использовать exists вместо in
2. Вариант соединения вместо exists работает примерно также.
Поэтому: индексы + запрос.
При этом я несколько потерялся — сначала речь идет про join, а потом плавно переходит на in
