Introduction
Recently we've got a complaint from one of our users that one of the search pages in their application became very slow. We quickly figured out that the problem is in the SQL query, where he used simple 'like’ operators instead of the full text index and ‘contains’ operator.
SELECT
DISTINCT
COUNT
(
DISTINCT
(ClientInformation.ID))
AS
[cID]
FROM
[dbo].[ClientInformation] (nolock)
INNER
JOIN
[dbo].[Client] (nolock)
ON
[dbo].[ClientInformation].[ID]=[dbo].[Client].[ClientInformationID]
LEFT
JOIN
[dbo].[ClosingDetails] (nolock)
ON
[dbo].[ClientInformation].[ID]=[dbo].[ClosingDetails].[ClientInformationID]
LEFT
JOIN
[dbo].[DealClient] (nolock)
ON
[dbo].[ClosingDetails].[ID]=[dbo].[DealClient].[ClosingDetailsId]
WHERE
[dbo].[ClientInformation].[Note]
like
@Keyword
OR
[dbo].[ClientInformation].[MoveInNote]
like
@Keyword
OR
[dbo].[ClientInformation].[OtherInfo]
like
@Keyword
OR
[dbo].[ClientInformation].[PriceNote]
like
@Keyword
OR
[dbo].[ClientInformation].[ApartmentNote]
like
@Keyword
OR
[dbo].[ClosingDetails].[Note]
like
@Keyword
OR
[dbo].[DealClient].[Employer]
like
@Keyword
Full text index is also slow?!
To solve this problem, we've implemented a full text index on all necessary fields in the database, and wrote a new query using the power of the 'contains' operator.
SELECT
DISTINCT
COUNT
(
DISTINCT
(ClientInformation.ID))
AS
[cID]
FROM
[dbo].[ClientInformation] (nolock)
INNER
JOIN
[dbo].[Client] (nolock)
ON
[dbo].[ClientInformation].[ID]=[dbo].[Client].[ClientInformationID]
LEFT
JOIN
[dbo].[ClosingDetails] (nolock)
ON
[dbo].[ClientInformation].[ID]=[dbo].[ClosingDetails].[ClientInformationID]
LEFT
JOIN
[dbo].[DealClient] (nolock)
ON
[dbo].[ClosingDetails].[ID]=[dbo].[DealClient].[ClosingDetailsId]
WHERE
Contains
([dbo].[ClientInformation].[Note], @Keyword)
OR
Contains
([dbo].[ClientInformation].[MoveInNote], @Keyword)
OR
Contains
([dbo].[ClientInformation].[OtherInfo], @Keyword)
OR
Contains
([dbo].[ClientInformation].[PriceNote], @Keyword)
OR
Contains
([dbo].[ClientInformation].[ApartmentNote], @Keyword)
OR
Contains
([dbo].[ClosingDetails].[Note], @Keyword)
OR
Contains
([dbo].[DealClient].[Employer], @Keyword)
Problem solved? Unfortunately, the new query was as slow as the original one the utilizes the 'like' operator. We took a closer look and figured out that the main culprit lies in the combination of the ‘left join’ and ‘or’ operators. When we removed them from the query, it became very fast.
SELECT
DISTINCT
COUNT
(
DISTINCT
(ClientInformation.ID))
AS
[cID]
FROM
[dbo].[ClientInformation] (nolock)
INNER
JOIN
[dbo].[Client] (nolock)
ON
[dbo].[ClientInformation].[ID]=[dbo].[Client].[ClientInformationID]
WHERE
Contains
([dbo].[ClientInformation].[Note], @Keyword)
OR
Contains
([dbo].[ClientInformation].[MoveInNote], @Keyword)
OR
Contains
([dbo].[ClientInformation].[OtherInfo], @Keyword)
OR
Contains
([dbo].[ClientInformation].[PriceNote], @Keyword)
OR
Contains
([dbo].[ClientInformation].[ApartmentNote], @Keyword)
So, we figure out that the main problem is in slowness of 'LEFT JOIN' and we try to find replacement for this code.
We can use 'EXISTS' operator to optimize the query instead of the slow 'LEFT JOIN'
The solution is not obvious, but fortunately not too difficult either. Instead of the slow ‘LEFT JOIN’, we can rewrite our query using the ‘EXISTS’ operator which will get us the same result, but much faster then before. Here is the final query:
SELECT
DISTINCT
COUNT
(
DISTINCT
(ClientInformation.ID))
AS
[cID]
FROM
[dbo].[ClientInformation] (nolock)
INNER
JOIN
[dbo].[Client] (nolock)
ON
[dbo].[ClientInformation].[ID]=[dbo].[Client].[ClientInformationID]
WHERE
Contains
([dbo].[ClientInformation].[Note], @Keyword)
OR
Contains
([dbo].[ClientInformation].[MoveInNote], @Keyword)
OR
Contains
([dbo].[ClientInformation].[OtherInfo], @Keyword)
OR
Contains
([dbo].[ClientInformation].[PriceNote], @Keyword)
OR
Contains
([dbo].[ClientInformation].[ApartmentNote], @Keyword)
OR
EXISTS
(
SELECT
[dbo].[ClosingDetails].[ID]
AS
[Id]
FROM
[dbo].[ClosingDetails] (nolock)
WHERE
[dbo].[ClosingDetails].[ClientInformationID] = [dbo].[ClientInformation].[ID]
AND
Contains
([dbo].[ClosingDetails].[Note], @Keyword))
OR
EXISTS
(
SELECT
[dbo].[DealClient].[Id]
FROM
( [dbo].[ClosingDetails] (nolock)
INNER
JOIN
[dbo].[DealClient] (nolock)
ON
[dbo].[ClosingDetails].[ID]=[dbo].[DealClient].[ClosingDetailsId])
WHERE
[dbo].[ClosingDetails].[ClientInformationID] = [dbo].[ClientInformation].[ID]
AND
[dbo].[DealClient].[ClosingDetailsId] = [dbo].[ClosingDetails].[ID]
AND
Contains
([dbo].[DealClient].[Employer], @Keyword))
If you use the MonoX and LLBLGen ORM in your projects, here is an example on how to write the above SQL query using the LLBLGen syntax:
string
ftSearchPhrase =
new
FullTextSearch(Keyword).NormalForm;
keywordPredicate.Add(
new
FieldFullTextSearchPredicate(ClientInformationFields.Note,
null
, FullTextSearchOperator.Contains, ftSearchPhrase));
keywordPredicate.AddWithOr(
new
FieldFullTextSearchPredicate(ClientInformationFields.MoveInNote,
null
, FullTextSearchOperator.Contains, ftSearchPhrase));
keywordPredicate.AddWithOr(
new
FieldFullTextSearchPredicate(ClientInformationFields.OtherInfo,
null
, FullTextSearchOperator.Contains, ftSearchPhrase));
keywordPredicate.AddWithOr(
new
FieldFullTextSearchPredicate(ClientInformationFields.PriceNote,
null
, FullTextSearchOperator.Contains, ftSearchPhrase));
keywordPredicate.AddWithOr(
new
FieldFullTextSearchPredicate(ClientInformationFields.ApartmentNote,
null
, FullTextSearchOperator.Contains, ftSearchPhrase));
PredicateExpression closingDetailsPredicate =
new
PredicateExpression();
closingDetailsPredicate.Add(ClosingDetailsFields.ClientInformationId == ClientInformationFields.Id);
closingDetailsPredicate.Add(
new
FieldFullTextSearchPredicate(ClosingDetailsFields.Note,
null
, FullTextSearchOperator.Contains, ftSearchPhrase));
keywordPredicate.AddWithOr(
new
FieldCompareSetPredicate(
null
,
null
, ClosingDetailsFields.Id,
null
, SetOperator.Exist, closingDetailsPredicate,
null
,
null
,
false
));
PredicateExpression dealClientPredicate =
new
PredicateExpression();
dealClientPredicate.Add(ClosingDetailsFields.ClientInformationId == ClientInformationFields.Id);
dealClientPredicate.Add(DealClientFields.ClosingDetailsId == ClosingDetailsFields.Id);
dealClientPredicate.Add(
new
FieldFullTextSearchPredicate(DealClientFields.Employer,
null
, FullTextSearchOperator.Contains, ftSearchPhrase));
RelationPredicateBucket dealClientBucket =
new
RelationPredicateBucket();
dealClientBucket.Relations.Add(DealClientEntity.Relations.ClosingDetailsEntityUsingClosingDetailsId);
keywordPredicate.AddWithOr(
new
FieldCompareSetPredicate(
null
,
null
, DealClientFields.Id,
null
, SetOperator.Exist, dealClientPredicate, dealClientBucket.Relations,
null
,
false
));
bucket.PredicateExpression.AddWithAnd(keywordPredicate);
We hope that this little tip will save you a couple of hours next time you need a fast text search utility for your project.