Comments 8
Здесь какая то опечатка? Вы создаете вычисляемый столбец AboutMeHash
ALTER TABLE dbo.Users
ADD AboutMeHash AS CHECKSUM(AboutMe); А в запросе его не используете
SELECT *
FROM dbo.Users
WHERE CHECKSUM(AboutMe) = CHECKSUM(@var) AND AboutMe = @var
Я имена вычисляемых столбцов вообще нигде по тексту не использую - только их определения.
MSSQLSERVER - умный, и в этом случае задействует индекс по вычисляемому полю.
Один из приемов ускорения запросов, в том случае, когда запрос изменить нельзя (например запрос летит из клиентского приложения, которое фиг поменяешь) - как раз и состоит в том, чтобы обнаружить подобные случаи, создать вычисляемые столбцы, аналогичные используемому выражению, и создание по ним индексов.
Засовывать varchar(max) в included-колонки индекса - так себе практика. Индекс распухнет и перестанет быстро сканироваться (понадобится много чтений страниц даже для бинарного поиска в нем) - так как included-колонки хранятся в нем «по значению» ;). Туда хорошо ложатся компактные FK для дальнейших связок и всякие скалярные признаки, по которым может идти поиск.
Computed колонки лучше делать persisted (по ним, кстати, тогда можно тоже построить индекс и даже ПК). В Вашем случае можно сделать computed persisted-колонку LEFT(x, 200) и индекс по ней - если реально данные не длиннее 200 символов. Не забывайте ещё, что varchar(max) хранятся в куче, а в таблице - только ссылки на них - поэтому выборка для поиска может быть ещё дольше
Засовывать varchar(max) в included-колонки индекса - так себе практика.
Согласен, вроде и не утверждал обратного, хотя иногда и приходится.
Computed колонки лучше делать persisted
Можете пояснить - зачем persisted? Зачем хранить и в кластерном индексе, и в самом индексе, если они мне особо и не нужны, а только для оптимизации запроса?
computed persisted-колонку LEFT(x, 200) и индекс по ней - если реально данные не длиннее 200 символов.
Зависит от того, какая средняя длина. Я предпочитаю объявлять больше, чтобы не было спилов в tempdb, если в выборке будут слишком длинные varchar'ы.
DROP INDEX ix_ON ON smth (field2_calculated);
GO
ALTER TABLE smth
DROP COLUMN field2_calculated;
GO
ALTER TABLE smth
ADD field2_calculated AS LEFT(field2, 200) PERSISTED;
GO
CREATE INDEX ix_ON ON smth (field2_calculated);
GOMsg 1919, Level 16, State 1, Line 18 Column 'field2_calculated' in table 'smth' is of a type that is invalid for use as a key column in an index.
Явно приводить тип всё равно нужно.
Не забывайте ещё, что varchar(max) хранятся в куче
зависит от реальной длины строки
Сорри, я прочитал статью на телефоне практически по диагонали - сейчас смотрю на компьютере и вижу, что вопросов у меня нет :)
Добавлю только, что я лично не уверен, что SQL Engine всегда будет понимать выражение с колонкой, и заменять его на computed, поэтому в своих конструкциях я полагаюсь на явное использование имени СС, при этом делаю ее PERSISTED, и если нужен индекс, включаю ее в селектор или в included. В примере с контрольной суммой CLOBа я бы сделал AboutMeHash AS BINARY_CHECKSUM(AboutMe) PERSISTED и построил бы индекс на AboutMeHash.
PS - Вы абсолютно правильно пишете про key lookup для широких таблиц - это очень затратная операция - этот факт нужно прописать болдом в мануале разработчиков - так как выборка по разреженным ключам при большой длине записи приводит к хаотичному чтению большого количества страниц, часто ради одного-двух ключей по которым таблицы связываются дальше - поэтому если в execution plan вы видите, что key lookup делается для вытаскивания компактного набора данных, и он относительно статичен (не апдейтится часто) - то добавление его в included индекса существенно ускоряет весь велосипед.
Согласен, вроде и не утверждал обратного, хотя иногда и приходится.
А вот зачем приходится, не скажите? Ведь картинка будет та же - сначала будет найден ключ, а потом сервер полезет на LOB страницу. Что в лоб, что по лбу. Разве что для фильтрованных индексов имеет смысл.
Можете пояснить - зачем persisted? Зачем хранить и в кластерном индексе, и в самом индексе, если они мне особо и не нужны, а только для оптимизации запроса?
Чтобы не тратить время на пересоздание значения поля при каждом подъеме данных в память.
Впрочем, если вы построите по такому полю индекс (когда то давно это можно было делать только по persisted) - сервер всё равно материализует значение и сохранит в индексе.
Благодарю за интересный материал.
В своей практике часто использую вычисляемое сохраняемое поле как хэшсумма по большому полю или группы полей. На это поле навешивается индекс. В самом запросе сначала явно пишется фильтр по этому полю (равенство) и только потом фильтр уточняющий по исходным полям (равенство).
В принципе, такая идея лежит в .NET для тех ссылочных типов, когда сначала сравнивается их хэш двух объектов и если они одинаковые, то только потом сравнение идёт непосредственно по указанным полям. Так достигается лучшая производительность, чем тотально проверять сразу все поля типа на идентичность.
Computed Columns и nvarchar(max)