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

Откуда:
Сообщений: 12
Возникла некоторая странность, есть таблица с индексом
create table [test] ([a] int, [b] int)
create nonclustered index [test_a] on [test] ([a] asc) include ([b])


Далее делается простой селект
select * from [test] where [a] in (1, 2, 3, ....)


Так вот если указать в скобках количество значений меньше 64, то запрос отрабатывает исключительно по index seek, но вот если указать количество значений большее, то план запроса меняется на index scan + filter, и естественно весь список этих значений попадает в фильтр и индекс по сути не работает.

Пробовал чинить всякими хинтами, но ничерта не помогло, единственный способ который помог, это добавить еще одно условие в запрос
select * from [test] where [a] in (1, 2, 3, ....) or [a] < 0


Данное условие никак не влияет на результат выборки, но меняет план на использование index seek.

Проблема заключается в том, что нет простой возможности напихать везде подобные "спасительные" условия, а хочется понять, что за магия происходит и как этим бороться вменяемыми способами.
26 апр 19, 18:37    [21872944]     Ответить | Цитировать Сообщить модератору
 Re: Использование индексов  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36972
fallenyasha
но вот если указать количество значений большее, то план запроса меняется на index scan + filter,
В какой-то момент скан становится вогоднее кучи seek, это нормально. Ну или тут какие-то правила оптимизации in с константами.

fallenyasha
Пробовал чинить всякими хинтами, но ничерта не помогло, единственный способ который помог, это добавить еще одно условие в запрос
Для этого давно сделали хинт with ( forceseek )

Сообщение было отредактировано: 26 апр 19, 18:43
26 апр 19, 18:40    [21872945]     Ответить | Цитировать Сообщить модератору
 Re: Использование индексов  [new]
fallenyasha
Member

Откуда:
Сообщений: 12
В том то и дело, что forceseek ничего не дал, кроме усложнения плана, происходит index scan + join.
26 апр 19, 18:51    [21872953]     Ответить | Цитировать Сообщить модератору
 Re: Использование индексов  [new]
fallenyasha
Member

Откуда:
Сообщений: 12
Так же можно использовать перечисление вида a=1 or a=2 or ..., это никак не влияет на план.

Сие точно надо как-то исправлять, т.к. скан на 1000 записей на реальной таблице работает больше 10 секунд, а сик меньше секунды, так что скан ну уж точно не выгоднее.
26 апр 19, 18:54    [21872956]     Ответить | Цитировать Сообщить модератору
 Re: Использование индексов  [new]
Посетитель
Member

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

переделайте в джоин
26 апр 19, 19:02    [21872966]     Ответить | Цитировать Сообщить модератору
 Re: Использование индексов  [new]
.Евгений
Member

Откуда:
Сообщений: 516
fallenyasha
Так вот если указать в скобках количество значений меньше 64

Снова 64 элемента и отказ оптимизатора от оптимизации...
Картинка с другого сайта.
26 апр 19, 19:48    [21872988]     Ответить | Цитировать Сообщить модератору
 Re: Использование индексов  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Посетитель
fallenyasha,

переделайте в джоин
Джойн с табличной переменной. Тогда точно будет seek.
26 апр 19, 22:42    [21873090]     Ответить | Цитировать Сообщить модератору
 Re: Использование индексов  [new]
fallenyasha
Member

Откуда:
Сообщений: 12
К сожалению это невыполнимо, запросы формируются Entity Framework и это легаси-код, и по сути все на что можно повлиять это индексы, настройки БД, возможно какие-то параметры подключения, может что еще по мелочевке, но переписывать код, заменяя EF на прямые запросы вообще не вариант :( Поэтому и стоит задача, понять откуда растет эта проблема и как ее можно решить.
26 апр 19, 23:19    [21873130]     Ответить | Цитировать Сообщить модератору
 Re: Использование индексов  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
fallenyasha
запросы формируются Entity Framework
А, понятно, архитектурная ошибка :-(
26 апр 19, 23:20    [21873132]     Ответить | Цитировать Сообщить модератору
 Re: Использование индексов  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36972
fallenyasha
Поэтому и стоит задача, понять откуда растет эта проблема и как ее можно решить.
Из Entity Framework, очевидно же.
26 апр 19, 23:25    [21873135]     Ответить | Цитировать Сообщить модератору
 Re: Использование индексов  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
Гавриленко Сергей Алексеевич
fallenyasha
Поэтому и стоит задача, понять откуда растет эта проблема и как ее можно решить.
Из Entity Framework, очевидно же.
Добавлю для fallenyasha, что печально не только использование Entity Framework, но и то, для чего его используют.

А именно, цель горе-менеджеров и горе-архитекторов - достичь 2 фантастически полезных результата:
1) в проекте не нужно иметь ни одного специалиста по БД (что сразу видно по планам запросов),
2) при проблемах не надо звать DBA, ведь можно всё легко и просто поправить в C# коде (хихи).

