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

Откуда: Кишинёв
Сообщений: 6723
Mind, И?
У TC очень малые таблы, и скорее мало изменяемые. Эти "аргументы" превращаются в пыль. Даже если вдруг, то всё равно - перестройка кучи сравнима перестройке индекса, а на таких размерах вообще фиолетово.
Это не есть основной аргумент для данной задачи.

Как уже писали - пусть пробует на собственной шкуре, чем устраивает бесполезные бла-бла-бла на форумах. И вот после "игр" пусть и пишет - Стоило ли вообще заморачиваться с кучами.

Crimean
куча
стабильность планов, опять же, не последний фактор
Либо что-то недоговривает либо тонко издевается. Или не к тому был ответ.
7 июн 12, 10:08    [12679611]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный(изованный) индекс. Маленькие таблицы. Необходимость?  [new]
dmitry stakanov
Member

Откуда:
Сообщений: 241
Mind
dmitry stakanov
Шамиль Фаридович,
если есть pk значит есть какой-то индекс, который поддерживает уникальность pk. если есть индекс, то логичнее делать clst, тк он быстрее, чем ix.
Не совсем верно. 
Кластерный медленнее чем ix, при условии что вам нужны поля только из ix индекса, иначе сравнение не имеет смысла. ix меньше, так как содержит меньше полей, следовательно его сканирование быстрее.

это безусловно верно. имелось в виду скорость равноценных индексов, те на обоих одинаковых ключ а на ix включаемые поля.

Mind
Если идти еще дальше, то связка ix+куча быстрее чем ix+кластерный при поиске по ix.

dmitry stakanov
если таблица участвует только в запросах типа select * from tbl1 и не требуется поддержка уникальности, смысла строить ни индексы ни pk нет, тк это будет лишней нагрузкой на сервер.
Если таблица из 100 записей, то какой запрос вы не напишите все равно будет скан таблицы, не важно какие там индексы.


тут с Вами не соглашусь. будет скан pk если есть pk, если нет pk, но есть ix будет скан кучи.
7 июн 12, 11:22    [12680353]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный(изованный) индекс. Маленькие таблицы. Необходимость?  [new]
dmitry stakanov
Member

Откуда:
Сообщений: 241
--------------------------------
Шамиль Фаридович,

непонятен вопрос

если - нужен ли в таблице первичный ключ, то ответ однозначный - нужен.

если - нужно ли первичный ключ делать кластерным, то ответ - для маленьких таблиц большой разницы нет, но лучше сделать.


Я попробую, резюмировать обсуждение:

Вопрос: надо ли кластеризовать маленькие таблицы?

От ответ – да, пояснения почему выше и я сними абсолютно согласен.

Если даже мы отбросим вопросы производительности запросов итд, то по всем канонам реляционной таблица является лишь в случае отношений, которые строятся на pk.
7 июн 12, 11:47    [12680631]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный(изованный) индекс. Маленькие таблицы. Необходимость?  [new]
ScareCrow
Member

Откуда: Белый город
Сообщений: 17329
автор
то по всем канонам реляционной таблица является лишь в случае отношений, которые строятся на pk.

рыдалЪ.
7 июн 12, 12:31    [12681109]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный(изованный) индекс. Маленькие таблицы. Необходимость?  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Аж на две страницы тема =)

Шамиль Фаридович,
я так понимаю, у вас вопрос скорее теоретический, ибо размер таблицы на столько мал, что обсуждать это всерьез нет никакого смысла. Что касается теории, вот хорошая статья SQL Server Best Practices Article от MS. Там все расписано, с тестами, временем, различными операциями и т.д.
7 июн 12, 13:10    [12681443]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный(изованный) индекс. Маленькие таблицы. Необходимость?  [new]
dmitry stakanov
Member

Откуда:
Сообщений: 241
ScareCrow,

конкретнее плз
7 июн 12, 13:22    [12681562]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный(изованный) индекс. Маленькие таблицы. Необходимость?  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 484
Mind
Если таблица из 100 записей, то какой запрос вы не напишите все равно будет скан таблицы, не важно какие там индексы.
А можно подробнее?
При запросе вида
select id, CatName, CatRang
from tbCategories
where id=5

в действительном плане выполнения показан Clustered Index Seek, а не Table Scan, или Executor действует по своему?
7 июн 12, 17:54    [12683859]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный(изованный) индекс. Маленькие таблицы. Необходимость?  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Шамиль Фаридович
Mind
Если таблица из 100 записей, то какой запрос вы не напишите все равно будет скан таблицы, не важно какие там индексы.
А можно подробнее?
При запросе вида
select id, CatName, CatRang
from tbCategories
where id=5

