Performance problem while using top (1) with full text search

  • Hi

    I am executing query as follows

    select top(1) city, state from city_state_finder where

    freetext((city,state),'vancouver') and freetext((city,state),'on')

    While executing this query response will not come as it's delaying the result without giving any errors.

    But if used same query without top(1) then result is coming up in 3 seconds.

    select city, state from city_state_finder where

    freetext((city,state),'vancouver') and freetext((city,state),'on')

    Please guide me why this is happening & how can i over come this situation.

    Thanks

    Shiv

  • shiv.kr.verma (11/25/2009)


    Hi

    I am executing query as follows

    select top(1) city, state from city_state_finder where

    freetext((city,state),'vancouver') and freetext((city,state),'on')

    While executing this query response will not come as it's delaying the result without giving any errors.

    But if used same query without top(1) then result is coming up in 3 seconds.

    select city, state from city_state_finder where

    freetext((city,state),'vancouver') and freetext((city,state),'on')

    Please guide me why this is happening & how can i over come this situation.

    Thanks

    Shiv

    Full text stuff in 2005 is not as good as it should be, especially with respect to the optimizer. 2008 is much better!

    I presume there is some difference in the query plan the optimizer puts out. You can investigate that. However, since the no-top query works fine, try this:

    SELECT TOP (1) *

    FROM (select city, state from city_state_finder where

    freetext((city,state),'vancouver') and freetext((city,state),'on')) as t

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 2 posts - 1 through 2 (of 2 total)

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