Итого, модель данных и их потоков безумная, планы запросов можно распечатывать на всю стену, и поправить это можно только каким то шаманством, тыкать в статистики и индексы, авось что то поможет.
26 апр 19, 23:46    [21873148]     Ответить | Цитировать Сообщить модератору
 Re: Использование индексов  [new]
fallenyasha
Member

Откуда:
Сообщений: 12
Предлагаете заняться оптимизацией EF? :)

По сути EF всего лишь формирует запрос, который я описал в начале, т.е. просто селект записей из таблицы по определенному условию, где условием является набор неких идентификаторов например. А тупит именно SQL, что неправильно формирует план по непонятной причине, задача сводится к тому, как ему объяснить что он не прав, бишь как реально принудительно включить seek.
26 апр 19, 23:47    [21873149]     Ответить | Цитировать Сообщить модератору
 Re: Использование индексов  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36972
fallenyasha
Предлагаете заняться оптимизацией EF? :)

По сути EF всего лишь формирует запрос, который я описал в начале, т.е. просто селект записей из таблицы по определенному условию, где условием является набор неких идентификаторов например. А тупит именно SQL, что неправильно формирует план по непонятной причине, задача сводится к тому, как ему объяснить что он не прав, бишь как реально принудительно включить seek.
Задайте это вопрос создателям EF, зачем они таким образом делают запросы. Сделать seek по списку значений в MSSQL есть вагон и маленькая тележка способов, главное, видимо, in не использовать.

Сообщение было отредактировано: 27 апр 19, 09:13
27 апр 19, 00:07    [21873157]     Ответить | Цитировать Сообщить модератору
 Re: Использование индексов  [new]
fallenyasha
Member

Откуда:
Сообщений: 12
Реальный запрос выглядит так:
SELECT 
    [Extent1].[Id] AS [Id], 
    ...
FROM [dbo].[CompanyUsers] AS [Extent1]
    WHERE 
--  ((''> [Extent1].[ExternalId]) AND (0 = [Extent1].[IsDeleted])) or
    ((N'TEST_USER_EXTERNAL_ID_TEST_USER_NAME_0_927e69fc89b14e23bfd1306a0e111c0e' = [Extent1].[ExternalId]) AND (0 = [Extent1].[IsDeleted])) OR
    ....
    ORDER BY row_number() OVER (ORDER BY [Extent1].[Id] ASC)
    OFFSET 0 ROWS FETCH NEXT 2147483647 ROWS ONLY 


IN я использовал просто для краткости, а вообще его использование никак не влияет на результат.

Закоментарен костыль, включающий seek и ускоряющий работу этой шайтан-машины более чем в 10 раз.
27 апр 19, 00:15    [21873158]     Ответить | Цитировать Сообщить модератору
 Re: Использование индексов  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34608
fallenyasha,

Так в чем странность?
Это ожидаемое поведение
27 апр 19, 08:11    [21873214]     Ответить | Цитировать Сообщить модератору
 Re: Использование индексов  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34608