в действительном плане выполнения показан Clustered Index Seek, а не Table Scan, или Executor действует по своему?
Если у вас 100 записей, то вся таблица помещается на 1 странице, так что разницы никакой, будет ли там в плане seek, scan или чего еще. Все равно будут прочитаны все страницы в количестве одна штука.
7 июн 12, 20:01    [12684271]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный(изованный) индекс. Маленькие таблицы. Необходимость?  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Главное чтоб не делался Loop JOIN вместо Merge.
8 июн 12, 18:20    [12690150]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный(изованный) индекс. Маленькие таблицы. Необходимость?  [new]
SomewhereSomehow
Member

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

Позвольте с вами не согласиться, коллега, главное в одностраничной таблице - бороться с фрагментацией.9727
8 июн 12, 18:52    [12690303]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный(изованный) индекс. Маленькие таблицы. Необходимость?  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
SomewhereSomehow, Ok слово "главное" надо было вырезать. :)
Просто надо было упомянуть. Согласитесь что глупо сканить даже 20 строк каждый раз когда Merge намного быстрее.
8 июн 12, 19:31    [12690443]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный(изованный) индекс. Маленькие таблицы. Необходимость?  [new]
SomewhereSomehow
Member

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

Я не совсем понимаю. Если единицей считывания является страница и вся таблица там умещается то о какой стратегии доступа вообще может идти речь? Если мы соединяем две одинаковые таблицы, т.е. по 100 строк и по одной странице, то да слияние выгоднее. Но на сколько? Там все упирается в какие-то сверхмалые величины, которые зависят только от процессора по сути, а не от И/О. Стоит ли выедать мозг?
Если речь идет о соединении маленькой таблицы с большой то там почти наверняка будут использованы вложенные циклы, с мелкой таблицей в роли внешнего цикла, ведь слияние требует сортировки, а на большой таблице это дорогая операция. Хотя, если конечно мы затребуем сортировку... но и то, это все будет зависеть от распределения. В этом смысле полностью согласен с Mind. И кластерный индекс тут уж совсем не при чем.
8 июн 12, 19:48    [12690522]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный(изованный) индекс. Маленькие таблицы. Необходимость?  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
SomewhereSomehow, не знаю как я там "выедаю мозг", но я всего лишь привёл ещё один аргумент с пользу упорядоченности перед кучей.
SomewhereSomehow
Если речь идет о соединении маленькой таблицы с большой то там почти наверняка будут использованы вложенные циклы
Мне кажется будет более обидно, когда индекс на большой табле будет по этому малелькому справочнику и серевер будет бесполезно бегать по кругу, по этой таблице. А т.к. такая вероятность не так уж мала - то не использовать глупо.
С миру по нитке и ... получаем то что имеем - тормоза на современных гигагерцовых мультиядерных мультипотоковых железках.

Вот интересно, SomewhereSomehow, как часто вы делали измерения по изменению скорости работы для высоконагруженной системы. На примере хоть данной задачи. Это не придирка лично к вам, это скорее к вопросу о честности.

Упорядоченность - естественное состояние данных. Если это не стоит дорого по сравнению.
11 июн 12, 09:57    [12698558]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный(изованный) индекс. Маленькие таблицы. Необходимость?  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Mnior
SomewhereSomehow, не знаю как я там "выедаю мозг", но я всего лишь привёл ещё один аргумент с пользу упорядоченности перед кучей.
Мне кажется будет более обидно, когда индекс на большой табле будет по этому малелькому справочнику и серевер будет бесполезно бегать по кругу, по этой таблице. А т.к. такая вероятность не так уж мала - то не использовать глупо.
Тема про кучу и кластерный индекс, по-моему, уже где только не обсуждалась. Что я имел ввиду, так это то что, да, возможно вы сэкономите немного процессорного времени, но имхо, это будет очень маленькая величина, а по этому, стоит ли заморачиваться.
Mnior
Вот интересно, SomewhereSomehow, как часто вы делали измерения по изменению скорости работы для высоконагруженной системы. На примере хоть данной задачи. Это не придирка лично к вам, это скорее к вопросу о честности.
К вопросу о честности, я честно так думаю. Если б я что-то измерял, сказал бы что знаю. Если у вас есть такой пример, где создание кластерного индекса, на таблице в 100 записей и одной странице данных, приводит к значительному росту производительности, то я бы с удовольствием на него посмотрел, и сохранил бы себе в копилку знаний.
12 июн 12, 11:21    [12701469]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный(изованный) индекс. Маленькие таблицы. Необходимость?  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
SomewhereSomehow
стоит ли заморачиваться
Я о том же - стоит ли думать о кучах.

