Медленный запрос с WHERE PK IN (...)

Question

У меня есть очень простая, но большая таблица, как эта:

CREATE TABLE tblMulti (
 pk1 bigint,
 pk2 bigint
)

где PK - состав pk1-pk2 (в этом порядке).

Затем у меня есть большая таблица, как это:

CREATE TABLE tbl (
 ID bigint,
 field1 int,
 ... (other fields)
)

с ID, являющимся единственным PK таблицы.

Мне нужно сделать следующий запрос:

SELECT  ID, COUNT(*) OVER () as TotalCount
FROM    tbl
WHERE   ID IN (
    SELECT  pk1
    FROM    tblMulti 
    WHERE   pk2 = 101)
ORDER BY  field1 DESC, ID DESC
OFFSET @start ROWS FETCH NEXT @count ROWS ONLY;

Проблема заключается в том, что фильтр "pk2=101" приводит к огромному количеству строк (94% всей таблицы tblMulti), и по этой причине SQL Server решает выполнить сканирование индекса вместо поиска по индексу.

Как я могу повысить производительность этого запроса?

большое спасибо

cghersi

Answer 1

Вот что я бы сделал для оптимизации:

1. Создать NON CLUSTERED индекс на pk2 колонка.
2. Создать NON CLUSTERED индекс на field1 колонка.
3. Перестройка / реорганизация индексов и статистики.
4. Используйте JOIN вместо IN.
5. Использовать OPTIMIZE FOR запрос подсказка.

Итак, попробуйте это:

DECLARE @C INT = 101;
SELECT ID, COUNT(*) OVER () as TotalCount
FROM tbl
INNER JOIN tblMulti
    ON tbl.ID = tblMulti.pk1        
WHERE pk2 = @C
ORDER BY  field1 DESC, ID DESC
OFFSET @start ROWS FETCH NEXT @count ROWS ONLY
OPTION (OPTIMIZE FOR (@C = 101));

Answer 2

Причина, по которой это кажется медленным, заключается в том, что у вас нет индекса pk2 поле, но вы используете его, чтобы ограничить свои результаты этим "только один тип" (101) (**).

=> Предполагая, что вам нужна только таблица для запроса, вы можете просто изменить порядок первичного ключа в tblMulti, и это (функционально) будет точно таким же.

=> Предполагая, что вам нужен пакет с указанным порядком полей для чего-то другого, вы можете создать дополнительный уникальный индекс (или ограничение) для таблицы с полями в обратном порядке.

**: Да, я понимаю, что у вас есть pk2 в индексе, но это так же полезно для MSSQL, как и то, что я говорю вам, чтобы найти номер телефона человека, чье имя оканчивается на "mith, Джон", и индексирование телефонной книги по фамилии также не поможет вам в этом.

Кроме того, так как вы знаете, что комбинация pk1, pk2 уникален, вы можете использовать JOIN вместо WHERE EXISTS()

SELECT  t1.ID, 
        COUNT(*) OVER () as TotalCount
FROM    tbl t1
JOIN    tblMulti tm
  ON    tm.pk1 = t1.ID
 AND    tm.pk2 = 101
ORDER BY t1.field1 DESC, 
         t1.ID DESC
OFFSET @start ROWS FETCH NEXT @count ROWS ONLY;

Тем не менее, я немного удивлен, что это работает. Вы делаете ORDER BY на поле, которое не является частью вывода. Я почему-то догадываюсь, что MSSQL оставляет это и просто использует t1.ID?!?

Не будет ли следующий запрос более простым в использовании?

SELECT  t1.ID, 
        COUNT(*) as TotalCount
FROM    tbl t1
JOIN    tblMulti tm
  ON    tm.pk1 = t1.ID
 AND    tm.pk2 = 101
GROUP BY t1.ID
ORDER BY t1.ID DESC
OFFSET @start ROWS FETCH NEXT @count ROWS ONLY;

Answer 3

Попробуйте что-то вроде этого....

SELECT  t1.ID, COUNT(*) OVER () as TotalCount
FROM    tbl t1
WHERE EXISTS (SELECT  1
              FROM    tblMulti 
              WHERE   t1.ID = pk1
                AND   pk2 = 101)
ORDER BY  t1.field1 DESC, t1.ID DESC
OFFSET @start ROWS FETCH NEXT @count ROWS ONLY;