Производительность пользовательских типов таблиц в SQL Server

Мы использовали Пользовательские типы таблиц, чтобы передать список целых чисел в наши хранимые процедуры.

Затем мы используем их для присоединения к другим таблицам в наших хранимых запросах proc.

Например:

CREATE PROCEDURE [dbo].[sp_Name]
(
    @Ids [dbo].[OurTableType] READONLY  
)
AS
    SET Nocount ON

    SELECT
        *
    FROM
        SOMETABLE
        INNER JOIN @Ids [OurTableType] ON [OurTableType].Id = SOMETABLE.Id

Мы видели очень низкую производительность при использовании больших наборов данных.

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

Например:

CREATE PROCEDURE [dbo].[sp_Name]
(
    @Ids [dbo].[OurTableType] READONLY  
)
AS
    SET Nocount ON
    CREATE TABLE #TempTable(Id INT)
    INSERT INTO #TempTable
    SELECT Id from @Ids

    SELECT
        *
    FROM
        SOMETABLE
        INNER JOIN #TempTable ON #TempTable.Id = SOMETABLE.Id

    DROP TABLE #TempTable

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

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

Ответ 1

Эта тема обсуждалась раньше. Основная причина низкой производительности JOIN состоит в том, что параметр с табличным значением (TVP) является табличной переменной. Переменные таблицы не хранят статистику и, по-видимому, оптимизатору запросов содержат только 1 строку. Следовательно, они просто в состоянии сделать что-то вроде INSERT INTO Table (column_list) SELECT column_list FROM @TVP; но не ПРИСОЕДИНЯЙТЕСЬ.

Есть несколько вещей, чтобы попытаться обойти это:

Дамп все в локальную временную таблицу (вы уже делаете это). Техническим недостатком здесь является то, что вы дублируете данные, передаваемые в TVP в базе данных tempdb (где и TVP, и временная таблица хранят свои данные).
Может быть, попробуйте определить пользовательский тип таблицы, чтобы иметь кластерный первичный ключ. Вы можете сделать это встроенным в поле [Id]:
```
[ID] INT NOT NULL PRIMARY KEY
```
Не уверен, насколько это помогает производительности, но стоит попробовать.
Вы можете попробовать добавить OPTION (RECOMPILE) к запросу. Это способ получить Оптимизатор запросов, чтобы увидеть, сколько строк в табличной переменной, чтобы иметь правильные оценки.
```
SELECT column_list
FROM   SOMETABLE
INNER JOIN @Ids [OurTableType]
        ON [OurTableType].Id = SOMETABLE.Id
OPTION (RECOMPILE);
```
Недостатком является то, что у вас есть RECOMPILE который занимает дополнительное время каждый раз, когда вызывается этот процесс. Но это может быть общая чистая прибыль.
Начиная с SQL Server 2014, вы можете воспользоваться OLTP в памяти и указать WITH (MEMORY_OPTIMIZED = ON) для пользовательского типа таблицы. Пожалуйста, смотрите Сценарий: переменная таблицы может быть MEMORY_OPTIMIZED = ON для подробностей. Я слышал, что это определенно помогает. К сожалению, в SQL Server 2014 и SQL Server 2016 RTM эта функция доступна только в 64-разрядной версии Enterprise Edition. Но, начиная с SQL Server 2016 с пакетом обновления 1 (SP1), эта функция была доступна для всех выпусков (возможное исключение - SQL Server Express LocalDB).
SQL Server 2019 представляет " отложенную компиляцию табличных переменных ":

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

Пожалуйста, смотрите связанную документацию для деталей.

PS. Не делайте SELECT *. Всегда указывайте список столбцов. Если не делать что-то вроде IF EXIST(SELECT * FROM)...