powered by simpleCommunicator - 2.0.49     © 2025 Programmizd 02
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Почему сортировка в плане запроса?
17 сообщений из 17, страница 1 из 1
Почему сортировка в плане запроса?
    #38686720
MSSQLBug
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Почему вообще используется сортировка в этом запросе, зачем она нужна?

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

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

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
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



И запрос:

Код: sql
1.
2.
3.
4.
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
...
Рейтинг: 0 / 0
Почему сортировка в плане запроса?
    #38686756
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
MSSQLBugПочему вообще используется сортировка в этом запросе, зачем она нужна?Потому что в запросе указано order by и запрошенный порядок можно получить только сортировкой. Например, замените запрос на:
Код: sql
1.
2.
3.
4.
5.
6.
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)

И сортировки не будет.
...
Рейтинг: 0 / 0
Почему сортировка в плане запроса?
    #38686799
MSSQLBug
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
invm,

> Потому что в запросе указано order by и запрошенный порядок можно получить только сортировкой.
Получается, что HASH JOIN в Microsoft SQL Server никогда не сохраняет порядок записей?
А почему, ведь для небольших хешируемых таблиц алгоритм кажется тривиальным?
...
Рейтинг: 0 / 0
Почему сортировка в плане запроса?
    #38686808
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
MSSQLBug,

Дело не в HASH JOIN, а в том, что clustered index scan по idxmain - unordered.
...
Рейтинг: 0 / 0
Почему сортировка в плане запроса?
    #38686815
MSSQLBug
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
invm,

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

А почему тогда используется unordered clustered index scan?
Почему не использовать ORDERED и затем HASH JOIN с сохранением порядка?
...
Рейтинг: 0 / 0
Почему сортировка в плане запроса?
    #38686822
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
MSSQLBugА почему тогда используется unordered clustered index scan?Потому что оптимизатор решил что это дешевле.
...
Рейтинг: 0 / 0
Почему сортировка в плане запроса?
    #38686825
Фотография Shakill
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
MSSQLBug,
кстати, а запрос

Код: sql
1.
2.
3.
select s.*
from dbo.TEST_Sales as s
ORDER BY s.rdate, s.itemid



на вашем сервере без сортировки в плане?
...
Рейтинг: 0 / 0
Почему сортировка в плане запроса?
    #38686851
MSSQLBug
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Shakill,

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

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

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

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

По-моему, дело просто в том, что order-preserving hash join в MS SQL просто нет.
...
Рейтинг: 0 / 0
Почему сортировка в плане запроса?
    #38686853
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
...
Рейтинг: 0 / 0
Почему сортировка в плане запроса?
    #38686859
Фотография SomewhereSomehow
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
MSSQLBugПочему не использовать ORDERED и затем HASH JOIN с сохранением порядка?
вы сами себе ответили
MSSQLBugПолучается, что HASH JOIN в Microsoft SQL Server никогда не сохраняет порядок записей?
Хэш джойн не сохраняет порядок. Вот что у Крейга Фридмана написано :
Nested Loops JoinMerge JoinHash JoinBest 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)YesOuter and semi-joinsLeft joins only (full outer joins via transformation)All join typesAll join typesUses memoryNoNo (may require sorts which use memory)YesUses tempdbNoYes (many-to-many join only)Yes (if join runs out of memory and spills)Requires orderNoYesNoPreserves orderYes (outer input only)Yes No
Касательно того, почему такой тип соединения уже ответил invm, потому что дешевле. Проверьте сами, выполнив в одном окне и посмотрев на стоимость одного относительно другого (тот случай, когда проценты можно сравнивать).
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
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)
...
Рейтинг: 0 / 0
Почему сортировка в плане запроса?
    #38686863
MSSQLBug
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
SomewhereSomehow,

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

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

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

1998 год, между прочим. Почему бы это не реализовать в сервере?
...
Рейтинг: 0 / 0
Почему сортировка в плане запроса?
    #38686864