SomewhereSomehow
пример, где создание кластерного индекса, на таблице в 100 записей и одной странице данных, приводит к значительному росту производительности
Ха. Не воиспроизводимо на топорном коне в вакууме, а на практике вероятно (скуль тупит только на сложных запросах).
Так что тут с моей стороны проигрышная ситуация изначально, независимо от аргументов.

+ Скрипт
USE tempdb
GO
CREATE TABLE dbo.Dictionary (
	 ID	TinyInt	IDENTITY
	 CONSTRAINT [PK_Dictionary] PRIMARY KEY
	,Data	Int	NOT NULL
)
CREATE TABLE dbo.BigTabe (
	 Dictionary	TinyInt
	,[Date]		Date
	,CONSTRAINT [PK_BigTabe] PRIMARY KEY (
		 Dictionary
		,[Date]
	)
	,Data	Int	NOT NULL
)
CREATE TABLE dbo.DictionaryH (
	 ID	TinyInt	NOT NULL
	,Data	Int	NOT NULL
)
GO
INSERT	dbo.Dictionary
SELECT	Top(100) V.number
FROM	master.dbo.spt_values V
WHERE	V.[type] = 'P'

INSERT	dbo.BigTabe
SELECT	 X.ID
	,DateAdd(Year	,Y.number
	,DateAdd(Month	,M.number
	,DateAdd(Day	,D.number,0)))
	,Y.number
	+M.number
	+D.number
FROM	     dbo.Dictionary		X
	JOIN master.dbo.spt_values	Y ON Y.[type] = 'P' 
	JOIN master.dbo.spt_values	M ON M.[type] = 'P' AND M.number < 12
	JOIN master.dbo.spt_values	D ON D.[type] = 'P' AND D.number < 28

INSERT	dbo.DictionaryH
SELECT	*
FROM	dbo.Dictionary
GO
SET STATISTICS TIME ON

SELECT	 B.Dictionary
	,Sum(B.Data + D.Data)
FROM	     dbo.BigTabe	B
	JOIN dbo.Dictionary	D ON D.ID = B.Dictionary
GROUP BY B.Dictionary
OPTION(MAXDOP 1)
-- Время ЦП = 28 189 мс, затраченное время = 28 192 мс.

SELECT	 B.Dictionary
	,Sum(B.Data + D.Data)
FROM	     dbo.BigTabe	B
	INNER LOOP JOIN dbo.DictionaryH	D ON D.ID = B.Dictionary
GROUP BY B.Dictionary
OPTION(MAXDOP 1)
-- Время ЦП = 1 394 805 мс, затраченное время = 1 394 777 мс.
мс
MERGE28 192
LOOP 1 394 777


Можно исследовать варианты и видеть как реагирует скуль. Он не так глуп в простых ситуациях, чтоб не делать MERGE когда его "зажимают": MAXDOP 1 - добавляет Sort.

И кто среагирует на аргумент, про этот лишний оператор Sort?! Который возникает палюбэ на данном примере (с подсказками и без).
Почему он возникает практически при любых обстоятельствах?
13 июн 12, 10:55    [12706153]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный(изованный) индекс. Маленькие таблицы. Необходимость?  [new]
SomewhereSomehow
Member

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

Не по теме: Зачем столько строк? =) Я по неосторожности, не прикинув получающееся количество, запустил ваш пример на домашнем компе – не дождался даже вставки. Короче сузил диапазон до 3-4 миллионов вместо 67, вот так:
JOIN master.dbo.spt_values	Y ON Y.[type] = 'P' AND Y.number between 1900 and 2000


По теме:
карлсон
“Знаешь, кто это такие? О, брат! Это жулики! Они замышляют зловещие преступления на крыше!” (с)
=)

Пример яркий, но нечестный. Если обратить внимание на второй план, то видно, что в случае хинта loop join в соединении внешней указана BigTable, ведь когда мы указываем хинт в соединении мы отменяем эвристику перестановок и фактически делаем force order!

Естественно, он начинает бешено спулить, записывая 3м*100 строк! Отсюда такая драматическая разница. Не говоря уже о том, что нехорошо сравнивать результаты кучи и кластерного в разных условиях. Вот более честные результаты, (это не придирка лично к вам, а к вопросу о честности =)).

Не приводя каждый раз запрос, вот - костяк запроса:
SELECT	 B.Dictionary
	,Sum(B.Data + D.Data)
FROM	     dbo.BigTabe	B
	INNER JOIN dbo.Dictionary	D ON D.ID = B.Dictionary
