Fulltext search too slow

Hi,

I am using an FT index on a column (varchar(4000) with an average
length of 100) in a table that contains about 1 million rows.

My tiny performance test looks like

declare @t datetime
set @t = getdate()
select count(*) from containstable(table_name, expression,
'"searchthis*"')
print datediff (ms, @t, getdate())

If "searchthis" matches about 1000 rows, the search will usually take
about 2 seconds. But when it matches a lot of rows (some terms will
match up to 200.000 rows) then the search takes up to one minute. With
5.000 matches the search already takes about 15 seconds. It helps a
bit to restrict the ft search to Top-20000-of-rows but not enough and
I need many results (not to display them but to display how many
results of what type were found).

This is too slow for my application and I am sure the performance has
degraded for some reason. I am rebuilding the ft index every week and
reorganizing it every night. CPU and disk queues look relaxed so there
might be a less apparent bottleneck.

Does someone know where I should start looking for an enhancement, or
are these very rough performance figures normal for a 4 * 3 GHz
machine?

Thanks for any hint in advance,

Regards
DC

.

Relevant Pages

  • Re: SenderName problem and restrict performance
    ... I am trying to use AdvancedSearch but facing problems with C#. ... What I want to do is to sort the results and display the mails (from ... Restrict and Find do not support wildcards. ... would using multiple restricts further decrease performance as ...
    (microsoft.public.outlook.program_vba)
  • Re: percent
    ... I would like to use a drop down list to display the states ... >"way to restrict user from seeing only one state at a ... Can I give the user option ... >> ALL (land, water, forest,all). ...
    (microsoft.public.access.formscoding)
  • After dismissing getsaveasfilename dialog, my userform becomes inactive
    ... >From this form, on a button click, I am opening 'GetSaveAsFileName' dialog ... select cells on the active worksheet. ... Regarding restricting access to worksheet, I am able to restrict the user ... I am not able to display the userform if I select ...
    (microsoft.public.excel.programming)
  • Re: Not changing desktop setting with right button policy?
    ... > I restricted the Display icon in Control Panel, the Intel graphics ... There is also via GPO to restrict the Settings Tab of Display Properties not ...
    (microsoft.public.windows.server.general)
  • Re: Date and not DateTime
    ... > SELECT CONVERT(VARCHAR, getdate(), 112) AS YYYYMMDD ... > SQL Server MVP ... >> How do i display the Date and only the Date working with DatePart? ...
    (microsoft.public.sqlserver.programming)
Loading