select *,
(select * from Client c on c.Product = p.Id) as ClientCount,
(select max(Date) from Client c on c.Product = p.Id) as LastClient,
from Product p
Здесь двада вложенных select, которые фильтруют один и тот же диапазон из ссылаемой таблицы. Можно ли сделать так, чтобы был только один скан второй таблицы?
Здравствуйте, Sаныч, Вы писали:
S>Есть запрос вида:
S>
S>select *,
S>(select * from Client c on c.Product = p.Id) as ClientCount,
S>(select max(Date) from Client c on c.Product = p.Id) as LastClient,
S>from Product p
S>
S>Здесь двада вложенных select, которые фильтруют один и тот же диапазон из ссылаемой таблицы. Можно ли сделать так, чтобы был только один скан второй таблицы?
Можно попробовать уменьшить кол-во чтений таблицы, переписав ваш запрос вида
select *,
(select count(*) from Client c where c.Product = p.Id) as ClientCount,
(select max(Date) from Client c where c.Product = p.Id) as LastClient
from Product p
в конструкцию с использованием cross/outer apply например:
select *
from Product p
cross apply
(
select max(Date) as LastClient, count(*) as ClientCount
from dbo.Client c where c.Product = p.Id
) pp
Либо через обычный left join, но подозреваю, что вы привели не весь запрос в качестве примера
select *
from Product p
left join
(
select max(Date) as LastClient, count(*) as ClientCount, Product
from dbo.Client
group by Product
) pp on pp.Product = p.Id
Здравствуйте, Olaf, Вы писали:
O>в конструкцию с использованием cross/outer apply например: O>
O>select *
O>from Product p
O>cross apply
O>(
O> select max(Date) as LastClient, count(*) as ClientCount
O> from dbo.Client c where c.Product = p.Id
O>) pp
O>
O>Либо через обычный left join, но подозреваю, что вы привели не весь запрос в качестве примера
Почти. Еще есть таблица с платежами. Тоесть выподится что-то наподобия Товар 1, Клиентов 16, Заплатили 20-ю платежами на сумму NNN рублей.
cross apply мне понравился как-то проще в синтаксисе. Спасибо. Если мне еще сейчас в запрос добавить таблицу с платежами, то мне нужно вложенный cross apply делать?
Здравствуйте, Sаныч, Вы писали:
S>Почти. Еще есть таблица с платежами. Тоесть выподится что-то наподобия Товар 1, Клиентов 16, Заплатили 20-ю платежами на сумму NNN рублей.
S>cross apply мне понравился как-то проще в синтаксисе. Спасибо. Если мне еще сейчас в запрос добавить таблицу с платежами, то мне нужно вложенный cross apply делать?
Технических ограничений на вложенность у cross/outer apply нет, поэтому здесь нужно смотреть на схему БД и задачу, которая решается. Альтернативные решения всегда можно сравнить по времени, операциям ввода-вывода и по плану запроса, выбрав лучшее.
S>Здесь двада вложенных select, которые фильтруют один и тот же диапазон из ссылаемой таблицы. Можно ли сделать так, чтобы был только один скан второй таблицы?
Чтобы с гарантией, нужен спул в плане. Его можно запихать либо обманув оптимизатор через optimize for, либо через recursive cte, который и так всегда спулится.
Увертки с джойнами и апплаями не помогут — оптимизатор слишком умный
Как пример.
create table Product(id int primary key, name nvarchar(max))
create table Client(id int primary key, product int not null references product(id), [date] smalldatetime not null)
go
insert into product (id, name) values(1,N'A'),(2,N'B'),(3,N'C')
insert into Client(id, product, date) values
(1,1,getdate() -1),
(2,1,getdate() +0 ),
(3,1,getdate() + 1),
(4,2,getdate() -10),
(5,2,getdate() +2 )
declare @lie int =0;select
p.*,
ClientCount = coalesce(ClientCount, 0),
LastClient
from Product p
left join (select count(*) ClientCount, max(date) LastClient, product from Client c group by product) T
on t.product = p.id or @lie =1
option(optimize for(@lie =1))
Здравствуйте, rm822, Вы писали:
S>>Здесь двада вложенных select, которые фильтруют один и тот же диапазон из ссылаемой таблицы. Можно ли сделать так, чтобы был только один скан второй таблицы? R>Чтобы с гарантией, нужен спул в плане. Его можно запихать либо обманув оптимизатор через optimize for, либо через recursive cte, который и так всегда спулится. R>Увертки с джойнами и апплаями не помогут — оптимизатор слишком умный
R>Как пример.
R>...
Я просьбу автора интерпретировал, как уменьшить количество чтений таблицы dbo.Client минимум в два раза, причем и решение по представленному запросу напрашивалось само. Строго говоря, ни один из предложенных мной вариантов не гарантирует одного чтения таблицы dbo.Client. Формально ваш ответ полностью решает задачу топикстартера, но нужны ли ему такие сложности именно с однократным чтением таблицы !?
Здравствуйте, Olaf, Вы писали:
O>поэтому здесь нужно смотреть на схему БД и задачу, которая решается.
Задачу я привел выше. Сейчас еще раз скопирую
Почти. Еще есть таблица с платежами. Тоесть выподится что-то наподобия Товар 1, Клиентов 16, Заплатили 20-ю платежами на сумму NNN рублей.
Как я понимаю, мне нужно что-то типа такого?
select *
from Product p
cross apply
(
select max(Date) as LastClient, count(*) as ClientCount
from dbo.Client c where c.Product = p.Id
cross apply
(
select count(*) as PaymentCount, sum(Amount) as TotalAmount from Payment p where p.Product = p.Id and p.Client = c.Id
)
) pp
За 100% синтаксис не ручаюсь, пишу прямо с планшета в текстовый редактор форума
Я правильно понимаю, при таком запросе будет по 1 разу просканированны 3 таблицы (Product, Client, Payment)?
Здравствуйте, Sаныч, Вы писали:
S>... S>Как я понимаю, мне нужно что-то типа такого? S>... S>За 100% синтаксис не ручаюсь, пишу прямо с планшета в текстовый редактор форума
Такой вариант, конечно, будет работать и возможно даже не плохо, по крайней мере, по сравнению с тем, что вы предлагали изначально. Но как я уже говорил ориентироваться нужно на объективные показатели производительности запроса – время, ввод-вывод и план.
S>Я правильно понимаю, при таком запросе будет по 1 разу просканированны 3 таблицы (Product, Client, Payment)?
Точное количество покажет план запроса и параметр Number Of Executions на операторе чтения. Но если рассмотреть простой вариант с оператором Nested Loops, который сгенерирует оптимизатор для этого запроса, то картина получится следующая:
dbo.Product — 1 чтение
dbo.Client — количество чтений, равное количеству записей выбранных из dbo.Product
dbo.Payment — количество чтений, равное количеству записей выбранных из dbo.Product умноженное на количество записей выбранных из dbo.Client
Опять же чтение не означает сканирование всей таблицы. Если у вас правильно созданы индексы на таблицах, несмотря на большое количество записей, обработка данных может происходить очень быстро за счет использования поиска.
В соседней ветке rm822 уже предложил вариант однократного чтения таблицы через спулинг. Мое же решение было призвано снизить количество обращений к таблице в два раза, но однократного чтения оно не гарантирует. Оператор Nested Loops организован как вложенный цикл, поэтому однократного чтения обоих последовательностей стандартным способом достичь невозможно. Только через ухищрения, которые привел рядом rm822.
Возникает встречный вопрос – для вас действительно важно прочитать по разу каждую из таблиц и получить данные? Оптимизатор у Microsoft совсем неплох, он разбирает 99% ситуаций (на мой взгляд) и строит оптимальный план, нужно только срезать показатели производительности и сравнить с альтернативными решениями.
Здравствуйте, Olaf, Вы писали:
O>Возникает встречный вопрос – для вас действительно важно прочитать по разу каждую из таблиц и получить данные? Оптимизатор у Microsoft совсем неплох, он разбирает 99% ситуаций (на мой взгляд) и строит оптимальный план, нужно только срезать показатели производительности и сравнить с альтернативными решениями.
Я бы не начал оптимизировать это, если бы не тормоза. Индексы конечно решили проблему значительно, но хотел бы довести решение до очень быстрого состояния, чтобы еще долго не возвращаться к этой части базы данных.
Здравствуйте, Sаныч, Вы писали:
S>Я бы не начал оптимизировать это, если бы не тормоза. Индексы конечно решили проблему значительно, но хотел бы довести решение до очень быстрого состояния, чтобы еще долго не возвращаться к этой части базы данных.
У вас есть альтернативное решение и вы уже можете сравнивать производительность. Необходимо, чтобы поля таблиц dbo.Client и dbo.Payment, участвующие во внешнем ключе FK, были снабжены индексами, причем в идеальном варианте покрывающими. В таком случае для оператора Nested Loops в плане запроса при обращении к внутренней таблице будет выполняться поиск (seek), вместо сканирования (scan).
O>Я просьбу автора интерпретировал, как уменьшить количество чтений таблицы dbo.Client минимум в два раза, причем и решение по представленному запросу напрашивалось само. Строго говоря, ни один из предложенных мной вариантов не гарантирует одного чтения таблицы dbo.Client. Формально ваш ответ полностью решает задачу топикстартера, но нужны ли ему такие сложности именно с однократным чтением таблицы !?
Раз задал полностью корректный вопрос (что непросто), значит знает что делает, сам как-нибудь разберется нужны ему там сложности или нет
S>Я бы не начал оптимизировать это, если бы не тормоза. Индексы конечно решили проблему значительно, но хотел бы довести решение до очень быстрого состояния, чтобы еще долго не возвращаться к этой части базы данных.
Actual план пришли в виде файла или хмлки (не картинку), иначе беспредметно
Здравствуйте, Olaf, Вы писали:
O>Здравствуйте, Sаныч, Вы писали:
S>>Я бы не начал оптимизировать это, если бы не тормоза. Индексы конечно решили проблему значительно, но хотел бы довести решение до очень быстрого состояния, чтобы еще долго не возвращаться к этой части базы данных.
O>У вас есть альтернативное решение и вы уже можете сравнивать производительность. Необходимо, чтобы поля таблиц dbo.Client и dbo.Payment, участвующие во внешнем ключе FK, были снабжены индексами, причем в идеальном варианте покрывающими. В таком случае для оператора Nested Loops в плане запроса при обращении к внутренней таблице будет выполняться поиск (seek), вместо сканирования (scan).
Что-то не влезает с вложенными cross apply. Синтаксис неверный, пишет, что колонки из вложенного cross apply is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Здравствуйте, Sаныч, Вы писали: S>Что-то не влезает с вложенными cross apply. Синтаксис неверный, пишет, что колонки из вложенного cross apply is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Подозреваю, что на уровне dbo.Client, когда вы ссылаетесь на агрегированные данные из dbo.Payment вам нужно использовать агрегирующие функции для PaymentCount и TotalAmount. Например (зависит от задачи): max(PaymentCount) и max(TotalAmount)
select *
from Product p
outer apply
(
select max(Date) as LastClient, count(*) as ClientCount, max(PaymentCount) as PaymentCount, max(TotalAmount) as TotalAmountfrom dbo.Client c where c.Product = p.Id
outer apply
(
select count(*) as PaymentCount, sum(Amount) as TotalAmount from Payment p where p.Product = p.Id and p.Client = c.Id
)
) pp
Кстати, вы использовали коррелированный подзапрос изначально, поэтому в вашем случае лучше использовать outer apply.
Здравствуйте, Sаныч, Вы писали:
S>Есть запрос вида:
S>
S>select *,
S>(select * from Client c on c.Product = p.Id) as ClientCount,
S>(select max(Date) from Client c on c.Product = p.Id) as LastClient,
S>from Product p
S>
S>Здесь двада вложенных select, которые фильтруют один и тот же диапазон из ссылаемой таблицы. Можно ли сделать так, чтобы был только один скан второй таблицы?
select Distinct p.*, count(*) over (partition by c.Product), max(Date) over (partition by c.Product) from Product p inner join Client c on c.Product = p.Id
Здравствуйте, kgrach, Вы писали:
>>Уменьшить nested loops MS SQL 2014 >
select Distinct p.*, count(*) over (partition by c.Product), max(Date) over (partition by c.Product) from Product p inner join Client c on c.Product = p.Id
План выполнения проверьте, сюрприз будет
Схему у ув. rm822 в ответе можно подсмотреть, для полной красоты на c.Product индекс можно навесить.
Здравствуйте, Sinix, Вы писали:
S>Здравствуйте, kgrach, Вы писали:
>>>Уменьшить nested loops MS SQL 2014 >> S>
S>select Distinct p.*, count(*) over (partition by c.Product), max(Date) over (partition by c.Product) from Product p inner join Client c on c.Product = p.Id
S>
S>План выполнения проверьте, сюрприз будет S>Схему у ув. rm822 в ответе можно подсмотреть, для полной красоты на c.Product индекс можно навесить.
По стоимости плана почти тоже самое.
Вот у Olaf заметно быстрее
Здравствуйте, kgrach, Вы писали:
K>По стоимости плана почти тоже самое. K>Вот у Olaf заметно быстрее
Ага, криво написал, сорри. С вариантом Olaf и сравнивал
