full-test search too slow

Question

full-test search too slow

jroberts55116

SSC Rookie

Points: 29
More actions
June 24, 2004 at 4:25 pm

#162169

I am a SQL Server user who is new to full-text searching. All was going well until I tried to add an additional filter to a query that already had a CONTAINS clause.
(I am using Microsoft SQL Server 7.00 - 7.00.1063 (Intel X86) Standard Edition on Windows NT 4.0 (Build 1381: Service Pack 6))
My original query was:
select * from MYTABLE where CONTAINS(MYFIELD, ' "searchterm1" and "searchterm2" ')
It returns a couple of thousand records in a few seconds.
Now I want to filter on another field (an indexed field), which winnows the results to fewer than 100 records.
I tried:
select * from MYTABLE where CONTAINS(MYFIELD, ' "searchterm1" and "searchterm2" ') and MYFIELD2 = 'searchterm3'
and
select FT_TBL.* from MYTABLE as FT_TBL,
CONTAINSTABLE (MYTABLE, MYFIELD, ' "searchterm1" and "searchterm2" ') AS KEY_TBL
WHERE
FT_TBL.myid = KEY_TBL.
and FT_TBL.MYFIELD2 = 'searchterm3'
Either way, the query now takes more than 30 seconds to run. I want to use the query in a web application, and that's too slow.
Is there anything wrong with my syntax, or are there other options I could try to improve performance? My understanding of the process is that SQL runs the CONTAINS part of the query first, then applies the filter in the WHERE clause. Since the CONTAINS piece ran quickly before, I don't understand why it's so much slower to filter a smaller results set on an indexed field.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John Kane SSCrazy Points: 2810 More actions · Answer 1

Depending upon how large your FT-enable table is, you may want to consider using the new Top_N_Rank parameter with your CONTAINSTABLE query, for example:

select FT_TBL.* from MYTABLE as FT_TBL,

CONTAINSTABLE (MYTABLE, MYFIELD, ' "searchterm1" and "searchterm2" ', 1000) AS KEY_TBL

WHERE FT_TBL.myid = KEY_TBL. and FT_TBL.MYFIELD2 = 'searchterm3'

where 1000 is the Top_N_Rank in the above modified query. For more details, see KB article 240833 (Q240833) "FIX: Full-Text Search Performance Improved via Support for TOP" at http://support.microsoft.com//default.aspx?scid=kb;EN-US;240833 Yes, SQL Server (7.0 & 2000) apply the filter in the WHERE clause AFTER returning ALL rows from the FT Catalog unless restricted by the Top_N_Rank parameter.

Finally, if you plan on doing production level Full Text Search (FTS) query processing on tables with >500K rows, I'd highly recommend that you consider upgrading to SQL Server 2000 as there are many, many fixes, performance enhancements and new features over what is available in SQL Server 7.0. For example, you may also be hitting one or both of the following SQL Server 7.0 bugs that are fixed in SQL Server 2000:

230103 BUG: Cannot Have More than Eight Full Text Joins and Operations

http://support.microsoft.com/default.aspx?scid=KB;en-us;Q230103

230036 BUG: Heavy Full Text Query Activity Results in Unexpected Timeout Errors

http://support.microsoft.com/default.aspx?scid=KB;en-us;Q230036

Regards,

John Kane

PS: You can also post FTS related questions to the newsgroup: microsoft.public.sqlserver.fulltext

John T. Kane

jroberts55116 SSC Rookie Points: 29 More actions · Answer 2

Thanks for the reply, John. I tried your suggestion re: Top_N_Rank, with no noticeable improvement in performance.

I doubt I'm running into the bugs you mention -- at least not yet, anyway -- because no one but me is querying the database yet, and I'm only firing one query at a time.

I will add this to my growing body of evidence for why we need to upgrade. In the meantime, I welcome any other ideas anyone has on this problem.

--Janet Roberts

jroberts55116 SSC Rookie Points: 29 More actions · Answer 3

In case this helps someone else someday:

I created a clustered index on MYID and MYFIELD2 and regained the speed I had before adding the WHERE filter.

Janet

John Kane SSCrazy Points: 2810 More actions · Answer 4

Yep, adding a clustered index on MYID and [non-clustered index on] MYFIELD2 will always help query performance issues on large tables, even if FTS is not used!

John Kane

John T. Kane

skm Grasshopper Points: 11 More actions · Answer 5

Although I can see you sorted out your performance problem with FTS, I would like to recommend an easy replacement for FTS. The product is called SQL Turbo and integrates with SQL Server. It works similar to FTS, but is much much faster.

http://www.surfinity.com/sqlturbo