fallenyasha
К сожалению это невыполнимо, запросы формируются Entity Framework и это легаси-код, и по сути все на что можно повлиять это индексы, настройки БД, возможно какие-то параметры подключения, может что еще по мелочевке, но переписывать код, заменяя EF на прямые запросы вообще не вариант :( Поэтому и стоит задача, понять откуда растет эта проблема и как ее можно решить.


Никак её не решит
Тебе надо перейти от синтетического примера к решению проблем производительности твоих конкретных запросов, разные запросы по-разному оптимизируется.
И структуры у тебя не такие, и данные не такие...
Ты не можешь починить один запрос решая проблема производительности другого запроса.
27 апр 19, 08:16    [21873218]     Ответить | Цитировать Сообщить модератору
 Re: Использование индексов  [new]
fallenyasha
Member

Откуда:
Сообщений: 12
Товарищи, забудьте про EF, забудьте про разность запросов, это все и так понятно, сейчас решил эту задачу через сей костыль, все нормально работает для разных таблиц, полей и т.д.

Осталась непонятна логика SQL, на основании каких критериев принимается решение об использовании seek или scan, хочется докопаться до истины и понять нутро и методы воздействия на него, чтобы в будущем более осмысленно писать запросы, а не методом перебора планов и костылей.
27 апр 19, 09:32    [21873238]     Ответить | Цитировать Сообщить модератору
 Re: Использование индексов  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36972
fallenyasha
Осталась непонятна логика SQL, на основании каких критериев принимается решение об использовании seek или scan, хочется докопаться до истины и понять нутро и методы воздействия на него, чтобы в будущем более осмысленно писать запросы, а не методом перебора планов и костылей.
По статистике или, возможно, по каким-то правилам оптимизации. Зависит от запроса. Вы про какой именно сейчас?
27 апр 19, 10:07    [21873246]     Ответить | Цитировать Сообщить модератору
 Re: Использование индексов  [new]
fallenyasha
Member

Откуда:
Сообщений: 12
Скорее всего это некое правило оптимизации, т.к. ограничение на 64 значения срабатывает для любых таблиц и полей
27 апр 19, 10:15    [21873251]     Ответить | Цитировать Сообщить модератору
 Re: Использование индексов  [new]
aleks222
Member

Откуда:
Сообщений: 959
fallenyasha
Реальный запрос выглядит так:
SELECT 
    [Extent1].[Id] AS [Id], 
    ...
FROM [dbo].[CompanyUsers] AS [Extent1]
    WHERE 
--  ((''> [Extent1].[ExternalId]) AND (0 = [Extent1].[IsDeleted])) or
    ((N'TEST_USER_EXTERNAL_ID_TEST_USER_NAME_0_927e69fc89b14e23bfd1306a0e111c0e' = [Extent1].[ExternalId]) AND (0 = [Extent1].[IsDeleted])) OR
    ....
    ORDER BY row_number() OVER (ORDER BY [Extent1].[Id] ASC)
    OFFSET 0 ROWS FETCH NEXT 2147483647 ROWS ONLY 


IN я использовал просто для краткости, а вообще его использование никак не влияет на результат.

Закоментарен костыль, включающий seek и ускоряющий работу этой шайтан-машины более чем в 10 раз.


А мужики то и не знают!!!

ЗЫ. Это в цирке надо показывать

ORDER BY row_number() OVER (ORDER BY [Extent1].[Id] ASC)
27 апр 19, 10:23    [21873253]     Ответить | Цитировать Сообщить модератору
 Re: Использование индексов  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
fallenyasha
Скорее всего это некое правило оптимизации, т.к. ограничение на 64 значения срабатывает для любых таблиц и полей

Есть два магических числа 16 и 64, с которыми связана работа оптимизатора с предикатами в SQL Server.

<16 - число для nonSARGable предикатов, которые могут быть продвинуты на уровень сканирования, если 16 и более - условие выносится в отдельный оператор фильтра.
<= 64 - число sargable предикатов, которые могут быть продвинуты на уровень одиночной операции поиска, если более 64, то они выносятся в отдельный оператор Constant Scan (некая внутренняя "таблица" в памяти), который соединяется при помощи NL с вашей таблицей, но от этого Seek не перестает быть Seek-ом.
Это не документировано, или можно сказать, полудокументировано, читайте блог одного из разработчиков оптимизатора Conor Cuningham: https://www.sqlskills.com/blogs/conor/interesting/

Чтобы точно ответить на ваш вопрос, нужен репро-скрипт, либо, хотя бы, структура таблицы и индексов + действительные планы запроса Seek для 64 и Scan для 65, в формате .sqlplan, приложенные к сообщению.

Пока могу предположить, что у вас происходит вот так:
+
use tempdb;
go
set nocount on;
drop table if exists t1;
create table t1(a int identity primary key, b int, c int, d binary(2000) default 0x1);
insert t1(b, c) select top(10000) v1.number, v1.number from master..spt_values v1, master..spt_values v2;
create index ix on t1(b) include(c);
--create index ix on t1(b,c);

select * from t1 --with(forceseek)
where
b=1 and c=1 or b=2 and c=2 or b=3 and c=3 or b=4 and c=4 or b=5 and c=5
or b=6 and c=6 or b=7 and c=7 or b=8 and c=8 or b=9 and c=9 or b=10 and c=10
or b=11 and c=11 or b=12 and c=12 or b=13 and c=13 or b=14 and c=14 or b=15 and c=15
or b=16 and c=16 or b=17 and c=17 or b=18 and c=18 or b=19 and c=19 or b=20 and c=20
or b=21 and c=21 or b=22 and c=22 or b=23 and c=23 or b=24 and c=24 or b=25 and c=25
or b=26 and c=26 or b=27 and c=27 or b=28 and c=28 or b=29 and c=29 or b=30 and c=30
or b=31 and c=31 or b=32 and c=32 or b=33 and c=33 or b=34 and c=34 or b=35 and c=35
or b=36 and c=36 or b=37 and c=37 or b=38 and c=38 or b=39 and c=39 or b=40 and c=40
or b=41 and c=41 or b=42 and c=42 or b=43 and c=43 or b=44 and c=44 or b=45 and c=45
or b=46 and c=46 or b=47 and c=47 or b=48 and c=48 or b=49 and c=49 or b=50 and c=50
or b=51 and c=51 or b=52 and c=52 or b=53 and c=53 or b=54 and c=54 or b=55 and c=55
or b=56 and c=56 or b=57 and c=57 or b=58 and c=85 or b=59 and c=59 or b=60 and c=60
or b=61 and c=61 or b=62 and c=62 or b=63 and c=63 or b=64 and c=64 
-- Comment this and there will be 64 Seeks:
or b=65 and c=65
;


Картинка с другого сайта.

Так получается, потому, что Merge Concatenation тоже имеет свой предел, именно в 64.
Можете попробовать:
+
use tempdb;
go
drop table if exists t2;
create table t2(a int primary key);
go
select * from t2 union all select * from t2 union all select * from t2 union all select * from t2 union all
select * from t2 union all select * from t2 union all select * from t2 union all select * from t2 union all
select * from t2 union all select * from t2 union all select * from t2 union all select * from t2 union all
select * from t2 union all select * from t2 union all select * from t2 union all select * from t2 union all
select * from t2 union all select * from t2 union all select * from t2 union all select * from t2 union all
select * from t2 union all select * from t2 union all select * from t2 union all select * from t2 union all
select * from t2 union all select * from t2 union all select * from t2 union all select * from t2 union all
select * from t2 union all select * from t2 union all select * from t2 union all select * from t2 union all
select * from t2 union all select * from t2 union all select * from t2 union all select * from t2 union all
select * from t2 union all select * from t2 union all select * from t2 union all select * from t2 union all
select * from t2 union all select * from t2 union all select * from t2 union all select * from t2 union all
select * from t2 union all select * from t2 union all select * from t2 union all select * from t2 union all
select * from t2 union all select * from t2 union all select * from t2 union all select * from t2 union all
select * from t2 union all select * from t2 union all select * from t2 union all select * from t2 union all
select * from t2 union all select * from t2 union all select * from t2 union all select * from t2 union all
select * from t2 union all select * from t2 union all select * from t2 union all select * from t2 
--union all select * from t2
option(merge union)

Раскомментируете последнюю строку и получите ошибку:
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

Получается:
1) вариант с одиночным поиском и 65 условий вынести в отдельный ConstantScan нельзя, т.к. туда выносятся только SARGable предикаты, а видимо не все такие.
2) вариант разбирать на несколько одиночных поисков в одном операторе нельзя по той же причине
3) вариант разбирать на несколько условий поиска в каждом из которых проверять и SARGable и nonSARGable (residual), а потом соединять их Merge Join - упирается в ограничение 64 операции Merge Join.

