containstable rank inconsistent

Hi,
I have an FTS table on which I execute a CONTAINSTABLE(ISABOUT..) .
the search works fine but I noticed that after I do an incremental
population or after a track changes has been triggered by a change in the
data
then I get different rank results between identical searches (pre and post
population).
this is then sorted if I do a full population or rebuild the catalog
does anybody know why this happens, is it a bug? can it alter the rank in a
way that changes the order of appearance or is it always relative?
I am building an information retrieval (search) system and this could be a
big upset so any help
would be appreciated
thanks
shay
there was a problem with this prior to sp3. What version of sql server are
you running? do a select @.@.version to determin this.
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
"shay cohen" <shay@.infospheraltd.com> wrote in message
news:%23MUx$EHVFHA.3544@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have an FTS table on which I execute a CONTAINSTABLE(ISABOUT..) .
> the search works fine but I noticed that after I do an incremental
> population or after a track changes has been triggered by a change in the
> data
> then I get different rank results between identical searches (pre and post
> population).
> this is then sorted if I do a full population or rebuild the catalog
> does anybody know why this happens, is it a bug? can it alter the rank in
a
> way that changes the order of appearance or is it always relative?
> I am building an information retrieval (search) system and this could be a
> big upset so any help
> would be appreciated
> thanks
> shay
>
>

CONTAINSTABLE Column Weighting in FTS 2005

Hi there,
Is it possible with SQL Server 2005 to add weighting to a column in a
CONTAINSTABLE or FULLTEXTTABLE ?
I've got the statment :
SELECT ftt.RANK, [content].id, [content].title, [content].content_data FROM
[content] INNER JOIN CONTAINSTABLE([content], (title, content_data),
'Summary') as ftt ON ftt.[key]=[content].id ORDER BY ftt.rank desc
What I need to do is ensure that matches in the title column have much
higher ranking than matches in content_data.
I've seen similar posts, but have not seen a definitive yes/no answer for
2005.
Many thanks,
Larry.
Hello Larry,
Do two searches, we also include the job title in the content data. This
is to because the searhc has to match in at least on column, not across columns.
i.e. a search for SQL AND server AND DBA with column title = "SQL DBA" and
content of "SQL Server" would not return as result unless you combined the
data.
SELECT ftt.RANK, [content].id, [content].title, [content].content_data
> FROM [content]
left JOIN CONTAINSTABLE([content], (title), 'Summary') as fttitle ON fttitle.[key]=[content].id
INNER JOIN CONTAINSTABLE([content], (content_data), 'Summary') as ftt ON
ftt.[key]=[content].id
ORDER BY
fttitle.rank desc, ftt.rank desc

>
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

