SQL Server Full-Text Search Performance

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Vijay - 09 Oct 2007 17:42 GMT

Hello,

We are using SQL Server 2000 and we are doing Full-Text Indexing on a table
that has 5 Text Columns (we have Full Text Indexing on these 5 columns).
There are around 1.5 million records in this table. And the size of the
Full-Text Catalog is around 1 GB.

The performance issue we are facing is some searches are slow. If I search
for “to kill a mockingbird” it returns results very quickly and if I search
for “numbers” the search is slower. So we are looking to increase the search
speed. And also we need to get paginated results in our application.

So what options I have to try?

I am thinking of these options:
• Upgrade to SQL Server 2005 and partition this table based on the year. Is
it possible to create different Full-Text Catalogs for each partition?
• I just learnt about Lucene4DB.
http://www.netomatix.com/products/documentmanagement/lucene4db.aspx . Has
anyone used this tool in their application? Will there be a gain in
performance in the search speed and maintaining the indexes.

Thanks
Vijay

Reply to this Message

Hilary Cotter - 09 Oct 2007 17:46 GMT

SQL 2005 is a great choice.

Lucene is also one. SQL FTS has faster indexing and is searches are very
fast. Lucene is optimized for returning small number of rows (as is SQL
FTS). Lucene does trimming which allows you to filter on the full-text index
by date (for example).

Katmai (SQL 2008) should close this gap.

Signature

RelevantNoise.com - dedicated to mining blogs for business intelligence.

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

> Hello,
>
[quoted text clipped - 24 lines]
> Thanks
> Vijay

Reply to this Message

Vijay - 09 Oct 2007 18:06 GMT

Hi Hilary Cotter,

Thanks for your quick reply.
I have a few followup questions:
Can we create a FT Catalog for each partition in a partitioned table in SQL
2005?
(If the FT Catalog is smaller then hopefully the searches will be faster)

Can you point me to any sources that show the comparision of perfomance in
Seraches and Indexing between Lucene4DB and SQL Server?

When will Katmai (SQL 2008) release?

Thanks in advance,
Vijay

> SQL 2005 is a great choice.
>
[quoted text clipped - 33 lines]
> > Thanks
> > Vijay

Reply to this Message

Hilary Cotter - 09 Oct 2007 21:02 GMT

No, one catalog per table partition or unpartitioned.

I have no stats for comparison's between Lucene4DB or SQL Server.

Katmai is scheduled for release in early Feb 2008 (IIRC).

Signature

> Hi Hilary Cotter,
>
[quoted text clipped - 55 lines]
>> > Thanks
>> > Vijay

Reply to this Message

Vijay - 09 Oct 2007 22:22 GMT

Thanks Hilary

Vijay

> No, one catalog per table partition or unpartitioned.
>
[quoted text clipped - 61 lines]
> >> > Thanks
> >> > Vijay

Reply to this Message

Simon Sabin - 15 Oct 2007 23:53 GMT

Hello Hilary,

Katmai is being launched in Feb 2008 but won't go to RTM until later in the
year.

Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

> No, one catalog per table partition or unpartitioned.
>
[quoted text clipped - 79 lines]
>>>> Thanks
>>>> Vijay

Reply to this Message