Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Razacharovan  [new]
Lepsik
Member

Откуда: glubinka
Сообщений: 4256
чисто теоритический вопрос - почему при выполнении не откидываются заведомо неиспользуемые части запроса. Я бы так предпологал что план запроса :

select name from dbo.t1
       left join dbo.t2 on t1.id = t2.id
       left join dbo.t3 on t1.id = t3.id


должен показывать только
select name from dbo.t1
4 май 15, 22:16    [17599419]     Ответить | Цитировать Сообщить модератору
 Re: Razacharovan  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
Lepsik
должен показывать только
select name from dbo.t1
В общем случае не должен. Если t2.id и t3.id PK или для них есть ограничения уникальности, то оптимизатор исключит эти таблицы из плана выполнения.
4 май 15, 22:35    [17599473]     Ответить | Цитировать Сообщить модератору
 Re: Razacharovan  [new]
Lepsik
Member

Откуда: glubinka
Сообщений: 4256
забыл запостить таблицы.

Идея в том что в запросе я указываю только поля из таблицы t1 и мне собственно все равно что есть такие-же id в t2, t3.

create table t1 ( id int not null primary key, name sysname)
insert into t1 SELECT id, name FROM (VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd'), (5, 'e')) x(id, name);
create table t2 ( id int not null primary key, code int)
insert into t2 SELECT id, code FROM (VALUES (1, 11), (2, 22), (3, 33), (4, 44)) x(id, code);
create table t3 ( id int not null primary key, field real)
insert into t3 SELECT id, field FROM (VALUES (2, 2.1), (3, 3.3), (4, 42.), (5, 5.4)) x(id, field);
4 май 15, 23:48    [17599655]     Ответить | Цитировать Сообщить модератору
 Re: Razacharovan  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
Мда. Ваш же пример:
create table t1 ( id int not null primary key, name sysname)
insert into t1 SELECT id, name FROM (VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd'), (5, 'e')) x(id, name);
create table t2 ( id int not null primary key, code int)
insert into t2 SELECT id, code FROM (VALUES (1, 11), (2, 22), (3, 33), (4, 44)) x(id, code);
create table t3 ( id int not null primary key, field real)
insert into t3 SELECT id, field FROM (VALUES (2, 2.1), (3, 3.3), (4, 42.), (5, 5.4)) x(id, field); 

set statistics xml on;

select name from dbo.t1
       left join dbo.t2 on t1.id = t2.id
       left join dbo.t3 on t1.id = t3.id

set statistics xml off;

drop table t1, t2, t3;


К сообщению приложен файл. Размер - 8Kb
5 май 15, 01:36    [17599801]     Ответить | Цитировать Сообщить модератору
 Re: Razacharovan  [new]
Lepsik
Member

Откуда: glubinka
Сообщений: 4256
да с PK это так, а если индексы то не так.
5 май 15, 04:00    [17599846]     Ответить | Цитировать Сообщить модератору
 Re: Razacharovan  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 5975
Lepsik
да с PK это так, а если индексы то не так.

use tempdb
go
create table t1 ( id int not null, name sysname)
insert into t1 SELECT id, name FROM (VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd'), (5, 'e')) x(id, name);
create unique nonclustered index idx#t1 on t1 (id asc) include (name);

create table t2 ( id int not null, code int)
insert into t2 SELECT id, code FROM (VALUES (1, 11), (2, 22), (3, 33), (4, 44)) x(id, code);
create unique nonclustered index idx#t2 on t2 (id asc) include (code);

create table t3 ( id int not null, field real)
insert into t3 SELECT id, field FROM (VALUES (2, 2.1), (3, 3.3), (4, 42.), (5, 5.4)) x(id, field); 
create unique nonclustered index idx#t3 on t3 (id asc) include (field);

set statistics xml on;

select name from dbo.t1
       left join dbo.t2 on t1.id = t2.id
       left join dbo.t3 on t1.id = t3.id

set statistics xml off;

drop table t1, t2, t3;


К сообщению приложен файл. Размер - 6Kb
5 май 15, 07:02    [17599906]     Ответить | Цитировать Сообщить модератору
 Re: Razacharovan  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31444
Lepsik
да с PK это так, а если индексы то не так.
Вы показывайте, показывайте ваши варианты, когда "не так".

Думаю, сервер достаточно умён, что бы отсечь ненужные джойны - но только если это не влияет на результат.
5 май 15, 08:45    [17600122]     Ответить | Цитировать Сообщить модератору
 Re: Razacharovan  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
Lepsik
а если индексы то не так
Конечно не так, если индекс не уникальный.
Вы почему-то считаете, что в случае left join из правой таблицы всегда берется не более одной строки.
5 май 15, 10:08    [17600522]     Ответить | Цитировать Сообщить модератору
 Re: Razacharovan  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20607
Lepsik, а как, по-Вашему, без использования таблиц t2 и t3 сервер определит, сколько копий для каждой записи из t1 следует подать в выходной поток?
5 май 15, 10:14    [17600559]     Ответить | Цитировать Сообщить модератору
 Re: Razacharovan  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34621
Lepsik
чисто теоритический вопрос - почему при выполнении не откидываются заведомо неиспользуемые части запроса.


А Оракл, цука, хитрый, он откидывает...

я согласен, что это далеко не самая нужная вещь оптимизатору, но все же...
5 май 15, 11:14    [17600989]     Ответить | Цитировать Сообщить модератору
 Re: Razacharovan  [new]
Glory
Member

Откуда:
Сообщений: 104760
MasterZiv
А Оракл, цука, хитрый, он откидывает...

За бесплатно откидывает ? Не читая ничего из таблицы ?
А то я тоже могу сделать запрос для выяснения, сколько там в правой таблице значений, уникальны ли они, а потом нарисовать красивый конечный план.
5 май 15, 11:18    [17601009]     Ответить | Цитировать Сообщить модератору
 Re: Razacharovan  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31444
MasterZiv
А Оракл, цука, хитрый, он откидывает...
Да, и выдаёт неправильный результат. :-)

Или правильный, но откидывает при тех же условиях, как и сиквел? Или он специальную статистику держит, для поддержки которой требуются те же ресурсы, что и для поддержки уникального инедкса?

Варианты, сами понимаете, только эти, чудес не бывает.
5 май 15, 11:22    [17601032]     Ответить | Цитировать Сообщить модератору
 Re: Razacharovan  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
Сиквел не задействует при выполнении запроса таблицы, которые не влияют на результат, насколько я наблюдал.
Так что автор несколько лукавит.
5 май 15, 13:06    [17601694]     Ответить | Цитировать Сообщить модератору
 Re: Razacharovan  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
А можно убрать unique и записать вот так

use tempdb
go
create table t1 ( id int not null, name sysname)
insert into t1 SELECT id, name FROM (VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd'), (5, 'e')) x(id, name);
create nonclustered index idx#t1 on t1 (id asc) include (name);

create table t2 ( id int not null, code int)
insert into t2 SELECT id, code FROM (VALUES (1, 11), (2, 22), (3, 33), (4, 44)) x(id, code);
create nonclustered index idx#t2 on t2 (id asc) include (code);

create table t3 ( id int not null, field real)
insert into t3 SELECT id, field FROM (VALUES (2, 2.1), (3, 3.3), (4, 42.), (5, 5.4)) x(id, field); 
create nonclustered index idx#t3 on t3 (id asc) include (field);

set statistics xml on;

select name from dbo.t1
OUTER APPLY
(
	SELECT TOP 1 * FROM dbo.t2 WHERE t1.id = t2.id
) t2
OUTER APPLY
(
	SELECT TOP 1 * FROM dbo.t3 WHERE t1.id = t3.id
) t3

set statistics xml off;

drop table t1, t2, t3;


И тоже он будет только по первой таблице делать скан
5 май 15, 15:11    [17602649]     Ответить | Цитировать Сообщить модератору
 Re: Razacharovan  [new]
Lepsik
Member

Откуда: glubinka
Сообщений: 4256
t2 не нужен!!!

create table t1 ( id int not null identity primary key, name sysname, user_id int not null index idx_user1) 
insert into t1 (name, user_id) SELECT name, user_id FROM (VALUES ('a', 11), ('b', 12), ('c', 13), ('d', 14), ('e', 15)) x(name, user_id);
create table t2 ( id int not null identity primary key, code int, user_id int not null index idx_user2) 
insert into t2 (code, user_id) SELECT code, user_id FROM (VALUES (11, 21), (2, 32), (3, 43), (4, 64)) x(code, user_id);
create table t3 ( id int not null identity primary key, field real, user_id int not null index idx_user3) 
insert into t3 (field, user_id) SELECT field, user_id FROM (VALUES (2.1, 13), (3.3, 14), (42.2, 11), (5.4, 15)) x(field, user_id);
GO

CREATE FUNCTION dbo.zapros()
RETURNS TABLE 
AS
  RETURN
    WITH cte ( user_id, field ) AS 
    (
       SELECT user_id, SUM(field) FROM dbo.t3 group by user_id
    )
     select name, field FROM cte 
       left join dbo.t1 on t1.user_id = cte.user_id
       left join dbo.t2 on cte.user_id = t2.user_id
GO

set statistics xml on;
 select name from dbo.zapros()
set statistics xml off;

drop FUNCTION dbo.zapros
drop table t1
drop table t2
drop table t3
GO


К сообщению приложен файл. Размер - 19Kb
5 май 15, 16:19    [17603157]     Ответить | Цитировать Сообщить модератору
 Re: Razacharovan  [new]
Lepsik
Member

Откуда: glubinka
Сообщений: 4256
Akina
Lepsik, а как, по-Вашему, без использования таблиц t2 и t3 сервер определит, сколько копий для каждой записи из t1 следует подать в выходной поток?


LEFT JOIN? какие копии?
5 май 15, 16:46    [17603354]     Ответить | Цитировать Сообщить модератору
 Re: Razacharovan  [new]
Lepsik
Member

Откуда: glubinka
Сообщений: 4256
[quot Сон Веры Павловны]
Lepsik
да с PK это так, а если индексы то не так.


Спасибо помогло
5 май 15, 16:51    [17603378]     Ответить | Цитировать Сообщить модератору
 Re: Razacharovan  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Lepsik,

Для того, чтобы оптимизатор мог безопасно исключить лишние соединения должно выполняться три логических условия:
- никаких проекций из соединяемой таблицы (у вас выполняется, т.к. в select только поля t1.name, t3.field их t2 ничего нет)
- никакой фильтрации строк (у вас выполняется, т.к. left join)
- никакого дублирования строк (у вас не выполняется, т.к. индекс index idx_user2 по user_id не уникальный)
Еще есть ограничения чистой реализации, в некоторых сложных выражениях оптимизатор может не исключить соединение, даже если все условия выполняются.
Но у вас не происходит исключения, т.к. не выполняется третий пункт.
Замените в своем примере вот так:
+
create table t1 ( id int not null identity primary key, name sysname, user_id int not null index idx_user1) 
insert into t1 (name, user_id) SELECT name, user_id FROM (VALUES ('a', 11), ('b', 12), ('c', 13), ('d', 14), ('e', 15)) x(name, user_id);
create table t2 ( id int not null identity primary key, code int, user_id int not null, constraint idx_user2 unique(user_id) ) 
insert into t2 (code, user_id) SELECT code, user_id FROM (VALUES (11, 21), (2, 32), (3, 43), (4, 64)) x(code, user_id);
create table t3 ( id int not null identity primary key, field real, user_id int not null index idx_user3) 
insert into t3 (field, user_id) SELECT field, user_id FROM (VALUES (2.1, 13), (3.3, 14), (42.2, 11), (5.4, 15)) x(field, user_id);
GO

CREATE FUNCTION dbo.zapros()
RETURNS TABLE 
AS
  RETURN
    WITH cte ( user_id, field ) AS 
    (
       SELECT user_id, SUM(field) FROM dbo.t3 group by user_id
    )
     select name, field FROM cte 
       left join dbo.t1 on t1.user_id = cte.user_id
       left join dbo.t2 on cte.user_id = t2.user_id
GO

set statistics xml on;
 select name from dbo.zapros()
set statistics xml off;

drop FUNCTION dbo.zapros
drop table t1
drop table t2
drop table t3
GO

Увидите, что оптимизатор исключил t2.
5 май 15, 16:57    [17603417]     Ответить | Цитировать Сообщить модератору
 Re: Razacharovan  [new]
Lepsik
Member

Откуда: glubinka
Сообщений: 4256
SomewhereSomehow
Lepsik,

- никакого дублирования строк (у вас не выполняется, т.к. индекс index idx_user2 по user_id не уникальный)
Но у вас не происходит исключения, т.к. не выполняется третий пункт.
Увидите, что оптимизатор исключил t2.


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

Вполне очевидно что если сиквел строит дерево запроса то для left join, если поля из таблицы не используются, он должен отсекать ненужные ветки. Каким образом index или даже unique index должен влиять на это? Ето детская задачка, должна легко решатся.

У меня туча репортов в полсотней полей каждый, в каждом запросе используется десяток подзапросов с агрегациями приклеиваются к результату через left join. Если пользователю в репорте хочется 2-3 поля мне приходится выполнять все вычисления, что долго по времени.
5 май 15, 17:31    [17603613]     Ответить | Цитировать Сообщить модератору
 Re: Razacharovan  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
Ваше утверждение неверно.

Lepsik
если поля из таблицы не используются,


То что их нет в SELECT не означает, что они не используются.

Возможно вам надо понять, что такое Left join и почему


select name from dbo.t1
       left join dbo.t2 on t1.id = t2.id
       left join dbo.t3 on t1.id = t3.id


и

select name from dbo.t1


это разные запросы, которые могут вернуть разное кол-во записей, если только нет уникальных индексов
5 май 15, 17:37    [17603648]     Ответить | Цитировать Сообщить модератору
 Re: Razacharovan  [new]
Lepsik
Member

Откуда: glubinka
Сообщений: 4256
неужели поменялись правила? Я всегда считал результатом t1 left join t2 будут все записи из t1, неважно если ли индекс или нет, если записи в t2 или нет.

К сообщению приложен файл. Размер - 28Kb
5 май 15, 17:50    [17603707]     Ответить | Цитировать Сообщить модератору
 Re: Razacharovan  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
Lepsik
Я всегда считал результатом t1 left join t2 будут все записи из t1, неважно если ли индекс или нет, если записи в t2 или нет.
declare @t1 table (id int, name varchar(10));
declare @t2 table (id int);

insert into @t1 values (1, 'a');
insert into @t2 values (1), (1);

select
 t1.name
from
 @t1 t1 left join
 @t2 t2 on t2.id = t1.id;
5 май 15, 17:57    [17603735]     Ответить | Цитировать Сообщить модератору
 Re: Razacharovan  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
Lepsik
неужели поменялись правила? Я всегда считал результатом t1 left join t2 будут все записи из t1, неважно если ли индекс или нет, если записи в t2 или нет.


ку?
create table #t1 (c int);
insert into #t1 values (1);
create table #t2 (c int);
insert into #t2 values (1), (1);
go
select t1.*
from #t1 t1
  -- ненужный left join
  --left join #t2 t2 on
  --  t1.c = t2.c

select t1.*
from #t1 t1
  left join #t2 t2 on
    t1.c = t2.c
go
drop table #t1
go
drop table #t2;
5 май 15, 18:00    [17603745]     Ответить | Цитировать Сообщить модератору
 Re: Razacharovan  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Lepsik
Вполне очевидно что если сиквел строит дерево запроса то для left join, если поля из таблицы не используются, он должен отсекать ненужные ветки. Каким образом index или даже unique index должен влиять на это? Ето детская задачка, должна легко решатся.

У меня туча репортов в полсотней полей каждый, в каждом запросе используется десяток подзапросов с агрегациями приклеиваются к результату через left join. Если пользователю в репорте хочется 2-3 поля мне приходится выполнять все вычисления, что долго по времени.


Влияет не наличие/отсутствие индекса как такового, а гарантия факта: никакого дублирования строк.
Проведите мысленный эксперимент, пусть есть две таблицы: t1 = a {1,2,3}, t2 = a {1,1,2,3}
Соединяете их t1 left join t3 on t1.a = t2.a.
1) Если мы отбрасываем t2, то результат res = {1,2,3}, т.е. таблица t1.
2) Если мы не отбрасываем, то результат res = { (1,1), (1,1), (2,2), (3,3)} = далее берем значения только из t1 = {1,1,2,3}.
Очевидно разные результаты. В результате соединения, строки из t1 дублируются, т.е. нельзя отбрасывать вторую таблицу, т.к. она влияет на конечный результат. Вот если бы можно было гарантировать уникальность, например так:
3) t1 left join (select distinct a from t3) t3(a) on t1.a = t2.a - в таком случае, можно отбросить.
И вы проверьте, в вашем же примере:
+
create table t1 ( id int not null identity primary key, name sysname, user_id int not null index idx_user1) 
insert into t1 (name, user_id) SELECT name, user_id FROM (VALUES ('a', 11), ('b', 12), ('c', 13), ('d', 14), ('e', 15)) x(name, user_id);
create table t2 ( id int not null identity primary key, code int, user_id int not null index idx_user2) 
insert into t2 (code, user_id) SELECT code, user_id FROM (VALUES (11, 21), (2, 32), (3, 43), (4, 64)) x(code, user_id);
create table t3 ( id int not null identity primary key, field real, user_id int not null index idx_user3) 
insert into t3 (field, user_id) SELECT field, user_id FROM (VALUES (2.1, 13), (3.3, 14), (42.2, 11), (5.4, 15)) x(field, user_id);
GO