Фотография SomewhereSomehow
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
MSSQLBugТак это же явно неправильное решение в такой ситуации.
Если у меня сравнить стоимости планов с JOIN-ом c TEST_InvLocs и без него, получится:
C JOIN: 2447.83
Без JOIN: 192.82

Не совсем понятно, как это "без join"?
Если сравнивать по типу соединения - то с Nested Loops дороже, что и объясняет выбор оптимизатора. Если вы считаете что он ошибается, то оформляйте запрос на microsoft connect со своим примером, и либо учтут, либо объяснят где не правы =)
...
Рейтинг: 0 / 0
Почему сортировка в плане запроса?
    #38686876
Фотография SomewhereSomehow
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
MSSQLBug1998 год, между прочим. Почему бы это не реализовать в сервере?

Ох, не сыпьте соль на сахар, задайте этот вопрос продуктовой группе =) Кое-кто, вообще считает что сиквел закончился на 2005
версии, я не столь радикален, но видимо хекатон и прочее важнее, чем доработка существующего. В защиту МС скажу, что старые вещи тоже допиливают, взять тот же Cardinality Estimator. Правда, есть авторитетное мнение (не мое, человека из МС), что в некоторых случаях он хуже и наблюдаются регрессы. Ну на то есть трейсфлаги для отката к старому поведению.
...
Рейтинг: 0 / 0
Почему сортировка в плане запроса?
    #38686889
MSSQLBug
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
SomewhereSomehow,

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

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



> Ох, не сыпьте соль на сахар, задайте этот вопрос продуктовой группе =)
Жаль, выигрыш мог бы быть на порядок в аналогичных случаях.
...
Рейтинг: 0 / 0
Почему сортировка в плане запроса?
    #38686899
Фотография SomewhereSomehow
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
MSSQLBugДа просто упорядоченная выборка из этой таблицы:

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

MSSQLBugЖаль, выигрыш мог бы быть на порядок в аналогичных случаях.
Ну так вперед, оформляйте на SQL Server | Microsoft Connect , чего попросту жалеть.
...
Рейтинг: 0 / 0
Почему сортировка в плане запроса?
    #38686915
MSSQLBug
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
SomewhereSomehow,

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

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

> Ну так вперед, оформляйте на SQL Server | Microsoft Connect, чего попросту жалеть.
Как-то странно, что это больше никому не нужно (аналогичного запроса там вроде нет).
...
Рейтинг: 0 / 0
Почему сортировка в плане запроса?
    #38686956
Фотография SomewhereSomehow
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
MSSQLBugКак-то странно, что это больше никому не нужно (аналогичного запроса там вроде нет).
Ну это не повод отказываться от своей мысли. Вы создайте, а народ проголосует если нужно. Кстати, киньте сюда ссылку потом, чтоб активней голосовали. А в МС киньте ссылкой на документ. Я правда не читал его, не знаю насколько он релевантен.

Вообще, хэш джоин интересная тема, у меня есть в планах про него подробно написать, даже статья в черновиках лежит. После этой темы, подробнее изучу вопрос про сохранение порядка и документ этот почитаю. Интересно то, что некоторые нюансы меняются, но это нигде не афишируются, я вот описывал случай: Забавный случай упрощения соединений - поди узнай, что там внутри в 2012 сервере поведение при упрощении соединений поменялось, хотя в книге архитектора оптимизатора описано другое поведение. ИМХО, остро не хватает актуального материала и литературы по теме оптимизации. Можно, конечно, копать самому, что я и делаю, но очень много времени на это уходит. По этому, приводя ссылку на тот же блог Крейга, неплохо бы убедиться, что информация актуальна и по сей день. Видимо, в отношении Хэш Джойна, информация актуальна, но без самостоятельного изучения сказать вот так прямо, что он не сохраняет порядок во всех случаях тоже нельзя.
...
Рейтинг: 0 / 0
17 сообщений из 17, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Почему сортировка в плане запроса?
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]