Остается два варианта у оптимизатора:
4) соединять каждый отдельный поиск при помощи Concatenation и уже после этого исключить дубли (это то, что у меня происходит, если выполнить запрос с FORCESEEK, странно, что у вас не сработало, может все-таки, что-то другое у вас, планов то так и нет)
5) выполнить сканирование и фильтрацию.

Если у вас ситуация схожая с моим примером, то попробуйте добавить все поля таблицы условия where, в индекс, это сделает их все предикатами поиска и оптимизатор сможет использовать вариант 2.

Резюмируя, могу сказать, что не забудьте проверить вариант 2, на числа 128, 256, 512, 1024 и т.д. =) Мало ли магических чисел в SQL Server. Если серьезно, то на мой взгляд нужно что-то править в архитектуре или подходе, согласен с alexeyvg. Может сейчас вам поможет этот или другой совет, но в следующий раз что, опять на форум идти? Одна из хороших практик работы с реляционной БД - be relational.
Удачи.
27 апр 19, 12:25    [21873302]     Ответить | Цитировать Сообщить модератору
 Re: Использование индексов  [new]
fallenyasha
Member

Откуда:
Сообщений: 12
SomewhereSomehow, спасибо за развернутый ответ, многое стало понятно.

Про исправление архитектуры или подхода все как обычно, проект достался в наследство, сроки горят, дедлайны прошли еще в прошом веке, так что приходится страдать :)
29 апр 19, 10:42    [21874009]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить