a question or two re fulltext queries

hi,
i'm using SQL Server 2000 (SP3, I think).
i am looking at SQL for finding documents (table DOC) based on a couple of
criteria, one of which involves a fulltext index on a related table (TXT).
i discovered today that a less selective CONTAINS() clause:
CONTAINS (txt_stripped, 'daimler')
results in disastrous performance when I ask for the "top 10" documents
sorted by published date (a field of DOC) descending.
if I
1) include a more selective contains clause:
CONTAINS (txt_stripped, 'daimler AND mercedes')
2) omit the "top 10" (there are only125 documents total.) or
3) omit the "ORDER BY doc_pubfrom DESC" clause
the query performs fine.
i examined the query plans and discovered that in the bad case the first
thing done is to walk the doc_pubfrom index (figures, I guess: top 10 order
by...). in the other cases the first thing done is the "remote scan" of the
fulltext index, which is what I want done first.
i already know how I'll solve this problem: i don't need the "top 10" at
this point -- it was just a whim while prototyping.
my questions, though for future reference:
1) is there syntax to influence SQL's query planning specifically to prefer
the full text index ?
2) is there a way to get the query plan textually? (the graphic view is
great but I wondered what I would do if I wanted to mail it or post it)
cheers,
Tim Hanson
1) Could you post the entire schema with indexes?
2) use set showplan_text on
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"tbh" <femdev@.newsgroups.nospam> wrote in message
news:uTlE0P$KGHA.1180@.TK2MSFTNGP09.phx.gbl...
> hi,
> i'm using SQL Server 2000 (SP3, I think).
> i am looking at SQL for finding documents (table DOC) based on a couple of
> criteria, one of which involves a fulltext index on a related table (TXT).
> i discovered today that a less selective CONTAINS() clause:
> CONTAINS (txt_stripped, 'daimler')
> results in disastrous performance when I ask for the "top 10" documents
> sorted by published date (a field of DOC) descending.
> if I
> 1) include a more selective contains clause:
> CONTAINS (txt_stripped, 'daimler AND mercedes')
> 2) omit the "top 10" (there are only125 documents total.) or
> 3) omit the "ORDER BY doc_pubfrom DESC" clause
> the query performs fine.
> i examined the query plans and discovered that in the bad case the first
> thing done is to walk the doc_pubfrom index (figures, I guess: top 10
> order by...). in the other cases the first thing done is the "remote scan"
> of the fulltext index, which is what I want done first.
> i already know how I'll solve this problem: i don't need the "top 10" at
> this point -- it was just a whim while prototyping.
> my questions, though for future reference:
> 1) is there syntax to influence SQL's query planning specifically to
> prefer the full text index ?
> 2) is there a way to get the query plan textually? (the graphic view is
> great but I wondered what I would do if I wanted to mail it or post it)
> cheers,
> Tim Hanson
>
|||thanks, Hillary, that was quick!
re 1) by "schema" you mean all affected tables? that would be a serious
handful. i'd hate to trouble you and post so much stuff. do you have any
general hints on keywords for nudging the query plan in favor of fulltext
index first?
re 2) thanks!!
cheers,
Tim
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OPg4yc$KGHA.668@.TK2MSFTNGP11.phx.gbl...
> 1) Could you post the entire schema with indexes?
> 2) use set showplan_text on
> --
> Hilary Cotter
|||Just the problem tables. I don't have any hints off the top of my head. You
might be able to do a force order query hint which might help.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"tbh" <femdev@.newsgroups.nospam> wrote in message
news:uyE2Ow$KGHA.4052@.TK2MSFTNGP15.phx.gbl...
> thanks, Hillary, that was quick!
> re 1) by "schema" you mean all affected tables? that would be a serious
> handful. i'd hate to trouble you and post so much stuff. do you have any
> general hints on keywords for nudging the query plan in favor of fulltext
> index first?
> re 2) thanks!!
> cheers,
> Tim
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:OPg4yc$KGHA.668@.TK2MSFTNGP11.phx.gbl...
>
>
|||I have the same problem, when I search for a (litle) word that apears
in many records, the query that uses full-text index takes to long to
execute.
I use Top 1000, Contains() and Order By.
How can I optimize this without changing the TOP and Order by?
Thaks
|||Can you use a containstable and the top_n_by_rank clause? IE
SELECT FT_TBL.Description,
FT_TBL.CategoryName,
KEY_TBL.RANK
FROM Categories AS FT_TBL INNER JOIN
CONTAINSTABLE (Categories, Description,
'("sweet and savory" NEAR sauces) OR
("sweet and savory" NEAR candies)'
, 10
) AS KEY_TBL
ON FT_TBL.CategoryID = KEY_TBL.[KEY]
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<paulo.gonc@.gmail.com> wrote in message
news:1139421012.509817.170610@.g43g2000cwa.googlegr oups.com...
>I have the same problem, when I search for a (litle) word that apears
> in many records, the query that uses full-text index takes to long to
> execute.
> I use Top 1000, Contains() and Order By.
> How can I optimize this without changing the TOP and Order by?
> Thaks
>