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.