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

• shiv.kr.verma

  Newbie

  Points: 1

  More actions

  November 25, 2009 at 11:58 pm

  #206772

  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

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  November 27, 2009 at 8:52 am

  #1084936

  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