CONTAINSTABLE performance with "1-1" searches

Question

I am using a query with the containstable function, with a string search like this: "1-1" or similar (like "1 1" or "a a") The issue is that the query takes way too long and doesnt bring many results. Instead the same query, but with other search string like "a" which retrieve much more results, take much less time to complete. this is the query:

SELECT     COUNT(d.DocumentID) 
FROM       KnowledgeIndex_Data.dbo.Document d
INNER JOIN CONTAINSTABLE ( KnowledgeIndex_Data.dbo.Document , * , '"1-1"' ) ftt 
        ON ( d.DocumentID = ftt.[Key] )

Note: The stop word list for the full-text index doesnt contains the 1

Do you know what could be going on? Thanks!

Here is the execution plan

here is the creations script for the table Document:

CREATE TABLE dbo.Document
(
      DocumentID int IDENTITY (1, 1) NOT NULL -- Local int for cross reference tables to save 12 bytes per record
    , DocumentGUID uniqueidentifier NOT NULL

--  , DocumentTypeID tinyint NOT NULL
    , DocumentSourceID smallint NOT NULL -- New Document Source identifier
    , SourceDocumentID nvarchar(80) NOT NULL --crb 2011/08/23 changed from nvarchar(40) to support PageCodes -- asw 2010/02/12 renamed to make purpose more clear

    , DocumentStructureID tinyint NOT NULL -- New Document Structure identifier

    , SortOrder nvarchar(450) NOT NULL -- 2010/06/18 bdw- Add the Sort Order column and index to the Document table

    , ResultDisplayContent xml (DOCUMENT DocumentResultDisplayContentSchemaCollection) NOT NULL  -- Required For All DocumentTypes -- jci 2011/02/22 DOCUMENT added -- jci 2010/07/02 xml schema added
    , DetailDisplayContent xml (DOCUMENT DocumentDetailDisplayContentSchemaCollection) NULL -- Only required for some DocumentTypes -- jci 2011/02/22 DOCUMENT added  -- jci 2011/0/31 xml schema added
    , TeaserDisplayContent xml (DOCUMENT DocumentResultDisplayContentSchemaCollection) NULL -- Teaser Result data. Optional, replaced with main ResultDisplayContent if null. -- jci 2011/02/22 DOCUMENT added -- jci 2010/07/02 xml schema added

, TitleQueryContent nvarchar(max) NOT NULL
, QueryContent nvarchar(max) NOT NULL

, CreatedAt datetimeoffset(2) NOT NULL

, CONSTRAINT pcDocument PRIMARY KEY CLUSTERED -- jci 2011/07/01 replaced -- CONSTRAINT pncDocument PRIMARY KEY NONCLUSTERED
    ( DocumentID ) WITH FILLFACTOR = 100
, CONSTRAINT fkDocumentDocumentSourceID FOREIGN KEY
    ( DocumentSourceID )
    REFERENCES dbo.DocumentSource ( DocumentSourceID )
    ON DELETE CASCADE
, CONSTRAINT fkDocumentDocumentStructureID FOREIGN KEY
    ( DocumentStructureID )
    REFERENCES dbo.DocumentStructure ( DocumentStructureID )
    ON DELETE CASCADE
)
GO

and the index:

-- Create Index On Table
CREATE FULLTEXT INDEX ON dbo.Document(QueryContent LANGUAGE N'English' , TitleQueryContent LANGUAGE N'English')
    KEY INDEX pcDocument -- 2011/07/01 replaced --pncDocument
    ON (FILEGROUP SECONDARY)
    WITH STOPLIST = SrsStopWordList -- Use SrsStopWordList
        , CHANGE_TRACKING = OFF , NO POPULATION; -- Update Manually For Performance

GO

What version of SQL Server? What does the plan look like? Is the contains table function on the inside of a nested loops plan so it is evaluated repeatedly? — Martin Smith, Nov 13 '12 at 20:20
Also what is your question. Are you asking about performance or results? — Martin Smith, Nov 14 '12 at 12:45
I am using SQLserver 2008. Yes, the question is about the performance, how could it be so slow. Since it doesnt bring many results Thanks. — Gerardo Gonzalez, Nov 14 '12 at 14:00
What is the definition of `dbo.Document` including indexes? What does the fast plan look like? — Martin Smith, Nov 14 '12 at 14:28
The fast plan searching for "a" or "engine" takes 10 secs vs 1min 40secs that it takes for the "1 1" search, and the execution plan is very much a like — Gerardo Gonzalez, Nov 14 '12 at 14:44

score 0 · Answer 1 · answered Nov 14 '12 at 16:35

Running sys.dm_fts_parser on the search term results in the following -

select  *from sys.dm_fts_parser('"1-1"', 1033, 0 ,0)

display_term    expansion_type  source_term
1                0       1-1
nn1              0       1-1
1                0       1-1
nn1              0       1-1

So the full-text engine ends up searching for 4 different search terms and then combining the results. Could you run a sys.dm_fts_index_keywords on your table with display_term LIKE '1' or 'nn1' and share the results? That might help explain the long run times.

score 0 · Answer 2 · answered Nov 14 '12 at 18:27

I did the that query as follows:

   SELECT count(*) FROM sys.dm_fts_index_keywords(db_id('KnowledgeIndex_Data'),              object_id('dbo.Document'))
   where display_term like '%1-1%'
   GO

it returned 685053 with '%nn1%' ir returned 413578 with '%engine%' it returned 2500

Note that the 1 is not a noise word for my full-text index. Think it could be related to that?

Would it be posible to do a CONTAINSTABLE with part of a table instead of all of it?

the CONATINSTABLE does a search on all the table dbo.Document, and actually after on that query I apply a WHERE to a field of the Document which is making the CONTAINSTABLE do unnecessary work. Thanks!

CONTAINSTABLE performance with "1-1" searches

2 Answers2