Performance question

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

Hello,
Our main product catalog is approx. 3.1 million rows, with a full-text index
on 3 (varchar) columns. For the past year as our catalog has grown, we have
experienced continuing performance degredation, to the point that we are
looking at biting the bullet and migrating this application to Oracle Text,
which from our initial testing is several orders of magnitude faster (we're
going to stick with SQL Server for everything else). Obviously we'd like to
avoid that due to cost issues. However, this application is very important
and if we've reached the limit of SQL Server then so be it. Our problem is
that when a customer searches our catalog, we sort the search results based
on their sales rank which does not allow us to use the "top_n" parameter of
containstable or freetexttable. For example, say a customer searches our
catalog for a relatively common word that results in around 72,000 results
(takes approx 11 sec on subsequent runs...over 1 min on first run, which is
the most important statistic). If we were to use (say) n=2000 for the top_n
parameter, our best selling products would not be returned from the FTS
engine. We need to return all the results so we can sort them by sales rank
and display them to customers.

The server is running Windows 2003, with 6GB RAM, 16 x 15,000K RPM SCSI
drive in a RAID 10, in a dual opteron configuration the with transaction log
on a seperate RAID volume. SQL is SQL Server 2000, SP4 (I've included the
output of @@version below). Perfmon shows that the server isn't sweating at
all during these queries from a disk, memory, or CPU standpoint, so that
leaves SQL as the performance bottleneck. Our most recent population was
around a month ago, so the catalog is relatively up to date.

We've fooled around with increasing the memory available to FTS, but that
did not seem to make a difference. Perhaps we did not do it right since
mssearch.exe is still only showing about 49,000K in memory---but since we
are using AWE this could be distorted.

We're going to make one last gasp at improving the performance here before
dumping SQL Server and moving to Oracle. Help!

John

@@Version:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows
NT 5.2 (Build 3790: )

Sample query:
SELECT distinct <field list>
FROM containstable(<ft-table>, <ft-field>,'"<common term>"') as ct
JOIN <ft-table> t with (nolock) on t.<PK>=ct.[key] //tables are 100%
readonly except during monthly updates, hence the nolocks
join salesRank sr with (nolock) on sr.<PK>=ct.[key]
order by sr.SalesRank


.

Relevant Pages

  • Re: Performance question
    ... perhaps in your case by sales rank. ... Looking for a SQL Server replication book? ... For the past year as our catalog has grown, ... say a customer searches our ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Update based on SQ LDynamic Stored Proc Problem
    ... I'm using Visual Studio 2005 with SQL Server 2005 ASP.NET 2.0 VB ... I have a Catalog table and I created a form with textboxs and Submit button to call stored procedure to updated existing records ... Basically you create a SQLParameter for each input parameter for the SP provide each parameter with a value and add the SQLParameter to the SQLCommand. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Help - An unknown full-text failure (80004005) while trying to create a new full-text catalog
    ... You are correct *most* of the causes for the "unknown full-text failure ... while trying to create a new full-text catalog" in SQL Server ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Fulltext index for 2 columns in one table
    ... In SQL Server 2005 you can now pass a list of indexed columns instead, ... >> You can put both columns in one catalog. ... >> In Sql Server 2000 you can query one column or all columns. ... >>> have to search using this tilldescription. ...
    (microsoft.public.sqlserver.fulltext)
  • Re: When does Full Catalog Break.
    ... Extensions to SQL Server to Support Full-Text Search ... Textual Searches on Database Data Using Microsoft SQL Server 7.0 ... > A good KB article that describes the procedures for moving a FT catalog - ... >> Will the full text catalog break if I delete all the records from a ...
    (microsoft.public.sqlserver.fulltext)