Почему удаление столбца из индекса улучшило производительность?

Предоставление запроса и планов запроса (с помощью «Вставки плана») действительно поможет нам вам здесь.

Насколько велики списки IN? В принципе, SQL Server, безусловно, должен иметь возможность преобразовать это в N*M поиск по (A,B), а не сканировать его.

@MartinSmith, ты совершенно прав. N было примерно 90 000, а M 2 200, что дало 2 миллиарда поисков.

Добавлен @ThomA Paln.

Ах, ну, 2 миллиарда поисков вполне могут оказаться дороже, чем просто выполнение 90 000 поисков по A и остаточному предикату по B для оценки всех строк, возвращаемых для части A. Каждый поиск должен начинаться с корня и перемещаться по глубине индекса.

@MartinSmith именно этого я и ожидал. Однако по какой-то причине SQL-сервер отказался от этого.

явно задокументировано, что иметь такой большой список [в IN] — плохая идея, @AndriyK . Используйте параметр типа таблицы (индексированный) в EXISTS, если вам нужно иметь столько

Честно говоря, я удивлен, что вы не столкнулись с ошибкой 8623 со списком из 90 000 значений в IN.

@ThomA - План выполнения показывает, что на самом деле они из JSON, а не длинный список

Но SQL Server не будет знать, сколько значений на самом деле содержит JSON, поэтому предполагаемое количество выполнений операторов — мусор.

Тогда это противоречит запросу, который у них есть в вопросе, @MartinSmith. Хотя мне интересно, как будет работать массив JSON по сравнению с (индексированным) параметром типа таблицы.

С параметром типа таблицы он может получить лучшие оценки кратности, если используется OPTION (RECOMPILE) или с отложенной компиляцией, поэтому не будет просто предполагать 50 для обоих списков.

@MartinSmith, спасибо, мне было интересно, почему оценки такие неправильные. Что объясняет его.

@ThomA EF Core легко создает для меня эти списки JSON, и до сих пор все работало нормально. Параметры табличного типа могут обеспечить более высокую производительность, однако с ними не так легко работать со стороны кода, поэтому нам придется оценить, стоит ли выигрыш затраченных средств.

@ThomA OPENJSON получает оценку по умолчанию в 100 строк, и значения не считаются уникальными, поэтому он получит дополнительную сортировку/поток или хеш-агрегат.

TVP не так уж и сложно использовать в EF Core, см., например, stackoverflow.com/a/76831915/14868997. Если вы покажете свой существующий код, мы сможем показать, как его улучшить. В любом случае, для такого запроса сложно/неэффективно использовать один многостолбцовый индекс, поскольку предикаты A и B являются OR. Лучше всего использовать два отдельных индекса и использовать объединение индексов (в основном, двойной запрос и INTERSECT).

@Charlieface, спасибо, по твоей ссылке достаточно информации, с которой я могу работать. Мне нравится идея пересечения индексов, возможно, это правильно.

Лично я не вижу никакого преимущества в использовании пересечения индексов перед простым поиском A и выполнением остаточного предиката для B из покрывающего индекса. При пересечении индексов ему по-прежнему приходится выполнять все поиски A, а теперь необходимо объединить результат со всеми поисками B. Это не похоже на то, что объединенные наборы результатов многих поисков на A и многих поисков на B будут упорядочены каким-либо образом, который облегчит их объединение посредством соединения слиянием. Если он собирается выполнить хеш-соединение, он может с таким же успехом сделать это на основе значения B, полученного из индекса покрытия.

@MartinSmith, ваши комментарии были точны, на самом деле все сводилось к строкам JSON и неспособности анализатора предсказать количество элементов в них. Если вы можете составить быстрый ответ, я с радостью отмечу его как принятый.

Я видел, что за этот ответ проголосовали против. Если бы я узнал, в чем причина, что нужно улучшить, то с радостью согласился бы.

Извините, это не я минусовал. Я думаю ты прав. Пытаясь воспроизвести эту проблему, я решил, что на самом деле в экземпляре-нарушителе произошли некоторые изменения, а именно добавлен новый пакет данных, отсутствующий в других копиях. Это, должно быть, привело к фрагментации индекса и вызвало наблюдаемое поведение.

Проблема не имеет ничего общего с фрагментацией. Это просто плохой план реализации. Показанный план выполнения выполняет поиск 206,719,755 в таблице, содержащей только 113,394,000 строк. Это просто очень неэффективно. По его оценкам, он выполнит только 353 таких поиска, поскольку понятия не имеет, с каким JSON ему придется столкнуться, и не проводит никакого анализа этого при составлении плана.