Full-Text search problem

  • Hi Everyone,

    We have a database in SQL SERVER 2008 R2 and we have enabled Full-Text Search on one of our tables. We have a sp that call below TSQL

    declare @Value int = 10

    select

    count(*)

    from

    dbo.Table1 t1 join

    dbo.Table2 t2 on t1.Code1 = t2.Code1 and t2.Code2 = @Value left join

    dbo.Table3 t3 on t1.Code3 = t3.Code3 and

    t3.Code4 = 1

    where

    t1.code5 = 1 and

    contains(t1.*,'test')

    This SP has started to time out occasionally .When I checked the execution plan I noticed the process start from Full text search on Table1(on this table we have 40,000,000 rows), instead of applying the other filter's first and then Full-Text Search. I converted the Full-Text Search to use 'like' statement and in test environment in runs much faster.

    I am trying to keep my full-Text search feature. Is there any way to force optimizer to use the filters first instead of Full-Text Search.

    Thanks

  • Did you try updating meta data in fulltext index? Look in the properties.

  • Thanks for your reply. How do I know if i need to do that update?and will that cause any problem for end user. Is there any online feature for full-text search, so end user will not be affected.

  • You need to have a look into your indexing and how it correlates with the query.

    It seems like the indexes on the tables involved are so irrelevant to the parameters in the query that optimizer decides that selectivity of the query will be not good enough and it's better to try its luck with full-text search first.

    _____________
    Code for TallyGenerator

  • And you can safely remove left join

    dbo.Table3 t3 on t1.Code3 = t3.Code3 and

    t3.Code4 = 1

    left-joined table is not gonna affect COUNT(*) in any way.

    _____________
    Code for TallyGenerator

  • Sergiy (4/14/2016)


    And you can safely remove left join

    dbo.Table3 t3 on t1.Code3 = t3.Code3 and

    t3.Code4 = 1

    left-joined table is not gonna affect COUNT(*) in any way.

    Woah, careful there!

    This statement is only true if the combination of columnst Code3 and Code4 in Table3 is guaranteed to never match more than a single row. In other words, there has to be a PRIMARY KEY constraint, a UNIQUE constraint, or an unfiltered UNIQUE index on the combination (Code3, Code4) or a subset thereof. [Or, in this specific case, a UNIQUE filtered index on Code3 only with filter predicate Code4 = 1].

    If there can be duplicates, COUNT(*) will most definitely be affected!


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • My thought is t3.code4=1 is unique and therefore the count will never be more than the base table (t1) so I agree you can remove that left join for count purposes as it will never be more than the count of t1.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply