locked
FTI Query over Joined tables is slow RRS feed

  • Question

  • Hi

    I have a table with a full text index. I have a query which uses CONTAINS to search the table. This works well.

    I now need to expand the query to also search in a parent table. The two tables are join with a primary/foreign key.

    The parent table has a full text index and the columns I need to search are included in the index. The parent table is very small.

    I added an Inner Join to the query and included the parent table search in an OR. For example:

    select tb1.fields 

    from tb1 inner join tb2 on tb1.id = tb2.id

    where contains(tb1.field, 'searchvalue')

    OR contains (tb2.field, 'searchvalue')

    The problem is the search is now slow. What am I doing wrong?

    Monday, May 4, 2020 2:29 AM

All replies

  • OR is its self enable table scanning with Contains its impact performance greatly - you can achieve same thing by using union operator. Hope this helps you

    select 

    tb1.fields 

    from tb1 
    inner join tb2
    on tb1.id = tb2.id

    where contains(tb1.field, 'searchvalue')

    union  

    select 

    tb1.fields 

    from tb1 
    inner join tb2
    on tb1.id = tb2.id

    where  contains (tb2.field, 'searchvalue')


    http://uk.linkedin.com/in/ramjaddu

    Thursday, May 7, 2020 3:15 PM