Migliorare le prestazioni delle query full-text in SQL Server

Questo articolo fornisce un metodo per migliorare le prestazioni delle query che usano predicati full-text in SQL Server.

Versione originale del prodotto: SQL Server 2008 Developer, SQL Server 2008 Enterprise, SQL Server 2008 R2 Datacenter, SQL Server 2008 R2 Developer, SQL Server 2008 R2 Enterprise, SQL Server 2008 R2 Standard, SQL Server 2012 Developer, SQL Server 2012 Standard, SQL Server 2012 Web, SQL Server 2012 Enterprise
Numero KB originale: 2549443

Riepilogo

Questo articolo descrive un metodo per migliorare le prestazioni delle query microsoft SQL Server che usano predicati di ricerca full-text (ad CONTAINS esempio e CONTAINSTABLE) e che filtrano anche i dati. Ad esempio, questo metodo migliora le prestazioni della query seguente:

SELECT * FROM dbo.ftTest WHERE CONTAINS(TextData, '"keyword"') AND CDate > @date

Questo metodo consente di progettare la query, lo schema della tabella e l'indice full-text in modo che il motore di ricerca full-text filtri i risultati prima che vengano inviati al motore relazionale. Pertanto, il motore relazionale non deve filtrare un set di dati di grandi dimensioni.

Ulteriori informazioni

Quando si crea una query di ricerca full-text, il fattore principale che influisce sulle prestazioni della query è la quantità di dati che il motore di ricerca full-text deve elaborare prima che i dati rimanenti vengano inviati al motore relazionale. In SQL Server è possibile migliorare le prestazioni della query filtrando le righe in anticipo per ridurre il numero di righe che devono essere elaborate in un secondo momento.

Nelle versioni di SQL Server rilasciate prima di SQL Server 2008, il motore di ricerca full-text restituisce tutte le righe che corrispondono a un termine di ricerca e quindi il motore relazionale applica eventuali filtri. Miglioramenti a questo comportamento sono stati apportati nel SQL Server 2008, in SQL Server 2008 R2 e in SQL Server 2012. Tuttavia, è difficile usare questi miglioramenti perché gli indici di ricerca full-text sono organizzati in modo diverso dagli indici di database. Inoltre, il motore di ricerca full-text e il motore relazionale funzionano in modo diverso. Pertanto, il metodo descritto in questo articolo usa la funzione Table-Valued (TVF) per filtrare le righe in anticipo e ridurre il numero di righe che devono essere elaborate in un secondo momento.

Ad esempio, il piano di query seguente restituisce 131051 righe che corrispondono a una CONTAINS stringa di ricerca. Inoltre, un operatore join nel piano esegue filtri aggiuntivi usando una ricerca di indice.

Rows StmtText
-------------------- ----------------------------------------------------------------------------------------
1167 SELECT CDate, ID FROM dbo.fttest WHERE contains (c2, '"create"') AND CDate> '08/05/2019'

1167 |--Merge Join(Left Semi Join, MERGE:([FTSdb].[dbo].[fttest].[ID])=(FulltextMatch.[docid]), RESIDUA
5858 |--Sort(ORDER BY:([FTSdb].[dbo].[fttest].[ID] ASC))
5858 | |--Clustered Index Seek(OBJECT:([FTSdb].[dbo].[fttest].[clidx1]), SEEK:([FTSdb].[
131051 |--Table-valued function

Tuttavia, se la query include la colonna chiave di indice univoca full-text come predicato, il motore di ricerca full-text può usare il predicato per filtrare i risultati a livello full-text. In questo caso, il file TVF restituisce una quantità di dati molto inferiore prima che sia necessario applicare filtri aggiuntivi. Ad esempio, la query seguente specifica cinque valori che devono corrispondere alla condizione c2 e il TVF restituisce solo i risultati che corrispondono ai cinque valori:

Rows StmtText
-------------------------------------------------------------------------------------------------------------------------------------------
5 SELECT CDate, ID FROM dbo.fttest WHERE contains (c2, '"create"') AND CDate > '08/05/2019' AND ID IN ( 654051, 644051, 649106, 465, 105)

5 |--Nested Loops(Left Semi Join, OUTER REFERENCES:([FTSdb].[dbo].[fttest].[ID]))
5 |--Index Seek(OBJECT:([FTSdb].[dbo].[fttest].[idx1]), SEEK:([FTSdb].[dbo].[fttest].[ID]=(105) OR ...
5 |--Table-valued function

La possibilità del motore di ricerca full-text di eseguire il push verso il basso dei valori usati dalla chiave di indice univoca è alla base del metodo seguente.

Se un predicato contiene una DateTime colonna del tipo di dati, è possibile includere informazioni sulla data nella colonna chiave di indice univoca in modo che vengano restituite solo le righe che corrispondono a questo predicato. A tale scopo, è necessario incorporare logicamente le informazioni sulla data nella colonna chiave. Tuttavia, potrebbe anche essere necessario modificare il tipo di dati della colonna chiave e le applicazioni che usano la query.

Per implementare il metodo , modificare il tipo di dati della chiave ID univoca full-text in BIGINT. I primi 4 byte dell'ID chiave acquisiscono i valori di anno, mese e data dalla colonna date e gli ultimi 4 byte rimangono invariati. Ad esempio, il primo byte dell'ID chiave potrebbe fare riferimento all'anno, il byte successivo potrebbe fare riferimento al mese e gli ultimi 2 byte potrebbero fare riferimento alla data. L'applicazione deve supportare questa modifica del tipo di dati.

Quindi, convertire un predicato di intervallo in un predicato sull'ID chiave. Ad esempio, il x < CDate < y predicato di intervallo può essere convertito nel (x*2^32 < ID < y*2^32) predicato. Poiché il predicato tradotto è un predicato sulla chiave full-text, il predicato verrà inserito nel file STVF (Streaming Table-Valued Functions) full-text. Questo comportamento esegue in modo efficace ricerche all'interno dell'intervallo di date.