Re: Containstable problem

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Hilary Cotter (hilary.cotter_at_gmail.com)
Date: 11/06/04 

Date: Fri, 5 Nov 2004 20:50:11 -0500

No, unfortunately not. When you have restictions in your where clause you
should if at all possible partition your data into different tables.

So you would have a table which would contain all the rows with a source
value of news.

The problem is you get 1000 or less rows returned from MSSearch and then you
trim based on the value of the source column.

So suppose you have 2000 rows which match your search condition, and the
first 1000 rows don't have a source value of news. Such a search would
return 0 results.

The other option is to increase the value of your top_n_by_rank to a value
which guarantees 1000 rows after being filtered by the source restiction.
Normally you don't have the luxury of knowing a value of top_n_by_rank to
guarantee this though. 

-- 
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Stdu" <stdu@myrealbox.com> wrote in message
news:%233pOC93wEHA.1988@TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a question regarding the contains and containstable.
> I have a table with about 2.000.000 records.
>
> A the begining we simply use a "select top 1000 * from tablex where
> contains(columnx,'xxx')" to find records matching the keywords enter by
> users.
>
> But, we find some performance problems when performing queries on keywords
> that appears a lot of time
> in the table (like "men", "women", "car", ...). The query take more than a
> minute.
>
> So we change our query to use the containstable to limit the reseach and
it
> worked fine and it only take few seconds. (Perfect)
>
> But now, we wanted to add a where clause in the select to limit the
reseach
> according to a value of a field (not full text)
> So, the query was something like this :
>
> SELECT TOP 1000 ID FROM tableX AS FT_TBL
> INNER JOIN CONTAINSTABLE (tableX, FT_INDEX , 'men',1000) AS KEY_TBL ON
> FT_TBL.ID = KEY_TBL.KEY
> WHERE SOURCE = 'news'
>
> But, I notice that the containstable limit the result the 1000 first
> occurences of 'men' and after that the where source='news' is applied.
> So, of the 1000 records I only have about 500 with a "men" in the ft_index
> and source='news'. But in the DB I have more than 2000 records with "men"
> and "news"
>
> Is there any way to combine the where clause and benefit of the limit of
the
> containstable ?
> My hope is to have a select distinct top 1000 it from tablex where
> source='news' and contains(ft_index,'men',1000)
>
> Thanks
>
> Stephane
>
>
>

Relevant Pages

  • Containstable problem
    ... we find some performance problems when performing queries on keywords ... SELECT TOP 1000 ID FROM tableX AS FT_TBL ... WHERE SOURCE = 'news' ... Is there any way to combine the where clause and benefit of the limit of the ...
    (microsoft.public.sqlserver.fulltext)
  • Re: question about last call optimization
    ... The slowdown is due to test2 using indexing, ... > where test3 creates a choicepoint. ... > - number of permanent variables in the clause ... > The good news is that in my plans for a new ECLiPSe compiler, ...
    (comp.lang.prolog)
  • Re: Getting more OT (was: Re: FS: All manner of Acorn Hardware)
    ... John Williams (News) wrote: ... and one should parse outwards from that. ... who scans groups for mention of this word" is a genitive clause. ...
    (comp.sys.acorn.hardware)
  • Re: Where do you p00p?
    ... wonderful news because mom's hate it. ... have to clean it up" clause. ...
    (microsoft.public.cert.exam.mcse)