使用具有附加条件的全文搜索搜索 1300 万条记录

Question

执行具有其他条件的 SQL Server 全文搜索时的性能问题。(SQL Server 2012)

我正在尝试根据搜索过滤器列表(表值参数)过滤数据，这将返回匹配过滤器的所有记录，并且过滤器的单个记录没有任何来自表的记录。

全文搜索索引已在列SNAME的表Names上。

在存储过程中，表类型参数SearchFilter用于传递名称和地址信息的列表。

两个表都有超过 1400 万条记录，当我们在过滤器列表中传递 1000 条唯一记录执行该过程时，返回结果大约需要 7 分钟(1400 条记录)。

过滤条件为：包含(名称)和街道地址，城市，州，邮政编码完全匹配。

是否有任何替代方法可以避免 while 循环作为 SQL ServerCONTAINS函数所需的字符串值或变量？

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 中的搜索条件。因此，不能在应用了 FTS 谓词的数据表和SearchFilter表之间进行直接JOIN。

在其他问题/论坛中找到的当前解决方案是循环浏览过滤器列表，并在变量中CONTAINS提供搜索条件，就像您所做的那样。因此，您不会摆脱此循环。

但是，查看您的查询，我发现许多其他问题可能会影响性能：

1. DISTINCTINSERT 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。地址表，然后在其上构建索引(或将其添加到现有索引)：

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

因此，请修复上述1.和2.然后在RIGHT JOIN中注释AND CONTAINS(en.SNAME,@SearchName)条件并注意执行时间。之后，取消注释CONTAINS条件，并查看添加了多少延迟。这样，您将确定是FTS引擎是延迟的罪魁祸首，还是您的主要查询本身需要改进。

为了能够提供更多建议，我们需要查看您的程序的执行计划。您可以使用以下页面共享查询执行计划：https://www.brentozar.com/pastetheplan/。

呵呵