Re: Full Text Search Performance

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Hilary Cotter (hilary.cotter_at_gmail.com)
Date: 10/22/04 

Date: Fri, 22 Oct 2004 12:56:31 -0400

No, this is highly abnormal. Performance should be sub second. However I
need to know what your queries look like, ie how many search arguments, how
many rows are returned, and what language you are querying in.

"Ali Salem" <AliSalem@discussions.microsoft.com> wrote in message
news:AECBD305-A9FD-4759-B240-B4D970B32BEA@microsoft.com...
>
> thanks for your reply.
>
> I cannot limit the search in this way, as there are other parameters in my
> query that can affect the returned result set and that are ourside the
FTS.
>
> I am asking for any figures about FTS performance? What should I expect
from
> it? Is this behavior am getting normal for the data size or, I am having
> something wrong
>
> "Hilary Cotter" wrote:
>
> > That depends. SQL FTS performance is most sensitive to the number of
rows
> > you are returning. You should limit your results set as much as
possible - I
> > think you will find that the practical limit for most applications is
around
> > 100-200 rows.
> >
> > Limit it by using the the top_n_by_rank operator in ContainsTable or
> > FreeTextTable, ie
> >
> > USE Northwind
> > GO
> > SELECT FT_TBL.Description,
> > FT_TBL.CategoryName,
> > KEY_TBL.RANK
> > FROM Categories AS FT_TBL INNER JOIN
> > CONTAINSTABLE (Categories, Description,
> > 'test',200
> > ) AS KEY_TBL
> > ON FT_TBL.CategoryID = KEY_TBL.[KEY]
> > ORDER BY KEY_TBL.RANK DESC
> > "Ali Salem" <AliSalem@discussions.microsoft.com> wrote in message
> > news:CFE9CB8C-7AE4-44C3-8A3C-BC397585AC1E@microsoft.com...
> > > Hello,
> > >
> > > I have full text search enabled on two large tables, the first one has
3
> > > million records (FT index column length is around 512-1025 char), and
the
> > > other one has around 1.6 records (FT index column length is around
50-120
> > > char)
> > >
> > > The simplest query that uses the smiplest form of FT on the first
table FT
> > > index search needs 23-30 seconds to complete and from 10-15 seconds on
the
> > > seconds table as it the indexed column is smaller.
> > >
> > > SQL server is deployed on 4 P servers with 8 GB of memory and attached
to
> > a
> > > very powerful SAN system. Memory usage on the server is not very high
so I
> > > believe there is enough memory for the mssearch service to use.
> > >
> > > I just wonder if this is the normal throughput of the Full Text search
of
> > > SQL Server. If not, I will appreciate any tips and hints that might be
the
> > > reason of the system.
> > > --
> > > Ali Salem
> >
> >
> >

Relevant Pages

  • Re: Full Text Search Performance
    ... Hilary Cotter ... Performance should be sub second. ... >> need to know what your queries look like, ie how many search arguments, ... >>> I am asking for any figures about FTS performance? ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Full Text Search Performance
    ... Do you have any comment to add to my problwm ... "Hilary Cotter" wrote: ... > need to know what your queries look like, ie how many search arguments, how ... >> I am asking for any figures about FTS performance? ...
    (microsoft.public.sqlserver.fulltext)