Блог

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

Вот моя таблица

 tblGamePlayLog
UserId (PK)
LogId (PK)
...
...
ProviderId
ResellerId
GameId
...
...

-- ProviderId, ResellerId and GameId is indexed (composite index)
 

И у меня есть хранимая процедура, подобная этой

 CREATE PROCEDURE [AS.uspProviderResellerGame_IsDeletable]
(
    @ProviderId INT = -1,    --Use -1 to ignore this field
    @ResellerId INT = -1,    --Use -1 to ignore this field
    @GameId INT = 1,         --Use -1 to ignore this field
    @IsDeletable BIT = 0 OUT
)
AS
BEGIN
    SET @IsDeletable = 1;
    
    ...
    ...
    ...
    ELSE IF (EXISTS(SELECT TOP 1 1 FROM [tblGamePlayLog] WHERE ((@ProviderId = -1) OR ([ProviderId] = @ProviderId)) AND ((@ResellerId = -1) OR ([ResellerId] = @ResellerId)) AND ((@GameId = -1) OR ([GameId] = @GameId)))) SET @IsDeletable = 0;
END;
 

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

Если я удалю проверку -1, скорость значительно улучшится.

 ...
...
...
ELSE IF (EXISTS(SELECT TOP 1 1 FROM [tblGamePlayLog] WHERE ([ProviderId] = @ProviderId) AND ([ResellerId] = @ResellerId) AND ([GameId] = @GameId))) SET @IsDeletable = 0;
 

Я подозреваю, но добавляя проверку -1, SQL не использует проверку индекса. Мой вопрос в том, как разрешить проверку -1 в предложении WHERE, но сохранить проверку индекса.

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

 IF(
    EXISTS(
        SELECT 1
        FROM [tblGamePlayLog] 
        WHERE 
                (@ProviderId = -1 OR [ProviderId] = @ProviderId) 
            AND (@ResellerId = -1 OR [ResellerId] = @ResellerId) 
            AND (@GameId     = -1 OR [GameId]     = @GameId)
    )
    OPTION (RECOMPILE)
)
 

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

 (providerid)
(resellerid)
(gameid)
(providerid, resellerid)
(providerid, gameid)
(gameid, resellerid)
(providerid, resellerid, gameid)
 

Мы можем немного разложить:

 (providerid, resellerid, gameid)
(providerid, gameid)
(gameid, resellerid)
(resellerid)
 

Вы можете обнаружить, что динамический SQL — с правильными индексами — более согласован:

 DECLARE @sql NVARCHAR(max) = '
SELECT @exists = MAX(flag)
FROM (SELECT TOP (1) 1 as flag
      FROM [tblGamePlayLog] 
      WHERE 1=1 @WHERE
     ) gpl
';

DECLARE @WHERE NVARCHAR(MAX) = '';
SET @WHERE = @WHERE   
           (CASE WHEN @ProviderId <> -1 THEN ' AND [ProviderId] = @ProviderId' ELSE '' END)  
           (CASE WHEN @ResellerId <> -1 THEN ' AND [ResellerId] = @ResellerId' ELSE '' END)  
           (CASE WHEN @ProviderId <> -1 THEN ' AND [GameId] = @GameId' ELSE '' END);

SET @sql = REPLACE(@sql, '@WHERE', @WHERE);

DECLARE @exists INT;

exec sp_executesql @sql,
                   N'@ProviderId int, @ResellerId int, @ProviderId int, @exists INT OUTPUT',
                   @exists=@exists OUTPUT;

IF (@exists = 1) BEGIN
     . . . 
END;
 

Построение SQL и накладные расходы на его выполнение должны быть относительно небольшими — вам все равно нужно выполнить запрос. Это гарантирует перекомпиляцию. Вы также должны быть уверены, что у вас есть соответствующие индексы в таблице, что требует большого количества комбинаций (GMB указывает на это в этом ответе).