WHERE with Containstable

WHERE with Containstable

Post by Sumi » Sun, 19 May 2002 02:13:58



Hi ,

This is just for curiosity sake.

When we use a WHERE clause with CONTAINSTABLE, is the
WHERE clause handled by the SQL Server Relational engine
or is it handled by the Full-Text provider ?

Thanks
Sumit

 
 
 

WHERE with Containstable

Post by Hilary Cotte » Sun, 19 May 2002 10:45:52


the where clause is handed off to the MSSearch engine.  Do a CTRL - L in
isqlw when highlighting your query and you will see a graphical
representation of how your query is handled.  The remote scan is the handing
off of your search clause to MSSearch


Quote:> Hi ,

> This is just for curiosity sake.

> When we use a WHERE clause with CONTAINSTABLE, is the
> WHERE clause handled by the SQL Server Relational engine
> or is it handled by the Full-Text provider ?

> Thanks
> Sumit


 
 
 

1. Containstable on Multiple columns.

Hi,
  I have a question reg the performance of the following query.
I need to get 500 rows from tblBook with Title = "America" & Author=
"James".  Using "CONTAINS" slows down the query.

Question 1:
  a.. When a limit of 500 is used as part of the CONTAINSTABLE, as in
    -- Title ONLY
    INNER JOIN CONTAINSTABLE(tblBook, Title,'"AMERICA"', 500) AS T1 ON
CB.Pf_ID = T1.[Key]
  the resultset is returned fast.

  a.. When a limit of 500 is used as part of the CONTAINSTABLE in a join, as
in
    -- Title and Author combination
      INNER JOIN CONTAINSTABLE(tblBook, Title,'"AMERICA"', 500) AS T1 ON
CB.Pf_ID = T1.[Key]
      INNER JOIN CONTAINSTABLE(tblBook, Author,'"JAMES"', 500) AS T2 ON
CB.Pf_ID = T2.[Key]

  the query takes first matching 500 titles (i.e AMERICA) and first matching
500 Authors (i.e JAMES) and makes a join out of it.  This returns little or
no resultset when the first matching 500 titles does not contain Author as
JAMES.
  I cannot use a * here since I need specific title and specific author.
  Avoiding the limit of 500 returns meaningful result but too slow.

  Can you suggest ways for improving the performance of the query?

2. HELP----SNMP AGENT-----

3. CONTAINSTABLE & Searching Numbers

4. Advanced Search Form

5. Understanding CONTAINSTABLE rankings in SQL 2000

6. long running query

7. search engine with containstable

8. Importing a 6.5 .DAT file into 2000

9. CONTAINSTABLE not returning wildcard records

10. Using CONTAINSTABLE and Rank causes timeout

11. RANK returned by CONTAINSTABLE

12. Contains v/s Containstable

13. what is used to determine rank for CONTAINSTABLE?