GROUP BY B.Dictionary

Теперь просто опции и результаты (время) выполнения при их применении поочередно к каждому запросу (CI – Clustered Index, H – Heap):
								--1
--OPTION(MAXDOP 1)				--2
--option(loop join)				--3
--option(loop join, MAXDOP 1)	--4


1) Без опций:
CI:
Время: Время ЦП = 1265 мс, затраченное время = 373 мс.
План: параллельный, сортировки нет, тип соединения NLJ.
H:
Время: Время ЦП = 1235 мс, затраченное время = 371 мс.
План: параллельный сортировка есть, тип соединения NLJ.

2) Ограничиваем параллелизм:
CI:
Время: Время ЦП = 1328 мс, затраченное время = 1378 мс.
План: последовательный, сортировки нет, тип соединения MG.
H:
Время: Время ЦП = 1625 мс, затраченное время = 1709 мс.
План: последовательный, сортировка есть (оно объяснимо т.к. мердж ее требует), тип соединения MG.

3) Не ограничиваем параллелизм, но ограничиваем тип соединения до NLJ:
CI:
Время: Время ЦП = 1344 мс, затраченное время = 417 мс.
План: параллельный, сортировки нет, тип соединения NLJ.
H:
Время: Время ЦП = 1250 мс, затраченное время = 386 мс.
План: параллельный сортировка есть, тип соединения NLJ.

4) Ограничиваем параллелизм, ограничиваем тип соединения до NLJ:
CI:
Время: Время ЦП = 1172 мс, затраченное время = 1225 мс.
План: последовательный, сортировки нет, тип соединения NLJ.
H:
Время: Время ЦП = 984 мс, затраченное время = 1046 мс.
План: последовательный сортировка есть, тип соединения NLJ.

Насчет сортировки. В случае с мерджем понятно, зачем сортировка. Теперь, зачем сортировка в NLJ? Он сортирует так как потом применяет группировку stream agg по этому полю [tempdb].[dbo].[DictionaryH].ID, чтобы в этом убедиться можно добавить хинт hash group. Тогда сортировки не будет.
За это, судя по всему, отвечает правило GbAggToStrm, если его вот так отключить в этом запросе, например, при помощи хинта queryruleoff GbAggToStrm, о котором я писал тут: Оптимизатор (недокументированное): Отключить правила преобразования в отдельном запросе. То сортировки тоже не будет, однако в плане Stream Agg заменится на Hash Agg.

Итоги:
- Данный пример не характеризует никаких преимуществ либо кучи, либо кластерного индекса в случае с такой таблицей. Всё на уровне сверхмалых величин и погрешностей (хотя я запускал примеры несколько раз и стабильно где-то выигрывал индекс, а где-то куча, предсказуемо где, но все на уровне погрешностей). Как я и говорил.
Кластерный индекс – действительно дает какую-то процессорную выгоду в случае сортировки, которая может быть выполнена упорядоченным сканом (хотя он может нивелироваться фрагментацией, хотя при таких объемах – это! просто! смешно!)

- Mnior прав, в том случае, если разработчик начинает необдуманно хинтовать – это играет роковую роль (впрочем это касается не только данной темы).

- И, я думаю, все правы в том, что зачем обсуждать такую ерунду, и вообще, коня в вакууме? О чем я и говорил, удивляясь, насколько разрослась тема. Если б были действительные теоретические предпосылки к однозначному определению – сообщество бы их знало.

- Другое дело куча vs кластерный индекс на обычных таблицах…. Но это уже холи вар. =) За себя скажу, что я припомню только одну кучу у себя в базе, которая является просто реплицируемой таблицей внешней системы. Остальные все индексирую, ибо есть еще и другие причины так поступать.

ЗЫ:
То что Mnior привел неудачный пример, вообще не исключает удачного примера, т.е. когда кластерный индекс окажется продуктивнее или наоборот куча круче =). Но тем, кто сильно не разбирается, но ищет решение проблемы в маленькой таблице куче - совет - поищите тщательнее в других местах. А если нет, то велкам сюда пример, с таблицами, запросами, планами!
13 июн 12, 21:12    [12710456]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный(изованный) индекс. Маленькие таблицы. Необходимость?  [new]
SomewhereSomehow
Member

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

Mnior,
Nuff Said?
13 июн 12, 21:47    [12710528]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный(изованный) индекс. Маленькие таблицы. Необходимость?  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
SomewhereSomehow
Естественно, он начинает бешено спулить, записывая 3м*100 строк!
Эээ, в плане вроде как Lazy спул этих 100 строк. В добавок, к слову, из-за 100 строк он не смог подобрать Index Spool.

