Оптимизированные для памяти таблицы SQL Server - как я могу обеспечить поиск таблиц?

Question

У меня возникли некоторые проблемы при получении поля с одним столбцом "varchar(5)" для надежного использования поиска по таблице вместо сканирования по таблице. Производственная таблица в этом случае содержит 25 миллионов строк. Как бы впечатляюще ни было сканирование 25 миллионов строк за 35 секунд, запрос должен выполняться намного быстрее.

Вот частичное описание таблицы

CREATE TABLE [dbo].[Summaries_MO]
(
    [SummaryId] [int] IDENTITY(1,1) NOT NULL,
    [zipcode] [char](5) COLLATE Latin1_General_100_BIN2 NOT NULL,
    [Golf] [bit] NULL,
    [Homeowner] [bit] NULL,
    [IncomeCode] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Pets] [bit] NULL,

CONSTRAINT [Summaries_MO_primaryKey] PRIMARY KEY NONCLUSTERED HASH 
(
    [SummaryId]
)WITH ( BUCKET_COUNT = 33554432),
INDEX [ixZIP] NONCLUSTERED 
(
    [zipcode] ASC
)
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )

Как правило, эта таблица доступна с помощью запроса, который включает в себя:

SELECT ...
            FROM SummaryTable

            WHERE ixZIP IN (SELECT ZipCode FROM @ZipCodesForMO)

Этот запрос настаивает на использовании сканирования таблицы. Я пробовал WITH (FORCESEEK), например, но это просто делает запрос неудачным.

Поскольку я исследовал эту проблему, я также попытался:

SELECT * FROM Summaries WHERE ZipCode IN ('xxxxx', 'xxxxx', 'xxxxx')

Когда я выполняю этот запрос с 64 или менее (действительными, действительными) почтовыми индексами, запрос использует поиск по таблице.

Но когда я даю ему 65 или более почтовых индексов, он использует сканирование таблицы.

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

Честно говоря, мне интересно, если тип данных индексированного столбца (Latin1_General_100_BIN2 NOT NULL) является проблемой. Я, вероятно, попробую преобразовать почтовые индексы в целое число, чтобы посмотреть, что произойдет.

Но я предпочел бы знать, что происходит, чем просто пробовать вещи случайным образом.

Answer 1

Минимизировать размер:
Используйте SmallInt для почтового индекса
Рассмотрим TinyInt для IncomeCode

Используйте #temp для ZipCodesForMO с PK
И объединение

SELECT ...
FROM SummaryTable
JOIN #zipCodesForMOWHERE 
     on SummaryTable.zipcode = #zipCodesForMOWHERE.ZipCode 

Answer 2

Это немного долго для комментария.

Сначала попробуйте переписать как join:

SELECT st.*
FROM SummaryTable st JOIN
     @ZipCodesForMO z
     ON st.ixZIP = z.ZipCode;

Причиной этого является то, что JOINу них больше возможностей для оптимизации, чем IN,

Я думаю, что причина, по которой SQL Server предпочитает полное сканирование таблицы, заключается в том, что за каждым поиском по индексу должен следовать поиск по странице, чтобы выбрать остальные столбцы в таблице. Это удваивает объем работы. У вас есть узкие записи, поэтому даже 25 миллионов строк могут уместиться на 30 000 страниц или около того.

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