> Hi there,
> Is it possible with SQL Server 2005 to add weighting to a column in a
> CONTAINSTABLE or FULLTEXTTABLE ?
> I've got the statment :
> SELECT ftt.RANK, [content].id, [content].title, [content].content_data
> FROM [content] INNER JOIN CONTAINSTABLE([content], (title,
> content_data), 'Summary') as ftt ON ftt.[key]=[content].id ORDER BY
> ftt.rank desc
> What I need to do is ensure that matches in the title column have much
> higher ranking than matches in content_data.
> I've seen similar posts, but have not seen a definitive yes/no answer
> for 2005.
> Many thanks,
> Larry.
|||Hi Simon,
Thanks for the reply.
Does having multiple joins have a big impact on performance ?
Would I need to have another join for every field I wanted to search on in
the table ?
Is there anyway of combining the rankings ?
Many thanks,
Larry.
"Simon Sabin" <SimonSabin@.noemail.noemail> wrote in message
news:62959f1a50b1e8c92d61906228a8@.msnews.microsoft .com...
> Hello Larry,
> Do two searches, we also include the job title in the content data. This
> is to because the searhc has to match in at least on column, not across
> columns. i.e. a search for SQL AND server AND DBA with column title = "SQL
> DBA" and content of "SQL Server" would not return as result unless you
> combined the data.
> SELECT ftt.RANK, [content].id, [content].title, [content].content_data
> left JOIN CONTAINSTABLE([content], (title), 'Summary') as fttitle ON
> fttitle.[key]=[content].id
> INNER JOIN CONTAINSTABLE([content], (content_data), 'Summary') as ftt ON
> ftt.[key]=[content].id ORDER BY
> fttitle.rank desc, ftt.rank desc
>
> Simon Sabin
> SQL Server MVP
> http://sqlblogcasts.com/blogs/simons
>
>
|||Here is an example:
SELECT [KEY], SUM(Rank) AS WeightedRank
FROM
(
SELECT Rank * 5.0 as Rank, [KEY] from FREETEXTTABLE(listing,
Address, 'Street')
UNION
select Rank * 3.0 as Rank, [KEY] from FREETEXTTABLE(listing,
Realtor, 'Street')
UNION
select Rank * 1.0 as Rank, [KEY] from FREETEXTTABLE(listing,
Notes, 'Street')
) as x
GROUP BY [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
"Larry Neylon" <larry@.senior.removethis.co.uk> wrote in message
news:uFfhHyzXHHA.4692@.TK2MSFTNGP04.phx.gbl...
> Hi there,
> Is it possible with SQL Server 2005 to add weighting to a column in a
> CONTAINSTABLE or FULLTEXTTABLE ?
> I've got the statment :
> SELECT ftt.RANK, [content].id, [content].title, [content].content_data
> FROM [content] INNER JOIN CONTAINSTABLE([content], (title, content_data),
> 'Summary') as ftt ON ftt.[key]=[content].id ORDER BY ftt.rank desc
> What I need to do is ensure that matches in the title column have much
> higher ranking than matches in content_data.
> I've seen similar posts, but have not seen a definitive yes/no answer for
> 2005.
> Many thanks,
> Larry.
>
|||Hello Larry,
It depends on the searches and the data in the index. It can do.
But generally the job title will be small and so should be a quck search.
We combine the rankings by in my example doing fttitle.RANK * 100 + ftt.RANK.
This means that a match in the job title will always rank above a match in
the content. This is because rank goes from 0-100
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
[vbcol=seagreen]
> Hi Simon,
> Thanks for the reply.
> Does having multiple joins have a big impact on performance ?
> Would I need to have another join for every field I wanted to search
> on in the table ?
> Is there anyway of combining the rankings ?
> Many thanks,
> Larry.
> "Simon Sabin" <SimonSabin@.noemail.noemail> wrote in message
> news:62959f1a50b1e8c92d61906228a8@.msnews.microsoft .com...
|||Thanks for that Hilary,
This appears to be pretty close to what I'm after.
The only question I now have is how best to return the actual data in one
SQL statement
The following would give me what I need, but is this the most efficient way
of achieving this ?
SELECT [KEY], SUM(RANK) AS WeightedRank, content.title, content.abstract
FROM
(
SELECT Rank * 5.0 as Rank, [KEY] from FREETEXTTABLE(content, (title,
keywords), 'Content')
UNION
select Rank * 3.0 as Rank, [KEY] from FREETEXTTABLE(content,(abstract),
'Content')
UNION
select Rank * 1.0 as Rank, [KEY] from FREETEXTTABLE(content,(content_data),
'Content')
) AS x INNER JOIN content ON content.id = [KEY]
GROUP BY [KEY], content.title, content.abstract
ORDER BY WeightedRank DESC
Many thanks,
Larry.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23c$xVODYHHA.984@.TK2MSFTNGP04.phx.gbl...
> Here is an example:
> SELECT [KEY], SUM(Rank) AS WeightedRank
> FROM
> (
> SELECT Rank * 5.0 as Rank, [KEY] from FREETEXTTABLE(listing,
> Address, 'Street')
> UNION
> select Rank * 3.0 as Rank, [KEY] from FREETEXTTABLE(listing,
> Realtor, 'Street')
> UNION
> select Rank * 1.0 as Rank, [KEY] from FREETEXTTABLE(listing,
> Notes, 'Street')
> ) as x
> GROUP BY [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
>
> "Larry Neylon" <larry@.senior.removethis.co.uk> wrote in message
> news:uFfhHyzXHHA.4692@.TK2MSFTNGP04.phx.gbl...
>

CONTAINS vs. CONTAINSTABLE performance

We are implementing an FTS to support google-like drop down boxes.
When I originally constructed my query using a "Contains" parameter,
performance was in the neighborhood of 4 seconds. However, when we
implemented CONTAINSQUERY, performance was on-par with our
expectations (far less than a second).
The table being searched contains < 500 rows total, and the final
results set is < 10 rows at this juncture. But this is just on a
development database, and ultimately we expected thousands of rows
instead of hundreds.
Can anyone tell me why the performance is so dramatically different
and is there something common in the building of FTS queries that we
could do differently?
I'd post the queries, but really there is no significant difference
between them and the example queries except that there are multiple
tables involved.
Thanks!
Both of them can produce very different execution plans. Containstable also
allows you to limit your results set to a definite amount which has
performance benefits.
"Kerry" <maclean.kerry@.gmail.com> wrote in message
news:1177958407.967589.256680@.l77g2000hsb.googlegr oups.com...
> We are implementing an FTS to support google-like drop down boxes.
> When I originally constructed my query using a "Contains" parameter,
> performance was in the neighborhood of 4 seconds. However, when we
> implemented CONTAINSQUERY, performance was on-par with our
> expectations (far less than a second).
> The table being searched contains < 500 rows total, and the final
> results set is < 10 rows at this juncture. But this is just on a
> development database, and ultimately we expected thousands of rows
> instead of hundreds.
> Can anyone tell me why the performance is so dramatically different
> and is there something common in the building of FTS queries that we
> could do differently?
> I'd post the queries, but really there is no significant difference
> between them and the example queries except that there are multiple
> tables involved.
> Thanks!
>
|||We did some internal testing on the indexed-table using both CONTAINS
and CONTAINSTABLE. In both cases, the execution plan was virtually
identical, and when querying just the single table, performance is
fast.
However, when used in our live query, which joins 9 tables from two
different databases, the CONTAINSTABLE join maintains the original
speed, but CONTAINS takes the 4 seconds I mentioned. I used SET
SHOWPLAN_ALL when running the queries both ways, and there seems to be
a negligible difference in the execution plans, although the
CONTAINTABLE plan is marginally faster than CONTAINS.
The bottom line is that this is a very dramatic difference and if we
could understand why the performance difference between the two, we
would be much more comfortable taking this to our production machines.
Thanks for both your responses!

CONTAINS clause with multi-word "AND" inflectional searching?? help...

What if you want to search using FTS with AND logic using the FORMSOF(inflectional,...) inside the CONTAINS() clause?

if my search phrase is "light hearted" I can easily do an OR search using the following in my where clause:
CONTAINS(Colname,'formsof(INFLECTIONAL,light,heart ed).
but the and is far more tricky...

does anyone know how to do this without having multiple Contains statements (which greatly increases overhead)?

I know that I can use AND in a straight contains like so:
CONTANS(column, '"light" AND "hearted"') but this does not allow me to explore inflectional variations on the words...

nesting multiple FORMSOF's doesn't seem to work either like so:
contains(column,'"formsof(inflectional,light)"' AND 'formsof(inflectional,hearted)"')

anyone else found how to do this?just to clarify I think 2 better search words for my ex. would have been "sport" and "award" and it's really important that I get results for Sports, Sported, Awards, and Awarded. Then I'd have any combination of the two words Inflectional variations (one from each root word) that exist in the same record returned in my resultset.

Contains 2 Column and / or FTS

Thanks for any help!
SQL 2000 Standard Edition with SP3. The title and document column are both
Full text indexed. The problem I'm trying to solve is how to get the below
stored procedure to search BOTH columns for the criteria entered.
Search Example:
"pin*" or "Yello*" Returns 2 rows as desired.
"pin*" and "yello*" Returns no results which is not desired.
However "Pin*" and "Gree*" does return results which is also desired.
I'm trying to build a procedure which will pull matched words from BOTH
columns for both the "AND" and "OR" operators. Maybe it's the way the FTS
work in Microsoft, but currently when entering the "pin*" and "yello*"
search, it looks for both those terms in the SAME colum, and will not join
them so to speak.
A FreeText search would always return me the desired results, except it
does not seem to do partial term searches or wildcards, unless I'm missing
something.
I've found and seen quite a few bits of code on the internet such as this
http://www.experts-exchange.com/Data...20705253.html, but they don't seem to quite fit my needs.
Thanks a million!!
CREATE TABLE [dbo].[Document] (
[DocumentID] [int] IDENTITY (1, 1) NOT NULL ,
[Title] [varchar] (100) ,
[Submitter] [varchar] (100) NOT NULL ,
[RevisedBy] [varchar] (100) NOT NULL ,
[Document] [text] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
insert into document(Title, submitter, Revisedby, document) values ('Pink
Green',
'John Henry', 'Steven Smith', 'Orange')
insert into document(Title, submitter, Revisedby, document) values ('Silver
Blue',
'Kelly Owen', 'John Henry', 'Yellow')
GO
ALTER PROCEDURE SearchDoc2
(
@.Word varchar (300) = Null,
@.SubmitterID varchar (100) = NULL,
@.systemID varchar (100) = NULL
)
as
SELECT Title,
SubmitterID,
RevisedBy
FROM tblDocument
where (Contains(*, @.word) or @.Word IS NULL)
and
(systemID = @.SystemID or @.SystemID IS Null) and
((SubmitterID = @.SubmitterID or @.SubmitterID IS NULL) or
(RevisedBy = @.SubmitterID or @.SubmitterID IS NULL) )
Jeff,
Have you considered using CONTAINSTABLE vs. CONTAINS? If not, please review:
SQL Server FTS across multiple tables or columns
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!316.entry
Below are modified code examples from the above blog entry to demonstrate
how to handle both the AND or OR operators. Note, you and use dynamic SQL
code in the WHERE clause to handle both conditions in one stored proc. Also,
keep in mind that with the use of two CONTAINSTABLE clauses, this may not
perform well against large (>1 million rows) FT-enabled tables as each
CONTAINSTABLE clause is a full "round-trip" to the FT Catalog.
use Northwind
SELECT e.LastName
from Employees AS e,
containstable(Employees, Notes, 'ISABOUT (BA weight (.2) )') as A,
containstable(Employees, Title, 'Sales') as B
where
A.[KEY] = e.EmployeeID and -- Note, AND operator.
B.[KEY] = e.EmployeeID
-- vs.
use Northwind
SELECT distinct e.LastName
from Employees AS e,
containstable(Employees, Notes, 'ISABOUT (BA weight (.2) )') as A,
containstable(Employees, Title, 'Sales') as B
where
A.[KEY] = e.EmployeeID or -- Note, OR operator.
B.[KEY] = e.EmployeeID
For the OR operator, you will need to add the DISTINCT operator to prevent
dups in your results.
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:F5A5A066-BE17-43CC-83E3-E65E6270AC99@.microsoft.com...
> Thanks for any help!
> SQL 2000 Standard Edition with SP3. The title and document column are
> both
> Full text indexed. The problem I'm trying to solve is how to get the
> below
> stored procedure to search BOTH columns for the criteria entered.
> Search Example:
> "pin*" or "Yello*" Returns 2 rows as desired.
> "pin*" and "yello*" Returns no results which is not desired.
> However "Pin*" and "Gree*" does return results which is also desired.
> I'm trying to build a procedure which will pull matched words from BOTH
> columns for both the "AND" and "OR" operators. Maybe it's the way the FTS
> work in Microsoft, but currently when entering the "pin*" and "yello*"
> search, it looks for both those terms in the SAME colum, and will not join
> them so to speak.
> A FreeText search would always return me the desired results, except it
> does not seem to do partial term searches or wildcards, unless I'm missing
> something.
> I've found and seen quite a few bits of code on the internet such as this
> http://www.experts-exchange.com/Data...20705253.html,
> but they don't seem to quite fit my needs.
> Thanks a million!!
> CREATE TABLE [dbo].[Document] (
> [DocumentID] [int] IDENTITY (1, 1) NOT NULL ,
> [Title] [varchar] (100) ,
> [Submitter] [varchar] (100) NOT NULL ,
> [RevisedBy] [varchar] (100) NOT NULL ,
> [Document] [text] NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> insert into document(Title, submitter, Revisedby, document) values ('Pink
> Green',
> 'John Henry', 'Steven Smith', 'Orange')
> insert into document(Title, submitter, Revisedby, document) values
> ('Silver
> Blue',
> 'Kelly Owen', 'John Henry', 'Yellow')
> GO
>
> ALTER PROCEDURE SearchDoc2
> (
> @.Word varchar (300) = Null,
> @.SubmitterID varchar (100) = NULL,
> @.systemID varchar (100) = NULL
> )
> as
>
> SELECT Title,
> SubmitterID,
> RevisedBy
>
> FROM tblDocument
> where (Contains(*, @.word) or @.Word IS NULL)
> and
> (systemID = @.SystemID or @.SystemID IS Null) and
> ((SubmitterID = @.SubmitterID or @.SubmitterID IS NULL) or
> (RevisedBy = @.SubmitterID or @.SubmitterID IS NULL) )
>
>
|||Thanks for the reference John.
I read it over well and tried the examples which I've somewhat tried before.
Aparently when using the [and] operator with contains[table], it looks for
both "words" to exist in the same column, and not scross columns.
I think perhaps the FreeTextable will suit our needs, it's just a shame that
it does not support wildcards yet.
Thanks,
Jeff
"John Kane" wrote:

> Jeff,
> Have you considered using CONTAINSTABLE vs. CONTAINS? If not, please review:
> SQL Server FTS across multiple tables or columns
> http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!316.entry
> Below are modified code examples from the above blog entry to demonstrate
> how to handle both the AND or OR operators. Note, you and use dynamic SQL
> code in the WHERE clause to handle both conditions in one stored proc. Also,
> keep in mind that with the use of two CONTAINSTABLE clauses, this may not
> perform well against large (>1 million rows) FT-enabled tables as each
> CONTAINSTABLE clause is a full "round-trip" to the FT Catalog.
> use Northwind
> SELECT e.LastName
> from Employees AS e,
> containstable(Employees, Notes, 'ISABOUT (BA weight (.2) )') as A,
> containstable(Employees, Title, 'Sales') as B
> where
> A.[KEY] = e.EmployeeID and -- Note, AND operator.
> B.[KEY] = e.EmployeeID
> -- vs.
> use Northwind
> SELECT distinct e.LastName
> from Employees AS e,
> containstable(Employees, Notes, 'ISABOUT (BA weight (.2) )') as A,
> containstable(Employees, Title, 'Sales') as B
> where
> A.[KEY] = e.EmployeeID or -- Note, OR operator.
> B.[KEY] = e.EmployeeID
> For the OR operator, you will need to add the DISTINCT operator to prevent
> dups in your results.
> Hope that helps!
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Jeff" <Jeff@.discussions.microsoft.com> wrote in message
> news:F5A5A066-BE17-43CC-83E3-E65E6270AC99@.microsoft.com...
>
>
|||Jeff wrote on Mon, 19 Sep 2005 11:19:06 -0700:

> Thanks for the reference John.
> I read it over well and tried the examples which I've somewhat tried
> before.
> Aparently when using the [and] operator with contains[table], it looks for
> both "words" to exist in the same column, and not scross columns.
> I think perhaps the FreeTextable will suit our needs, it's just a shame
> that it does not support wildcards yet.
You can achieve your result using Contains, but it requires more processing:
select * from mytable where contains(*,'"pin*"') and contains(*,'"yellow*"')
This will find all rows with pin* in the FTS index, and all rows with
yello*, and then combine them together to find all rows that have both terms
in either column.
However, from a performance point of view this may not be satisfactory as
there is an FTS search for each word rather than all at once. In my own
system I maintain an extra column that is a contenation of the 6 columns
that I use in my searches - so if I want a search just on book titles I
would use Contains(title,@.words) but if I wanted the words to appear in any
columns then I'd use Contains(keywords,@.words). It does mean that when data
is changed the keywords field needs to be rebuilt, but all data is processed
from files via an in-house application so this is done automatically.
Additional storage is required for the extra column, but I'd rather use up a
bit more of my disk space than have slow FTS results (disk space is after
all relatively cheap compared to the perceived performance of a web site
search by customers).
Dan