FTS query performance on SQL 2005

I am seeing some query performance issues on a full-text search on SQL 2005.
My table has about 20 million rows, containing an integer primary key and a
field of type text. This is running on a dual core Xeon 2.8 Ghz processor, 2
GB RAM, 15k RPM drives in a RAID 5 configuration.

My query looks like this:
select ItemID FROM Item WHERE CONTAINS(ItemText, 'there')

which returns about 40,000 rows, but the query takes over 2 minutes! Is that
normal performance for this beefy server for such a simple query? If I add
"TOP 20" after the SELECT, the query takes under 1 second.

I have checked the hardware and I can't seem to find constraints, either in
CPU, memory or disk. Any ideas why that query takes over 2 minutes?

Thanks.


.

Relevant Pages

  • Re: DMX Query response time
    ... but mostly training time rather than query performance. ... inside the SQL server since you're invoking the Analysis Services ...
    (microsoft.public.sqlserver.datamining)
  • Re: Partitioned View Problem
    ... then you'll likely find PVs ok. ... They can offer significant improvements to ... query performance, especially if the underlying base tables are located on ...
    (microsoft.public.sqlserver.programming)
  • Re: query grindingly slow!
    ... query performance when the selection & sort fields you are using are indexed ... If you are basing a query on joined tables, ...
    (microsoft.public.access.queries)
  • Re: Query Retrieval is slow
    ... This query insert data into a temp table. ... >columns being declared with statistics. ... >improve query performance? ... >>sql server indexes automatically and server processor. ...
    (comp.databases.ms-sqlserver)
  • RE: MSDE Performance
    ... MSDE 2000 does not support query parallelism and some other tuning features. ... > I am using MSDE and i recently realised that the query performance is ... > Could it be that MSDE performance is so dramatically lower than SQL Server? ...
    (microsoft.public.sqlserver.msde)