SomewhereSomehow
option(loop join)
Не согласен. (там сортировка и порядок не тот)
1. Нельзя предсказать как протупит сервер на сложных запросах.
2. Вы не избавились от сортировки. Вы тестировали не то что я хотел показать.
3. Я подобрал случай показывающий, что скан 100 строк в LOOP-е очень дорого. Не более.

Давно хотел протестировать SEEK против скана:
+ Скрипт
SELECT	 B.Dictionary
	,Sum(B.Data + D.Data)
FROM	     dbo.BigTabe	B
	INNER LOOP JOIN dbo.DictionaryH	D ON D.ID = B.Dictionary
GROUP BY B.Dictionary
OPTION(MAXDOP 1)

SELECT	 B.Dictionary
	,Sum(B.Data + D.Data)
FROM	     dbo.BigTabe	B
	INNER LOOP JOIN dbo.Dictionary	D ON D.ID = B.Dictionary
GROUP BY B.Dictionary
OPTION(MAXDOP 1)
мс
SCAN (Table Spool)1 389 568
SEEK69 286
Йееессссс!!!1111адин адин
Доказано, MS (и компашка) неправы. SEEK палюбэ лучше скана.
Очень часто на табличных переменных так происходит (LOOP + SCAN по кластерному ключу). Типа "статистически" там мало строк.
Это баг просто выбешивает.

Одна строка? Да пжалуста!
мс
SCAN2343
SEEK683
Кто #%здел что скан на одной строке быстрее?! Утритесь!
Всё, %$#ть, мотивация сделать MS фейсом об тайбл повысилать, дойдут руки буду продавливать решение бага.

SomewhereSomehow
применяет группировку stream agg, а c хинт-ом hash group сортировки не будет
Да, и очевидно что Stream агрегирование эффективнее хешового. Порядок в данных это очень полезная вещь.

SomewhereSomehow
- Данный пример не характеризует никаких преимуществ либо кучи, либо кластерного индекса в случае с такой таблицей.
На что он и не претендовал.
14 июн 12, 12:35    [12712884]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный(изованный) индекс. Маленькие таблицы. Необходимость?  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Mnior
Эээ, в плане вроде как Lazy спул этих 100 строк. В добавок, к слову, из-за 100 строк он не смог подобрать Index Spool.
И что? Т.е. заспулить таким образом триста лямов строк пустяк?

Mnior
SomewhereSomehow
option(loop join)
Не согласен. (там сортировка и порядок не тот)
1. Нельзя предсказать как протупит сервер на сложных запросах.
2. Вы не избавились от сортировки. Вы тестировали не то что я хотел показать.
3. Я подобрал случай показывающий, что скан 100 строк в LOOP-е очень дорого. Не более.

Порядок чего не тот? Таблиц? Ну да, я позволил серверу выбрать порядок таблиц такой какой он посчитает нужным, ограничив только алгоритм соединения. А вы ограничили алгоритм, плюс еще и перестановки, и все что показывает ваш пример, это насколько неэффективным может получиться план если мы строго зададим неудачный порядок таблиц в соединении.
Если уж вы настаиваете на использовании хинта в секции from, то вы хотя бы порядок таблиц нормальный задайте, сравните:
set statistics time on
go
SELECT	 B.Dictionary
	,Sum(B.Data + D.Data)
FROM	     dbo.BigTabe	B
	INNER LOOP JOIN dbo.DictionaryH	D ON D.ID = B.Dictionary
GROUP BY B.Dictionary
OPTION(MAXDOP 1)

SELECT	 B.Dictionary
	,Sum(B.Data + D.Data)
FROM	     
	dbo.DictionaryH	D 
	INNER LOOP JOIN dbo.BigTabe	B ON D.ID = B.Dictionary
GROUP BY B.Dictionary
OPTION(MAXDOP 1)
go
set statistics time off

Warning: The join order has been enforced because a local join hint is used.
Warning: The join order has been enforced because a local join hint is used.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 15 ms.
(100 row(s) affected)
 SQL Server Execution Times:
   CPU time = 50782 ms,  elapsed time = 50783 ms.
(100 row(s) affected)
 SQL Server Execution Times:
   CPU time = 750 ms,  elapsed time = 754 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 30 ms.

Вот и вся разница. Кстати, вон сервер даже warning-ами плюется...
И зачем мне было избавлять от сортировки? Можно конечно избавиться, как именно, я кстати тоже написал, ну а смысл, если она нужна для последующей группировки? К тому же, если посмотреть на план (с нормальным порядком таблиц), то видно что она выполняется всего один раз и практически бесплатно.
Mnior
Давно хотел протестировать SEEK против скана:

Порядок таблиц поменяйте и повторите тест.
Хотя, в принципе, цифры я уже приводил вчера.
14 июн 12, 13:41    [12713480]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный(изованный) индекс. Маленькие таблицы. Необходимость?  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
SomewhereSomehow
все что показывает ваш пример, это насколько неэффективным может получиться план если мы строго зададим неудачный порядок таблиц в соединении.
Нет, этого я даже и не думал. Повторяю, я хотел показать что поиск в одной странице методом скана тотально неэффективно. Только это.
Показать порядки тормозов этой очевиднейшей вещи.

SomewhereSomehow
то вы хотя бы порядок таблиц нормальный задайте
... И зачем мне было избавлять от сортировки?
Повторяю:
1. Нельзя предсказать как протупит сервер на сложных запросах.
2. См абзац выше

SomewhereSomehow
она выполняется всего один раз и практически бесплатно
Очевидно же. Т.к. любой простой пример не даст того как гипотетически может быть. Поэтому рассматривался крайний вариант.
Т.к. этот аргумент могут по глупости использовать: "Переделаю кластер а в кучу т.к. всё равно сортировка дешёвая" (при нагрузке в 100500 запросов в секунду).
Прям как из МФТИ-шного анекдота.

SomewhereSomehow
Mnior
Давно хотел протестировать SEEK против скана:
Порядок таблиц поменяйте и повторите тест.
Хотя, в принципе, цифры я уже приводил вчера.
$лять, вы не поняли суть вопроса!

Повторюсь:
Mnior
Очень часто на табличных переменных оптимизатор без всяких хинтов делает (LOOP + SCAN по кластерному ключу).

----
Главное. На самом деле, изучая данный вопрос и видя тендеции постановка приоритетов меняется.
Покамест не приведётся случай, когда на практике куча эффективнее кластерного, будет считаться что она зло.

Чтобы вопрос не остался незамеченным: Куча зло в любом случае!!!
На любое сказанное на этом форуме, что "иногда куча эффективнее" будет говорится обратное и "Пруф в студию". С последующем срачем.
14 июн 12, 19:31    [12716060]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный(изованный) индекс. Маленькие таблицы. Необходимость?  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Mnior
SomewhereSomehow
все что показывает ваш пример, это насколько неэффективным может получиться план если мы строго зададим неудачный порядок таблиц в соединении.
Нет, этого я даже и не думал. Повторяю, я хотел показать что поиск в одной странице методом скана тотально неэффективно. Только это.
Я бы перефразировал - поиск одной строки в одной странице методом скана тотально неэффективно. И еще LOOP JOIN не эффективен для поиска во внешней таблице если на ней нет индекса и выполняется скан.
Ваш пример показывает только то, что сервер может делать глупость, когда ему не оставляют выбора. В реальности же, поиск одной строки путем скана таблицы в одну страницу или поиском по индексу будет отличаться по скорости в 2-4 раза, что выражается в миллисекундах разницы. Да конечно, если помножить это на несколько миллионов вызовов, каждый из которых вытягивает всего одну строку, то могут получится секунды и то это не критично при таком количестве запусков. И повторяю, это в случае если запрос будет обращатся только к этой таблице, типа такого
SELECT * FROM dbo.Dictionary WHERE ID = @ID
Такой запрос весьма бессмысленно дергать миллионы раз в секунду учитывая, что в таблице 100 строк.
Если же использовать мелкую таблицу в запросах с JOIN-ами, то как уже было сказано SomewhereSomehow, сервер, в общем случае, не выберет план с LOOP JOIN + TABLE SCAN по внешней таблице, так как есть более эффективные способы соединения в этом случае.

Mnior
SomewhereSomehow
то вы хотя бы порядок таблиц нормальный задайте
... И зачем мне было избавлять от сортировки?
Повторяю:
1. Нельзя предсказать как протупит сервер на сложных запросах.
При таком подходе, вообще нельзя ни в чем быть уверенным и единственный выход тогда это хинтование каждой таблицы в запросе + FORCE ORDER на всякий случай.

