Performance issue with CONTAINS

Seemingly a simple thing but something is wrong:

SELECT * FROM PERSON WHERE company_id = 7410 AND CONTAINS(first_name,
'Daniel') takes 2.5 minutes.

SELECT person.* FROM CONTAINSTABLE(PERSON, first_name, 'Daniel') AS a
JOIN person ON a.key = person.person_id WHERE company_id = 7410 takes
under a second.

Person table has 1101665 Million rows. 3893 rows have company_id =
7410. 6836 rows contain 'Daniel'. Total of 32 rows are returned.

Catalog attributes:
Item Count: 1101665
Unique Key Count: 4271510
Catalog Size: 128 Mb

The second query runs as expected: person table performs an index seek
on the company_id index and returns 3893 rows, remote scan returns 6836
rows and a Hash Match join is used to produce 32 rows.

The second query starts the same by using the company_id index and
returning 3893 rows but then things get ugly. Remote scan show
estimated number of rows as 6836 but returns 26,510,733 rows and a
filter is then applied to filter the number of rows to 32! Needless to
say that takes a while.

Need to add the fact that the catalog contains one table 'PERSON' but
there are about
dozen columns in PERSON table being indexed.

Any thoughts?

Thanks

.

Relevant Pages

  • Re: Performance issue with CONTAINS
    ... 'Daniel' and '7410') takes 2.5 minutes. ... Catalog Size: 128 Mb ... The second query runs as expected: person table performs an index seek ... filter is then applied to filter the number of rows to 32! ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Performance issue with CONTAINS
    ... and try to filter it down but now instead of millions of row it would ... 'Daniel' and '7410') takes 2.5 minutes. ... Catalog Size: 128 Mb ... The second query runs as expected: person table performs an index seek ...
    (microsoft.public.sqlserver.fulltext)
  • Re: SBS VPN access
    ... Have you added host files and lmhost files on the remote computers.? ... "daniel" wrote in message ... > available to remote users please? ... >> Regards, ...
    (microsoft.public.windows.server.sbs)
  • Re: Unable to Remotely connect to Client Desktop
    ... add the particular account to the Remote Desktop Users Group in ... the particular user at the Remote tab of System Properties by click "Select ... "Daniel Isac" skrev i meddelandet ... > WinXP Pro to be able to connect to them remotely. ...
    (microsoft.public.windows.server.sbs)
  • Re: Performance issue with CONTAINS
    ... BTW, I'm assuming this thread applies to Sql Server 2005 here, right? ... 'Daniel' and '7410') takes 2.5 minutes. ... Catalog Size: 128 Mb ... The second query runs as expected: ...
    (microsoft.public.sqlserver.fulltext)
Loading