RE: where filter on full-text contains / order of operations
- From: sql411@xxxxxxxxxx <sql411nospamcom@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 30 May 2007 11:41:03 -0700
In hopes I can get some advice on this I will explain a few details and ask a
qusetion that might be more specific.
I am starting to have some major slowness with Contains/Fulltext searches.
Currently, the clustered index and key of the FT catalog is an INT (4
bytes), while the 6 columns that are full text indexed are of type TEXT.
There are 100
I have seen other folks say they partitioned and got better performance, but
if you add a partition that increases the clustered/ft key -- that seems like
it would make things slower (searches/rebuild).
Is there any improvement going from TEXT data type to varchar(max) / and
choosing to store inside/outside row data?
I know there are a bunch of questions/details here -- but I am just trying
to put out as much as I can in case someone else has gone through this before
and can offer their insight into improving FT capabilities.
Gracias,
Robert Towne
"sql411@xxxxxxxxxx" wrote:
I have seen multiple posts that state a where clause can help filter a.
full-text search to ensure no full-text records are searched. What I
understand when showing "set statistics io on" -- the worktable is what the
FTE uses to search for the CONTAINS. Whether I have a filter (say, on date)
or no filter other than the CONTAINS -- the worktable logical reads are the
same. I have put a sample query below and the associated reads for
comparison. Sure, the fullTextTable reads are minimized when there is a date
filter but the FTE worktable remains the same (if some how the filter were
applied to the FTE worktable -- I am assuming the reads would be much lower).
I understand the ContainsTable method is faster, but both are showing the
worktable results to be static whether additional filter criteria added or
not.
Has anyone been able to add filter criteria and see a reduction in the
worktable resources?
Thanks in advance,
Robert Towne
SELECT message_id, message_date
into #test
FROM fullTextTable WITH (NOLOCK)
WHERE Contains( html_body, 'guarantee')
Table 'Worktable'. Scan count 1, logical reads 7253151, physical reads 5926,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead
reads 0.
Table fullTextTable . Scan count 1, logical reads 219502, physical reads
35, read-ahead reads 219461, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
SELECT message_id, message_date
into #test
FROM fullTextTable WITH (NOLOCK)
WHERE Contains( html_body, 'guarantee')
and am.message_date >= 'March 1 2007 12:00AM' AND am.message_date <= 'May
10 2007 11:59PM'
Table 'Worktable'. Scan count 1, logical reads 7253151, physical reads 5662,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead
reads 0.
Table fullTextTable . Scan count 1, logical reads 50823, physical reads 4,
read-ahead reads 50819, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
- Prev by Date: Re: sql 2000 Fulltext hanging on reindex
- Next by Date: "Sounds-like" queries on full-text catalogs?
- Previous by thread: newbie: FTS in 2005 Express Edition with Advanced Services
- Next by thread: "Sounds-like" queries on full-text catalogs?
- Index(es):
Relevant Pages
|
