Как оптимизировать выбранный топ N Query

Question

У меня есть очень большая таблица, состоящая из 40 миллионов строк, в базе данных SQL Server 2008.

CREATE TABLE [dbo].[myTable](
    [ID] [bigint] NOT NULL,
    [CONTRACT_NUMBER] [varchar](50) NULL,
    [CUSTOMER_NAME] [varchar](200) NULL,
    [INVOICE_NUMBER] [varchar](50) NULL,
    [AGENCY] [varchar](50) NULL,
    [AMOUNT] [varchar](50) NULL,
    [INVOICE_MONTH] [int] NULL,
    [INVOICE_YEAR] [int] NULL,
    [Unique_ID] [bigint] NULL,
    [bar_code] [varchar](50) NOT NULL,
 CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED 
(
    [ID] ASC,
    [bar_code] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Я пытаюсь оптимизировать производительность для следующего запроса:

SELECT top 35  ID,
            CONTRACT_NR,
            CUSTOMER_NAME,
            INVOICE_NUMBER,
            AMOUNT,
            AGENCY,
            CONTRACT_NUMBER,
            ISNULL([INVOICE_MONTH], 1) as [INVOICE_MONTH],
            ISNULL([INVOICE_YEAR], 1) as [INVOICE_YEAR],
            bar_code, 
            Unique_ID
            from MyTable 
WHERE 
CONTRACT_NUMBER like @CONTRACT_NUMBER and
INVOICE_NUMBER like @INVOICE_NUMBER and 
CUSTOMER_NAME like @CUSTOMER_NAME 
ORDER BY Unique_ID desc

Для этого я строю включенный индекс по столбцам CONTRACT_NUMBER, INVOICE_NUMBER и CUSTOMER_NAME.

CREATE NONCLUSTERED INDEX [ix_search_columns_without_uniqueid] ON [dbo].[MyTable] 
(
    [CONTRACT_NUMBER] ASC,
    [CUSTOMER_NAME] ASC,
    [INVOICE_NUMBER] ASC
)
INCLUDE ( [ID],
[AGENCY],
[AMOUNT],
[INVOICE_MONTH],
[INVOICE_YEAR],
[Unique_ID],
[Contract_nr],
[bar_code]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Тем не менее, выполнение запроса занимает от 3 до 10 секунд. Из плана выполнения запроса я вижу, что операция поиска по индексу занимает около 30% от общей рабочей нагрузки, а затем операция сортировки (Top N), которая потребляет остальные 70%. Любая идея, как я могу оптимизировать этот запрос, предпочтительнее время ответа менее 1 секунды? Примечание. Я также попытался включить столбец [Unique_ID] в столбцы индекса. В этом случае план выполнения запроса выполняет сканирование индекса, но со многими пользователями, запрашивающими базу данных, у меня та же проблема.

Answer 1

Проверьте эту страницу для более подробной информации.

• Обновите статистику с полной проверкой, чтобы оптимизатор работал легче.

UPDATE STATISTICS tablename WITH fullscan GO

• Установите время для статистики и выполните следующий запрос

  SET STATISTICS time ON GO
SELECT num_of_reads, num_of_bytes_read, num_of_writes, num_of_bytes_written FROM sys.dm_io_virtual_file_stats(DB_ID('tempdb'), 1) GO SELECT TOP 100 c1, c2,c3 FROM yourtablename WHERE c1<30000 ORDER BY c2 GO SELECT num_of_reads, num_of_bytes_read, num_of_writes, num_of_bytes_written FROM sys.dm_io_virtual_file_stats(DB_ID('tempdb'), 1) GO

Результат

CPU time = 124 ms,  elapsed time = 91 ms
Before Query execution 
num_of_reads         num_of_bytes_read    num_of_writes     num_of_bytes_written
-------------------- -------------------- -------------------- --------------------
725864               46824931328          793589               51814416384
After Query execution  
num_of_reads         num_of_bytes_read    num_of_writes        num_of_bytes_written
-------------------- -------------------- -------------------- --------------------
725864               46824931328          793589               51814416384

Источник: https://www.mssqltips.com/sqlservertip/2053/trick-to-optimize-top-clause-in-sql-server/

Answer 2

Попробуйте заменить кластеризованный индекс (в настоящее время на два столбца) одним unique_id (при условии, что это действительно уникально). Это поможет вашей сортировке. Затем добавьте второй индекс покрытия - как вы уже пытались - на три столбца, используемых в WHERE, Проверьте, что ваша статистика актуальна. Я чувствую, что колонна bar_code в вашем ПК мешает вашей сортировке работать так быстро, как это возможно.

Содержат ли ваши переменные символы подстановки? Если они есть и они являются ведущими символами подстановки, индекс столбцов WHERE не может использоваться. Если они не подстановочные, попробуйте прямой "="при условии, что чувствительность к регистру не является проблемой.

ОБНОВЛЕНИЕ: так как у вас есть ведущие символы подстановки, вы не сможете воспользоваться индексом на CONTRACT_NUMBER, INVOICE_NUMBER или же CUSTOMER_NAME: как предложил GriGrim, единственной альтернативой здесь является использование полнотекстового поиска (CONTAINS ключевое слово и т. д.).