跨多个表的SQL Server全文查询 - 为什么这么慢？

问题描述

我试图了解我构建的SQL Server 2008全文查询的性能。

以下查询使用全文索引，立即返回正确的结果：

  SELECT 
 O.ID，O.Name 
 FROM 
 dbo.EventOccurrence O 
 WHERE 
 FREETEXT（O.Name，'query'）
  

即，其名称中包含单词'query'的所有EventOccurrence。以下查询使用来自不同表格的全文索引也会立即返回：

  SELECT 
 V.ID，V.Name 
 FROM 
 dbo.Venue V 
 WHERE 
 FREETEXT（V.Name，'query'）
  

即所有场所都以查询一词的名字命名。但是，如果我尝试连接表并同时执行全文查询，则需要12秒返回：

  SELECT 
 O.ID，O.Name 
 FROM 
 dbo.EventOccurrence O 
 INNER JOIN dbo.Event E ON O.EventID = E.ID 
 INNER JOIN dbo.Venue V ON E.VenueID = V.ID 
 WHERE 
 FREETEXT（E.Name，'search'）
 OR FREETEXT（V.Name，'search'）
  

以下是执行计划：

更新：以文本形式显示计划：

  |  - 嵌套循环（Left Semi Join，外部参考：（[E]。[ID]，[V] 。[ID]））
 | --Hash Match（Inner Join，HASH：（[E]。[ID]）=（[O]。[EventID]））
 | |  - 哈希匹配（Inner Join，HASH：（[V]。[ID]）=（[E]。[VenueID]））
 | | |  - 集群索引扫描（OBJECT：（[iScene]。[dbo]。[Venue]。[PK_Venue] AS [V]））
 | | |  - 集群索引扫描（OBJECT：（[iScene]。[dbo]。[Event]。[PK_Event] AS [E]））
 | |  - 集群索引扫描（OBJECT：（[iScene]。[dbo]。[EventOccurrence]。[PK_EventOccurrence] AS [O]））
 |  - 连接
 |  - 表值函数
 |  - 表值函数
  

从我的阅读中，我没有想到它甚至有可能以这种方式在多个表中进行自由文本查询，所以我不确定我是否正确理解这一点。

请注意，如果我删除了WHERE子句从最后一个查询中返回，然后它在一秒钟内返回所有结果，所以它肯定是导致此问题的全文。

有人可以解释（i）为什么这是如此之慢，（ii）如果这甚至被支持/如果我甚至正确理解这一点。

预先感谢您的帮助。

解决方案

尝试使用FREETEXTTABLE重写您的查询并查看是否有帮助。 code> SELECT
O.ID，O.Name
FROM
dbo.EventO ccurrence O
INNER JOIN dbo.Event E ON O.EventID = E.ID
INNER JOIN dbo.Venue V ON E.VenueID = V.ID
LEFT JOIN FREETEXTTABLE（dbo.Event，名称，'搜索'）EFT
ON E.ID = EFT。[KEY]
LEFT JOIN FREETEXTTABLE（dbo.Venue，Name，'search'）VFT
ON V.ID = VFT [KEY]
WHERE EFT [KEY]不是NULL或VFT [KEY]不是NULL

I'm trying to understand the performance of an SQL Server 2008 full-text query I am constructing.

The following query, using a full-text index, returns the correct results immediately:

SELECT
    O.ID, O.Name
FROM
    dbo.EventOccurrence O
WHERE
    FREETEXT(O.Name, 'query')

ie, all EventOccurrences with the word 'query' in their name. And the following query, using a full-text index from a different table, also returns straight away:

SELECT
    V.ID, V.Name
FROM
    dbo.Venue V
WHERE
    FREETEXT(V.Name, 'query')

ie. all Venues with the word 'query' in their name. But if I try to join the tables and do both full-text queries at once, it 12 seconds to return:

SELECT
    O.ID, O.Name
FROM
    dbo.EventOccurrence O
    INNER JOIN dbo.Event E ON O.EventID = E.ID
    INNER JOIN dbo.Venue V ON E.VenueID = V.ID
WHERE
    FREETEXT(E.Name, 'search')
    OR FREETEXT(V.Name, 'search')

Here is the execution plan: http://uploadpad.com/files/query.PNG

UPDATE: the plan in in text form:

  |--Nested Loops(Left Semi Join, OUTER REFERENCES:([E].[ID], [V].[ID]))
       |--Hash Match(Inner Join, HASH:([E].[ID])=([O].[EventID]))
       |    |--Hash Match(Inner Join, HASH:([V].[ID])=([E].[VenueID]))
       |    |    |--Clustered Index Scan(OBJECT:([iScene].[dbo].[Venue].[PK_Venue] AS [V]))
       |    |    |--Clustered Index Scan(OBJECT:([iScene].[dbo].[Event].[PK_Event] AS [E]))
       |    |--Clustered Index Scan(OBJECT:([iScene].[dbo].[EventOccurrence].[PK_EventOccurrence] AS [O]))
       |--Concatenation
            |--Table-valued function
            |--Table-valued function

From my reading, I didn't think it was even possible to make a free text query across multiple tables in this way, so I'm not sure I am understanding this correctly.

Note that if I remove the WHERE clause from this last query then it returns all results within a second, so it's definitely the full-text that is causing the issue here.

Can someone explain (i) why this is so slow and (ii) if this is even supported / if I am even understanding this correctly.

Thanks in advance for your help.

解决方案

Try rewriting your query using FREETEXTTABLE and see if that helps.

SELECT
    O.ID, O.Name
FROM
    dbo.EventOccurrence O
    INNER JOIN dbo.Event E ON O.EventID = E.ID
    INNER JOIN dbo.Venue V ON E.VenueID = V.ID
    LEFT JOIN FREETEXTTABLE(dbo.Event, Name, 'search') EFT 
        ON E.ID = EFT.[KEY]
    LEFT JOIN FREETEXTTABLE(dbo.Venue, Name, 'search') VFT 
        ON V.ID = VFT.[KEY]
WHERE EFT.[KEY] IS NOT NULL OR VFT.[KEY] IS NOT NULL

这篇关于跨多个表的SQL Server全文查询 - 为什么这么慢？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！