CREATE FUNCTION dbo.zapros()
RETURNS TABLE 
AS
  RETURN
    WITH cte ( user_id, field ) AS 
    (
       SELECT user_id, SUM(field) FROM dbo.t3 group by user_id
    )
     select distinct name, field FROM cte 
       left join dbo.t1 on t1.user_id = cte.user_id
       left join dbo.t2 on cte.user_id = t2.user_id
GO

set statistics xml on;
 select name from dbo.zapros()
set statistics xml off;

drop FUNCTION dbo.zapros
drop table t1
drop table t2
drop table t3
GO

Никакой t2 в плане нет, даже несмотря на то, что distinct не под соединением. Т.е. оптимизатор догадался поменять их местами, понять что значения уникальны, возможно преобразовать тип соединения в semi и отбросить ненужное.

Если это упрощенный вариант, то ищите в вашем реальном примере, почему он не может этого сделать, это может быть нарушение одного из перечисленных трех пунктов (в больших запросах понять кто на ком стоял часто трудно, так что потребуется подумать, есть где-то нарушение семантики или нет если исключать таблицу), либо какие-то сложные выражения над полями и текущая реализация оптимизатора не способна чисто технически распознать паттерн правила упрощения (тут проще, ничего анализировать не надо, просто говорим "оптимизатор дурак, я разочарован" =))
5 май 15, 18:06    [17603765]     Ответить | Цитировать Сообщить модератору
 Re: Razacharovan  [new]
кухарка архитектор
Guest
Lepsik
неужели поменялись правила? Я всегда считал результатом t1 left join t2 будут все записи из t1, неважно если ли индекс или нет, если записи в t2 или нет.

left join ни разу не писали, азбуки и цифр не знаем, зато готовы в щепки разнести "кривую внутреннюю кухню" какой-то там промышленной (как бы) субд со всякими "глупостями в тупом оптимизаторе".
5 май 15, 18:23    [17603815]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить