Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Почему сортировка в плане запроса?  [new]
MSSQLBug
Guest
Почему вообще используется сортировка в этом запросе, зачем она нужна?

Ссылка на sqlfiddle: http://sqlfiddle.com/#!6/87c7d/1

Тестовые данные:

CREATE TABLE TEST_InvLocs(
inventlocationid varchar(10) NOT NULL PRIMARY KEY CLUSTERED,
shopgroupid varchar(10) NOT NULL)

CREATE TABLE TEST_Sales(
itemid varchar(20) NOT NULL, 
inventlocationid [varchar](10) NOT NULL,
rdate smalldatetime NOT NULL,
salea [numeric](38, 4) NOT NULL
)

ALTER TABLE TEST_Sales WITH CHECK ADD CONSTRAINT FK_InvLocId FOREIGN KEY(inventlocationid)
REFERENCES TEST_InvLocs (inventlocationid)
ON UPDATE CASCADE
ON DELETE CASCADE

CREATE UNIQUE CLUSTERED INDEX idxmain ON TEST_Sales(rdate, itemid, inventlocationid)

;with cte as ( 
  select 1 i
  union all
  select i+1 from cte where i < 10
)

INSERT INTO TEST_InvLocs(inventlocationid, shopgroupid)
SELECT 'invloc' + CAST(i AS VARCHAR), 'group' + CAST(i % 4 AS VARCHAR)
FROM cte

;with cte as ( 
  select 1 i
  union all
  select i+1 from cte where i < 10
)

INSERT INTO TEST_Sales(rdate, itemid, inventlocationid, salea)
select CAST('20120101' AS smalldatetime) + d.i as rdate, 
       'Item' + CAST(i.i AS VARCHAR),
       l.inventlocationid,
       d.i
  from cte as i, TEST_InvLocs as l, cte as d
  where i.i <= 5


И запрос:

select s.rdate, s.itemid, l.SHOPGROUPID, s.salea
 from dbo.TEST_Sales as s
inner join TEST_InvLocs as l 
   on s.inventlocationid = l.INVENTLOCATIONID
ORDER BY s.rdate, s.itemid
3 июл 14, 12:39    [16254188]     Ответить | Цитировать Сообщить модератору
 Re: Почему сортировка в плане запроса?  [new]
invm
Member

Откуда: Москва
Сообщений: 9719
MSSQLBug
Почему вообще используется сортировка в этом запросе, зачем она нужна?
Потому что в запросе указано order by и запрошенный порядок можно получить только сортировкой. Например, замените запрос на:
select s.rdate, s.itemid, l.SHOPGROUPID, s.salea
 from dbo.TEST_Sales as s
inner loop join TEST_InvLocs as l 
   on s.inventlocationid = l.INVENTLOCATIONID
ORDER BY s.rdate, s.itemid
option (force order)
И сортировки не будет.
3 июл 14, 13:08    [16254379]     Ответить | Цитировать Сообщить модератору
 Re: Почему сортировка в плане запроса?  [new]
MSSQLBug
Guest
invm,

> Потому что в запросе указано order by и запрошенный порядок можно получить только сортировкой.
Получается, что HASH JOIN в Microsoft SQL Server никогда не сохраняет порядок записей?
А почему, ведь для небольших хешируемых таблиц алгоритм кажется тривиальным?
3 июл 14, 13:45    [16254667]     Ответить | Цитировать Сообщить модератору
 Re: Почему сортировка в плане запроса?  [new]
invm
Member

Откуда: Москва
Сообщений: 9719
MSSQLBug,

Дело не в HASH JOIN, а в том, что clustered index scan по idxmain - unordered.
3 июл 14, 13:54    [16254745]     Ответить | Цитировать Сообщить модератору
 Re: Почему сортировка в плане запроса?  [new]
MSSQLBug
Guest
invm,

> Дело не в HASH JOIN, а в том, что clustered index scan по idxmain - unordered.
Так, моё понимание всё уменьшается. ;)

А почему тогда используется unordered clustered index scan?
Почему не использовать ORDERED и затем HASH JOIN с сохранением порядка?
3 июл 14, 14:05    [16254862]     Ответить | Цитировать Сообщить модератору
 Re: Почему сортировка в плане запроса?  [new]
invm
Member

Откуда: Москва
Сообщений: 9719
MSSQLBug
А почему тогда используется unordered clustered index scan?
Потому что оптимизатор решил что это дешевле.
3 июл 14, 14:07    [16254892]     Ответить | Цитировать Сообщить модератору
 Re: Почему сортировка в плане запроса?  [new]
Shakill
Member

Откуда: мск
Сообщений: 1882
MSSQLBug,
кстати, а запрос

select s.*
from dbo.TEST_Sales as s
ORDER BY s.rdate, s.itemid


на вашем сервере без сортировки в плане?
3 июл 14, 14:08    [16254904]     Ответить | Цитировать Сообщить модератору
 Re: Почему сортировка в плане запроса?  [new]
MSSQLBug
Guest
Shakill,

> на вашем сервере без сортировки в плане?

Да, естественно.

> Потому что оптимизатор решил что это дешевле.
Так это же явно неправильное решение в такой ситуации.
Если у меня сравнить стоимости планов с JOIN-ом c TEST_InvLocs и без него, получится:
C JOIN: 2447.83
Без JOIN: 192.82

Добавление в план построения хеш-таблицы на основе TEST_InvLocs (где сервер предполагает 409 строк)
и probes в ней не должны бы существенно увеличить стоимость.

По-моему, дело просто в том, что order-preserving hash join в MS SQL просто нет.
3 июл 14, 14:30    [16255085]     Ответить | Цитировать Сообщить модератору
 Re: Почему сортировка в плане запроса?  [new]
invm
Member

Откуда: Москва
Сообщений: 9719
MSSQLBug,

http://sqlblog.com/blogs/paul_white/archive/2010/09/23/a-tale-of-two-index-hints.aspx
3 июл 14, 14:31    [16255099]     Ответить | Цитировать Сообщить модератору
 Re: Почему сортировка в плане запроса?  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
MSSQLBug
Почему не использовать ORDERED и затем HASH JOIN с сохранением порядка?

вы сами себе ответили
MSSQLBug
Получается, что HASH JOIN в Microsoft SQL Server никогда не сохраняет порядок записей?

Хэш джойн не сохраняет порядок. Вот что у Крейга Фридмана написано:
Nested Loops JoinMerge JoinHash Join
Best for …Relatively small inputs with an index on the inner table on the join key.Medium to large inputs with indexes to provide order on the equijoin keys and/or where we require order after the join.DW queries with medium to large inputs. Parallel execution that scales linearly.
ConcurrencySupports large numbers of concurrent users.Many-to-one join with indexes to provide order supports large numbers of concurrent users.Best for small numbers of concurrent users with high throughput requirements.
Stop and goNoNoYes (build input only)
Equijoin requiredNoYes (except for full outer join)Yes
Outer and semi-joinsLeft joins only (full outer joins via transformation)All join typesAll join types
Uses memoryNoNo (may require sorts which use memory)Yes
Uses tempdbNoYes (many-to-many join only)Yes (if join runs out of memory and spills)
Requires orderNoYesNo
Preserves orderYes (outer input only)YesNo

Касательно того, почему такой тип соединения уже ответил invm, потому что дешевле. Проверьте сами, выполнив в одном окне и посмотрев на стоимость одного относительно другого (тот случай, когда проценты можно сравнивать).
select s.rdate, s.itemid, l.SHOPGROUPID, s.salea
 from dbo.TEST_Sales as s
inner join TEST_InvLocs as l 
   on s.inventlocationid = l.INVENTLOCATIONID
ORDER BY s.rdate, s.itemid

select s.rdate, s.itemid, l.SHOPGROUPID, s.salea
 from dbo.TEST_Sales as s
inner join TEST_InvLocs as l 
   on s.inventlocationid = l.INVENTLOCATIONID
ORDER BY s.rdate, s.itemid
option(loop join)
3 июл 14, 14:36    [16255126]     Ответить | Цитировать Сообщить модератору
 Re: Почему сортировка в плане запроса?  [new]
MSSQLBug
Guest
SomewhereSomehow,

Спасибо за подробный ответ. А что насчёт моего другого вопроса:

> А почему, ведь для небольших хешируемых таблиц алгоритм кажется тривиальным?

И, кстати, есть вот такая ссылка: http://citeseerx.ist.psu.edu/viewdoc/summary?doi=10.1.1.35.5835

1998 год, между прочим. Почему бы это не реализовать в сервере?
3 июл 14, 14:41    [16255181]     Ответить | Цитировать Сообщить модератору
 Re: Почему сортировка в плане запроса?  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
MSSQLBug
Так это же явно неправильное решение в такой ситуации.
Если у меня сравнить стоимости планов с JOIN-ом c TEST_InvLocs и без него, получится:
C JOIN: 2447.83
Без JOIN: 192.82

Не совсем понятно, как это "без join"?
Если сравнивать по типу соединения - то с Nested Loops дороже, что и объясняет выбор оптимизатора. Если вы считаете что он ошибается, то оформляйте запрос на microsoft connect со своим примером, и либо учтут, либо объяснят где не правы =)
3 июл 14, 14:42    [16255186]     Ответить | Цитировать Сообщить модератору
 Re: Почему сортировка в плане запроса?  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
MSSQLBug
1998 год, между прочим. Почему бы это не реализовать в сервере?


Ох, не сыпьте соль на сахар, задайте этот вопрос продуктовой группе =) Кое-кто, вообще считает что сиквел закончился на 2005
версии, я не столь радикален, но видимо хекатон и прочее важнее, чем доработка существующего. В защиту МС скажу, что старые вещи тоже допиливают, взять тот же Cardinality Estimator. Правда, есть авторитетное мнение (не мое, человека из МС), что в некоторых случаях он хуже и наблюдаются регрессы. Ну на то есть трейсфлаги для отката к старому поведению.
3 июл 14, 14:49    [16255241]     Ответить | Цитировать Сообщить модератору
 Re: Почему сортировка в плане запроса?  [new]
MSSQLBug
Guest
SomewhereSomehow,

> Не совсем понятно, как это "без join"?

Да просто упорядоченная выборка из этой таблицы:
select s.*
from dbo.TEST_Sales as s
ORDER BY s.rdate, s.itemid


> Ох, не сыпьте соль на сахар, задайте этот вопрос продуктовой группе =)
Жаль, выигрыш мог бы быть на порядок в аналогичных случаях.
3 июл 14, 14:59    [16255290]     Ответить | Цитировать Сообщить модератору
 Re: Почему сортировка в плане запроса?  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
MSSQLBug
Да просто упорядоченная выборка из этой таблицы:

Не сравнивайте разные запросы по критерию стоимости, просто не имеет смысла.

MSSQLBug
Жаль, выигрыш мог бы быть на порядок в аналогичных случаях.

Ну так вперед, оформляйте на SQL Server | Microsoft Connect, чего попросту жалеть.
3 июл 14, 15:06    [16255348]     Ответить | Цитировать Сообщить модератору
 Re: Почему сортировка в плане запроса?  [new]
MSSQLBug
Guest
SomewhereSomehow,

> Не сравнивайте разные запросы по критерию стоимости, просто не имеет смысла.

Я просто говорил о том, что будет, если добавить в Microsoft SQL Server такую возможность.

> Ну так вперед, оформляйте на SQL Server | Microsoft Connect, чего попросту жалеть.
Как-то странно, что это больше никому не нужно (аналогичного запроса там вроде нет).
3 июл 14, 15:14    [16255408]     Ответить | Цитировать Сообщить модератору
 Re: Почему сортировка в плане запроса?  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
MSSQLBug
Как-то странно, что это больше никому не нужно (аналогичного запроса там вроде нет).

Ну это не повод отказываться от своей мысли. Вы создайте, а народ проголосует если нужно. Кстати, киньте сюда ссылку потом, чтоб активней голосовали. А в МС киньте ссылкой на документ. Я правда не читал его, не знаю насколько он релевантен.

Вообще, хэш джоин интересная тема, у меня есть в планах про него подробно написать, даже статья в черновиках лежит. После этой темы, подробнее изучу вопрос про сохранение порядка и документ этот почитаю. Интересно то, что некоторые нюансы меняются, но это нигде не афишируются, я вот описывал случай: Забавный случай упрощения соединений - поди узнай, что там внутри в 2012 сервере поведение при упрощении соединений поменялось, хотя в книге архитектора оптимизатора описано другое поведение. ИМХО, остро не хватает актуального материала и литературы по теме оптимизации. Можно, конечно, копать самому, что я и делаю, но очень много времени на это уходит. По этому, приводя ссылку на тот же блог Крейга, неплохо бы убедиться, что информация актуальна и по сей день. Видимо, в отношении Хэш Джойна, информация актуальна, но без самостоятельного изучения сказать вот так прямо, что он не сохраняет порядок во всех случаях тоже нельзя.
3 июл 14, 15:35    [16255607]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить