Re: FTS query performance on SQL 2005

Speed depends on the complexity of your query and the amount of rows you are
returning. In your case you have a simple query and what appears to be
causing the performance problem. I would use containstable with the
top_n_by_rank parameter to limit your results set to 100 or 200 rows.

Is this SQL 2005? There are some optimizations for SQL 2005 which will offer
better performance.

--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com



"Bahama Joe" <someone@xxxxxxxxxxxxx> wrote in message
news:ub9L57y%23GHA.1224@xxxxxxxxxxxxxxxxxxxxxxx
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: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)
  • Re: Populating a list -- table structure?
    ... I had made a report already and figured out about adding the ... your responce below, but thanks to your help with SQL, I was able to get the ... It takes a summary from a select query and gives the ... KitID, long integer ...
    (microsoft.public.access.forms)
  • How do I do Paging through a large dataset via Stored Procedures
    ... Paging by dynamically altering the SQL Query ... Create stored procedures ... SELECT * FROM STUDENTS ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Populating a list -- table structure?
    ... I don't mean to skip your responce below, but thanks to your help with SQL, I was able to get the query to pull the info the way I needed. ... I have one report complete, ... Queries (just shows the QBE grid for convenience -- ...
    (microsoft.public.access.forms)
  • Re: Populating a list -- learning Access
    ... It is kinda funny -- I started using databases in the early 80's with dBase and, for years, never knew that I knew SQL! ... If you are on a form or report, the most important property is the NAME, because that is how you refer to it in code. ... I don't mean to skip your responce below, but thanks to your help with SQL, I was able to get the query to pull the info the way I needed. ... Queries (just shows the QBE grid for convenience -- ...
    (microsoft.public.access.forms)