Mnior
Доказано, MS (и компашка) неправы. SEEK палюбэ лучше скана.
Если вам нужна всего одна строка, то конечно SEEK немного лучше чем SCAN, а с этим кто-то спорит? И на этой самой одной строке разница не очень большая.
Mnior
Очень часто на табличных переменных так происходит (LOOP + SCAN по кластерному ключу). Типа "статистически" там мало строк.
Это баг просто выбешивает.
Это не баг, а "developed as designed" и это просто нужно всегда учитывать при выборе что между табличной переменной или временной таблицы.
Mnior
Чтобы вопрос не остался незамеченным: Куча зло в любом случае!!!
На любое сказанное на этом форуме, что "иногда куча эффективнее" будет говорится обратное и "Пруф в студию". С последующем срачем.
Согласен, но иногда куча все-таки эффективнее Картинка с другого сайта., попробую найти пруф.
15 июн 12, 00:12    [12716807]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный(изованный) индекс. Маленькие таблицы. Необходимость?  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Mind
Ваш пример показывает только то, что сервер может делать глупость, когда ему не оставляют выбора.
Неа. В моём примере сервер глупость не делает. Мой пример показывает стоимость конкретной конструкции. (которую я построил на хинтах)
Да я придираюсь к словам, но за маленькими ошибками скрываются большие глупости/мифы. Звиняйте
Mind
В реальности же, поиск одной строки путем скана таблицы в одну страницу или поиском по индексу будет отличаться по скорости в 2-4 раза, что выражается в миллисекундах разницы.
Всё немного драматичнее.
Т.к. эвристики оптимизатора допускаю такую явную глупость, то возникают ситуации (статистика неверна, или её нет или невозможна; таймаут компиляции). И это начинает вываливаться в жуткие тормоза в неподходящий момент на практике.

Mind
сервер, в общем случае, не выберет план с LOOP JOIN + TABLE SCAN
Да, в основном планы хорошие, конечно с этим я и не думал спорить - эту очевидность я не подчёркивал, уделяя больше внимания имеющимся проблемам (то что это лишь маловероятно, но иногда проскакивает как "best" plan без хинтов).

Mind
При таком подходе, вообще нельзя ни в чем быть уверенным и единственный выход тогда это хинтование каждой таблицы в запросе + FORCE ORDER на всякий случай.
Вы немного перегибаете. Подходов много. Проблема в оптимизаторе, а не в его использовании.

Mind
И на этой самой одной строке разница не очень большая.
Вы тоже немного не поняли. Проблема не в малой разнице. А в том что:
1. вообще допускается такая конструкция оптимизатором (есть много пруфов на практике, и на моём личном опыте)
2. некоторые явно писали "скан на малых таблах лучше чем SEEK"
3. статистика может ошибаться, а ошибка очень дорогая.
+ Выкладки
Даже если допустить что скан на одной строке быстрее на 1%, но ведь чем больше строк в табле тем быстрее растёт разница в отставании.
Получается что скуль готов выбрать LOOP SCAN ради 1 микросекунды (на его эвристиках), ради нехилой вероятности (при неправильной статистике) попасть на секунды/минуты.
Не стоит экономить на спичках, когда есть вероятность при этом попасть на лимоны. Это правило нельзя нарушать при разработке. Тем более в широко используемом продукте.

Mind
попробую найти пруф
Жду с нетерпением.

PS: При анализах поведения оптимизатора прослеживается явная любовь к приближённым планам, со слепотой к схемам/структуре данных/запроса.
Схема/структура должна быть первична, статистика вторична (дополнять картину, а не переделывать/перебивать).
15 июн 12, 01:34    [12716926]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный(изованный) индекс. Маленькие таблицы. Необходимость?  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Mnior
Mind
попробую найти пруф
Жду с нетерпением.

А чего его искать, вон возьмем тот же пример от Mnior. И ту же методику "демонстрации" отсюда 12706153.
Только теперь наоборот, хинт перенесем в запрос с кластерным индексом, а из запроса с кучей уберем.
+
set statistics time,io on
go
SELECT	 B.Dictionary
	,Sum(B.Data + D.Data)
FROM	     dbo.BigTabe	B
	>>INNER LOOP JOIN dbo.Dictionary<<<	D ON D.ID = B.Dictionary
GROUP BY B.Dictionary
OPTION(MAXDOP 1)

SELECT	 B.Dictionary
	,Sum(B.Data + D.Data)
FROM	     
	dbo.BigTabe	B
	join dbo.DictionaryH D ON D.ID = B.Dictionary
GROUP BY B.Dictionary
OPTION(MAXDOP 1)
go
set statistics time,io off

Результат:
Table 'Dictionary'. Scan count 0, logical reads 6787200, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'BigTabe'. Scan count 1, logical reads 7158, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
   CPU time = 3359 ms,  elapsed time = 3433 ms.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DictionaryH'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'BigTabe'. Scan count 1, logical reads 7158, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
   CPU time = 1266 ms,  elapsed time = 1338 ms.

