SQL Server Full text Function CONTAINSTABLE - to reduce run time

94 views Asked by At

I have the below query which uses a CONTAINSTABLE Full text search function.

DECLARE @Brands table (BrandId int,
                       Brand nvarchar(255),
                       FTSRank int,
                       [Count] int,
                       CalculatedRank int);
DECLARE @CommodityID int = 121320,
        @CommodityIDParentL1 int = 119892,
        @CommodityIDParentL2 int = NULL,
        @DataSourceID int = 36,
        @SearchWords nvarchar(4000) = N'ISABOUT("1RABBIT BALLOONS" WEIGHT(0.993268), "1*RABBIT*" WEIGHT(0.662179), "BALLOONS" WEIGHT(0.648935), "BALLOONS EACH" WEIGHT(0.441452), "EACH" WEIGHT(0.110363))',
        @ProductRecodeId int = 15661703;


INSERT INTO @Brands (BrandId,
                     Brand,
                     FTSRank,
                     [Count],
                     CalculatedRank)
SELECT TOP 100
       BrandId,
       Brand,
       FTSRank,
       [Count],
       CalculatedRank
FROM (SELECT x.BrandId,
             x.Brand,
             x.FTSRank,
             x.[Count],
             CONVERT(real, [FTSRank]) * (CASE WHEN Count2 < 1 THEN 1.0 ELSE Count2 END) * (CASE WHEN c1.CommodityID = @CommodityID THEN 1.3 ELSE 1 END) -- InSameCommodity,
             * (CASE WHEN c2.CommodityID = @CommodityIDParentL1 THEN 1.2 ELSE 1 END) -- InSameCommodityP1,
             * (CASE WHEN c3.CommodityID = @CommodityIDParentL2 THEN 1.1 ELSE 1 END) -- InSameCommodityP2
             * (CASE WHEN x.DataSourceID = @DataSourceID THEN 1.5 ELSE 1 END) * (1 + ISNULL(EAN.Percentage, 0)) AS CalculatedRank
      FROM (SELECT b.BrandId,
                   b.Brand,
                   CASE b.DataSourceID WHEN @DataSourceID THEN b.CommodityID ELSE -1 END AS CommodityID,
                   MAX(fts.[RANK]) AS [FTSRank],
                   CONVERT(real, COUNT(*)) AS [Count],
                   (-SQUARE(SQRT(COUNT(*)) - 4) + 40) / 31.0 AS [Count2],
                   CASE b.DataSourceID WHEN @DataSourceID THEN @DataSourceID ELSE -1 END AS DataSourceID
            FROM CONTAINSTABLE (dbo.tbl_BD_FTS_Brand, (Brand, ProductDescription, ProductRecodeDescription), @SearchWords, 1000) fts
                 INNER JOIN dbo.tbl_BD_FTS_Brand b ON b.ProductRecodeID = fts.[KEY]
            GROUP BY b.BrandId,
                     b.Brand,
                     CASE b.DataSourceID WHEN @DataSourceID THEN b.CommodityID ELSE -1 END,
                     CASE b.DataSourceID WHEN @DataSourceID THEN @DataSourceID ELSE -1 END) x
           LEFT JOIN [Beta].dbo.tbl_C c1 WITH (READUNCOMMITTED) ON c1.CommodityID = x.CommodityID
           LEFT JOIN [Beta].dbo.tbl_C c2 WITH (READUNCOMMITTED) ON c2.CommodityID = c1.ParentCommodityId
           LEFT JOIN [Beta].dbo.tbl_C c3 WITH (READUNCOMMITTED) ON c3.CommodityID = c2.ParentCommodityId
           LEFT JOIN dbo.tbl_BD_PLB plb ON plb.Brand = x.Brand
           LEFT JOIN dbo.fnt_BD_GetEAN(@ProductRecodeId, 0.7, 10) EAN ON EAN.BrandId = x.BrandId
      WHERE LEN(x.Brand) > 1
        AND ISNULL(plb.DataSourceID, @DataSourceID) = @DataSourceID) y
ORDER BY ROW_NUMBER() OVER (PARTITION BY Brand ORDER BY CalculatedRank DESC),
         y.CalculatedRank DESC;

Execution plan https://www.brentozar.com/pastetheplan/?id=Hk7K3CT-q


Even the CONTAINSTABLE function part only is taking 3 seconds minimum for a single value

DECLARE @SearchWords nvarchar(4000)='ISABOUT("1RABBIT BALLOONS" WEIGHT(0.993268), "1*RABBIT*" WEIGHT(0.662179), "BALLOONS" WEIGHT(0.648935), "BALLOONS EACH" WEIGHT(0.441452), "EACH" WEIGHT(0.110363))'
SELECT [KEY], [RANK] 
FROM CONTAINSTABLE(dbo.tbl_BD_FTS_Brand, (Brand, ProductDescription, ProductRecodeDescription), @SearchWords, 1000)

Execution plan - https://www.brentozar.com/pastetheplan/?id=BJrjnJCWq


Details about Full text search enabled table dbo.tbl_BD_FTS_Brand

sys.fulltext_index_fragments details of the table are as below

status data_size row_count
4 466001693 1408963

This table has 6 million records and is truncated and loaded daily.
After load below statement executes

ALTER FULLTEXT INDEX ON indx_table1 START FULL POPULATION

Full Text index is created on 3 columns - Brand, ProductDescription, ProductRecodeDescription.
There is no other maintenance seems to be done for the Full text index table.

CREATE TABLE dbo.tbl_BD_FTS_Brand ([ProductRecodeID] [int] NOT NULL,
                                   [Brand] [nvarchar](255) COLLATE Latin1_General_CI_AS NOT NULL,
                                   [BrandId] [int] NOT NULL,
                                   [DataSourceId] [int] NOT NULL,
                                   [CommodityId] [int] NULL,
                                   [ProductDescription] [nvarchar](255) COLLATE Latin1_General_CI_AS NOT NULL,
                                   [ProductRecodeDescription] [nvarchar](255) COLLATE Latin1_General_CI_AS NOT NULL,
                                   [ProductRecodeDescription_WithoutSizeDescription] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
                                   [LenOfProductRecodeDescription_WithoutSizeDescription] [int] NULL) ON [PRIMARY];
GO
ALTER TABLE dbo.tbl_BD_FTS_Brand
ADD CONSTRAINT [PK_tbl_BD_FTS_Brand]
    PRIMARY KEY CLUSTERED ([ProductRecodeID]) ON [PRIMARY];
GO
CREATE NONCLUSTERED INDEX [IX_tbl_BD_FTS_Brand_DSId_CommodityId_Len]
    ON dbo.tbl_BD_FTS_Brand (
[DataSourceId],
[CommodityId],
[LenOfProductRecodeDescription_WithoutSizeDescription])
    ON [PRIMARY];
GO
CREATE FULLTEXT INDEX
ON dbo.tbl_BD_FTS_Brand
KEY INDEX [PK_tbl_BD_FTS_Brand]
ON [FTSBD]
WITH CHANGE_TRACKING OFF,
     STOPLIST OFF;
GO
ALTER FULLTEXT INDEX
ON dbo.tbl_BD_FTS_Brand
ADD (Brand LANGUAGE 1033);
GO
ALTER FULLTEXT INDEX
ON dbo.tbl_BD_FTS_Brand
ADD (ProductDescription LANGUAGE 1033);
GO
ALTER FULLTEXT INDEX
ON dbo.tbl_BD_FTS_Brand
ADD (ProductRecodeDescription LANGUAGE 1033);
GO

The lag in the CONTAINSTABLE function is causing the process that uses this query run for long hours. I do not have much background on working with Full text Search in SQL server.
Can you help if you can see suggest any solutions that can reduce the run time for this.

0

There are 0 answers