Поиск 13 миллионов записей с использованием полнотекстового поиска с дополнительными условиями

Question

Проблема производительности при выполнении полнотекстового поиска SQL Server с дополнительными условиями. (SQL Server 2012)

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

Индекс полнотекстового поиска уже на столе Names для столбца SNAME,

В хранимой процедуре параметр типа таблицы SearchFilter используется для передачи списка информации об имени и адресе.

В обеих таблицах содержится более 14 миллионов записей. Когда мы выполняем процедуру с 1000 уникальными записями, переданными в списке фильтров, для возврата результата требуется около 7 минут (1400 записей).

Критерии фильтра: содержит (имя) и уличный адрес, город, штат, почтовый индекс.

Есть ли альтернатива, чтобы избежать цикла while как SQL Server CONTAINS функция требует строковое значение или переменную?

CREATE TABLE [dbo].[Names]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [UIN] [varchar](9) NULL,
    [SNAME] [varchar](500) NULL,
    CONSTRAINT [PK_Names] 
        PRIMARY KEY CLUSTERED ([ID] ASC)
)

CREATE TABLE [dbo].[ADDRESSES]
(
    [UIN] [varchar](9) NULL,
    [STREET1] [varchar](100) NULL,
    [STREET2] [varchar](50) NULL,
    [CITY] [varchar](30) NULL,
    [STATE] [varchar](2) NULL,
    [ZIP] [varchar](10) NULL    
) ON [PRIMARY]

CREATE TYPE [dbo].[SearchFilter] AS TABLE
(
    [UIN] [varchar](40) NULL,
    [SNAME] [varchar](max) NULL,
    [StreetAddress] [varchar](max) NULL,
    [City] [varchar](max) NULL,
    [State] [varchar](50) NULL,
    [Zip] [varchar](20) NULL
)

-- Stored procedure logic
DECLARE @filterList AS [dbo].[SearchFilter]

DECLARE @NoOfRows INT, @counter INT = 0

SET @NoOfRows = (SELECT COUNT(1) FROM @filterList)

DECLARE @result TABLE (UIN varchar(40), 
                       NAME varchar(500), 
                       StreetAddress varchar(1000), 
                       Zipcode varchar(20),
                       State varchar(20),
                       City varchar(1000),
                       IsRecordFound varchar(50)
                      );

WHILE (@NoOfRows > @counter)
BEGIN
    DECLARE @SearchName VARCHAR(4000)

    SET @SearchName = (SELECT '"'+SNAME+'"' FROM @filterList ORDER BY SNAME OFFSET @counter ROWS FETCH NEXT 1 ROWS ONLY)  

    --Start: Process to Select Records
    ;WITH Filter_CTE AS
    (
        SELECT 
            SNAME, StreetAddress, City, State, ZipCode 
        FROM
            @filterList 
        ORDER BY 
            SNAME 
            OFFSET @counter ROWS FETCH NEXT 1 ROWS ONLY 
    )
    INSERT INTO @result (UIN, NAME, STREETADDRESS, CITY, STATE, ZIPCODE, PHONE, IsRecordFound)
        SELECT DISTINCT 
            en.UIN, ISNULL(en.SNAME, Filter_CTE.SNAME),
            Filter_CTE.StreetAddress, Filter_CTE.ZipCode,
            Filter_CTE.state, Filter_CTE.City,
            IIF(en.UIN IS NULL, 'Not Found', 'Found') AS IsRecordFound 
        FROM 
            dbo.Names en 
        INNER JOIN 
            dbo.ADDRESSES ea ON en.UIN = ea.UIN
        RIGHT JOIN 
            Filter_CTE ON ea.ZIP = Filter_CTE.Zip 
                       AND ea.STATE = Filter_CTE.State 
                       AND ea.CITY = Filter_CTE.City 
                       AND (ISNULL(ea.STREET1, '') + ' ' + ISNULL(ea.STREET2, '')) = Filter_CTE.StreetAddress
                       AND CONTAINS(en.SNAME,@SearchName)
            --END

    SET @counter += 1
END 

SELECT 
    UIN, NAME, STREETADDRESS, CITY, STATE, ZIPCODE, PHONE 
FROM 
    @result 

Answer 1

В настоящее время невозможно использовать имена столбцов в качестве условия поиска в CONTAINS или CONTAINSTABLE. Таким образом, вы не можете сделать прямой JOIN между таблицей данных и SearchFilter таблица с примененными предикатами FTS.

Текущее решение, найденное в других вопросах / форумах, состоит в том, чтобы пройтись по списку фильтров и кормить CONTAINS с условием поиска в переменной, так же, как вы делаете. Таким образом, вы не избавитесь от этой петли.

Однако, глядя на ваш запрос, я вижу ряд других проблем, которые могут повлиять на производительность:

1. DISTINCT пункт в INSERT INTO @result ... SELECT DISTINCT ..., Это на уровне, где вы JOIN в таблицы с миллионами записей. Хотя я понимаю, что конечный результат может содержать только несколько тысяч строк, лучше переместить DISTINCT к этой строке:

   SELECT DISTINCT
       UIN, NAME, STREETADDRESS, CITY, STATE, ZIPCODE, PHONE 
   FROM 
       @result 
   

2. Это условие AND (ISNULL(ea.STREET1, '') + ' ' + ISNULL(ea.STREET2, '')) = Filter_CTE.StreetAddress это конечно не измученный. Вы используете конкатенацию и функцию (ISNULL()) который не позволяет SQL Server использовать существующие индексы dbo.ADDRESSES ea Таблица. Проверьте этот вопрос: что делает оператор SQL саргимным? чтобы увидеть, как построить JOIN / WHERE условия таким образом, что позволит использовать индексы. В этом конкретном случае лучше добавить вычисляемый столбец в таблицу dbo.Addresses, а затем построить индекс по нему (или добавить его в существующий индекс):

   CREATE TABLE [dbo].[ADDRESSES]
   (
       ...
       STREET as (ISNULL(ea.STREET1, '') + ' ' + ISNULL(ea.STREET2, '')),
       ...
   )
   

Так что исправьте вышеприведенные 1. и 2. затем прокомментируйте AND CONTAINS(en.SNAME,@SearchName) состояние в RIGHT JOIN и обратите внимание на время выполнения. После этого раскомментируйте CONTAINS условие и посмотреть, сколько задержки было добавлено. Таким образом, вы точно будете знать, виноват ли в задержке механизм FTS или ваш основной запрос нуждается в улучшении.

Чтобы иметь возможность посоветовать больше, нам нужно увидеть планы выполнения вашей процедуры. Вы можете поделиться своим планом выполнения запросов, используя эту страницу: https://www.brentozar.com/pastetheplan/.

НТН