Практически в 3-и раза медленнее! По вашей логике это доказывает, что CI в три раза хуже!
В общем, я не знаю что там вы хотели показать таким очевидно нечестным методом сравнения, но меня вы такой демонстрацией убедили только в том, что если разработчик бездумно ставит хинты, то можно испортить все что угодно, о чем собственно я и так всегда знал.
А что касается сложных запросов, там вообще заранее предсказывать поведение оптимизатора невозможно.
Так что, с вашими однозначными выводами не согласен, но переубедить вас, я чувствую не получится...
15 июн 12, 09:26    [12717333]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный(изованный) индекс. Маленькие таблицы. Необходимость?  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
SomewhereSomehow
А чего его искать, вон возьмем тот же пример от Mnior. И ту же методику "демонстрации"...
Вы тролите? Вы непоняли смысл вышесказаного и пропагандируете ваше "понимание" моих слов?

Я ставил вопрос в таком ракурсе: нужно рассмотреть все случаи которые могут попадаться. Не пример целиком, а элементы плана реально попадающиеся на практике.
Для каждого случая нужно проверить эффективность(1) и последствия(2) при неблагоприятных условиях.
Нужно показать что та же самая логическая консрукция плана (к примеру соединение) строит лучший физический план и не даёт больших проблем при неблагоприятных условиях (оказалось, что там не 1 строка а лимон).

SomewhereSomehow
убедили только в том, что если разработчик бездумно ставит хинты ...
Зарубите уже. Хинты никто не ставил, а высчитывал стоимости разных конструкций. Хинты это не тест оптимизатора,
это язык теста - средство получения нужного оператора для теста. Хинты мы не рассматриваем вааще.
SomewhereSomehow
А что касается сложных запросов, там вообще заранее предсказывать поведение оптимизатора невозможно.
Вот тут вы показали "логику" мышления.
1. SomewhereSomehow не может предсказать поведение. Отказывается понимать.
2. Есть сверх простые запросы показываюшие что куча не лучше.

То что вы не хотите рассматривать сложный вариант, именно не хотите вы делаете "вывод" - куча гипотетически может быть быстрее.
Эта стратегия человеческого мышления понятна и повсеместна. И оправдана. Но она не сравнится со стратегий углублённого рассмотрения - логических выводов:
1. При одинаковых логических конструкциях - физические планы для кучи не лучше чем для кластера (порядок).
2. При выборе оптимизатором планов, с кучей профтыков не меньше (точнее нет случаев что при куче "профтыка" небыло, а с кластером был)
Выдод уже понятен.

Проблема в том что все случаи тажело перебрать. Тогда смотрится контр аргументы и тендеции/эвристики. И тут только обмен опытом.

Поэтуму жду контр аргументов или примеров "иного" опыта.

PS: SomewhereSomehow, есть подозоения что у вас проблемы с матаном или вообще с доказательствами, их методами и стратениями решений. Как вы относитесь к ТРИЗ?
15 июн 12, 14:05    [12719888]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный(изованный) индекс. Маленькие таблицы. Необходимость?  [new]
SomewhereSomehow
Member

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

Не троллю. Никакого перевирания ваших слов нет. Вы привели два конкретных примера с цифрами, сравнивать которые - некорректно. При этом не учли или проглядели ключевой момент различия в производительности. Теперь вы пытаетесь выкручиваться придумывая какие-то сравнения "всех возможных вариантов" или "элементы плана на практике".
Если б можно было рассмотреть все варианты, зачем вообще вся морока с оценками, получил все варианты, а потом только знай выбирай из них.
А вот этот пассаж:
Mnior
Повторяю:
1. Нельзя предсказать как протупит сервер на сложных запросах.
2. См абзац выше

И далее коммент той же самой мысли, но высказанной мной...
Mnior
SomewhereSomehow
А что касается сложных запросов, там вообще заранее предсказывать поведение оптимизатора невозможно.
Вот тут вы показали "логику" мышления.
1. SomewhereSomehow не может предсказать поведение. Отказывается понимать.
2. Есть сверх простые запросы показываюшие что куча не лучше.

Логика, ау?

Mnior
То что вы не хотите рассматривать сложный вариант, именно не хотите вы делаете "вывод" - куча гипотетически может быть быстрее.

Очередные приписки, а? Вот что я говорил, вообще-то
SomewhereSomehow
То что Mnior привел неудачный пример, вообще не исключает удачного примера

Короче, все это выглядит так, как будто вы сваляли ваньку, а теперь пытаетесь сбить все на абстрактные темы и порете всякую чушь.
п.с.
Матан как и прочие науки давно позади, но учил их в свое время не за страх а за совесть. А вот ваша логика, мягко говоря, очень спорная.
15 июн 12, 15:21    [12720775]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3 4   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить