Knowledge is power. We love to share it.

News related to Mono products, services and latest developments in our community.

kpeulic

SQL Tips & Tricks: full-text query optimization

01/20/2013Categories: MonoX, ASP.NET, Other

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.

Rated 3.67, 6 vote(s).