SQL Server varbinary clustered index lookups медленный для определенных диапазонов

В моей работе мы в настоящее время имеем таблицу с 50 миллионами строк, которая имеет индекс на двух столбцах Varbinary (16), которые являются ip_start и ip_end.

PRIMARY KEY CLUSTERED ( [ip_end] ASC, [ip_start] ASC ) 

Первые несколько строк в таблице выглядят следующим образом:

 ip_start ip_end id 0x00000000 0x00000000 0 0x00000001 0x000000FF 1 0x00000100 0x00FFFFFF 2 0x01000000 0x010000FF 3 

Запрос, который мы используем для поиска совпадений:

 SELECT TOP 1 id FROM dbo.ip_ranges WITH (NOLOCK) WHERE @lookup <= ip_end AND @lookup >= ip_start 

Когда я просматриваю ip как 0x00000002 он возвращает id 1 мгновенно, но если я ищу диапазон, находящийся между диапазоном 0x000000000000001 для возврата NULL требуется несколько секунд. Разве SQL Server не должен понимать, что индекс varbinary упорядочен и поэтому быстро возвращается, если нет совпадений?

Есть ли лучший способ запросить это с ожиданием того, что некоторые ip будут находиться между диапазонами или лучший способ индексировать таблицу, чтобы промахи не вызывали такого большого попадания?

Разве SQL Server не должен понимать, что индекс varbinary упорядочен и поэтому быстро возвращается, если нет совпадений?

SQL Server понимает, что индекс упорядочен , но он не понимает, что диапазоны не перекрываются. Это условие @lookup >= ip_start истинно для множества диапазонов ip (примерно половина в среднем), и это производительность, которую вы видите для несоответствия. Индекс B-Tree не использует второй ключ для поиска индекса, когда первый ключ имеет неравенство.

К сожалению, стандартные индексы B-Tree не являются оптимальными для этого типа поиска (неравенства по двум измерениям). R-дерево (которое я изначально узнал как RD-дерево) лучше всего подходит. Они используются в основном для пространственных индексов.

Я думаю, что у меня был успех с таким запросом:

 SELECT ir.* FROM (SELECT TOP 1 ir.* FROM dbo.ip_ranges ir WHERE @lookup >= ip_start ORDER BY ip_start ) ir WHERE @lookup <= ir.ip_end ; 

SQL Server должен использовать индекс для подзапроса, быстро находив первую соответствующую строку. Затем вы можете проверить отдельно, если конец диапазона находится в этой строке. Это работает, потому что диапазоны IP-адресов не перекрываются.

  • найти результат из .doc-типа, который хранится в столбце varbinary (max)
  • SQL-сервер конвертирует шестнадцатеричную строку в varbinary
  • Как повысить производительность в таблице SQL Server с полями изображения?
  • Как скопировать VARBINARY, чтобы скопировать его из одной базы данных в другую с помощью скрипта?
  • Производительность SQL Server: 50 столбцов по сравнению с одним двоичным / varbinary
  • Обработка хэшированных паролей, хранящихся в виде varbinary в SQL Server и классическом ASP
  • SQL Server: как скопировать файл (pdf, doc, txt ...), хранящийся в поле varbinary (max) в файл в хранимой процедуре CLR?
  • Есть ли способ превратить результат sys.fn_varbintohexstr в varbinary?
  • Существуют ли проблемы с производительностью при запросе не varbinary (max) полей в таблице, содержащей данные varbinary (max)?
  • SQL Server конвертирует varbinary в строку
  • Загрузите файл в varbinary с помощью SQL Management Studio
  • Давайте будем гением компьютера.