Difficulty in implementing

How difficult is it to implement the full text searching capability provided
by SQL server if it wasn't implemented to start with? Currently I have an
application that allows searching based on user profile fields. However
it's not taking advantage of the full text indexing.
How much code change is involved? Thanks.
Very little code changes will be required.
First you need to create a catalog and build the index for the column you
wish to index.
Then you add a contains or freetext clause to your where query, ie:
select col1, col2, col3 from tablename where contains(IndexedColumnName,
'SearchPhrase').
For performance reasons you will want to limit your results set and use
ContainsTable, and possibly put your data you are indexing in a child table.
"Shabam" <blislecp@.hotmail.com> wrote in message
news:_sGdnbj6K8tAqODcRVn-iA@.adelphia.com...
> How difficult is it to implement the full text searching capability
provided
> by SQL server if it wasn't implemented to start with? Currently I have an
> application that allows searching based on user profile fields. However
> it's not taking advantage of the full text indexing.
> How much code change is involved? Thanks.
>
|||Shabam,
That depends upon several factors. What is the exact version & OS platform
that you have SQL Server installed? Could you post the full output of --
SELECT @.@.version -- as this is helpful in understanding your environment.
Could you post the output of the following SQL code?
SELECT FullTextServiceProperty('IsFulltextInstalled')
If the Full-text Search (FTS) components are installed (as they are
installed by default for SQL Server 2000, but not for SQL Server 7.0), you
can use the Full-Text Indexing Wizard (sqlftwiz.exe) and launch this wizard
from the Enterprise Manager and it will walk you though all the necessary
steps for FT Indexing your tables. You should also review Books online (BOL)
and using the search tab, search on "full text" (with the double quotes) as
well as search on CONTAINS and FREETEXT for more information on using FTS.
Regards,
John
"Shabam" <blislecp@.hotmail.com> wrote in message
news:_sGdnbj6K8tAqODcRVn-iA@.adelphia.com...
> How difficult is it to implement the full text searching capability
provided
> by SQL server if it wasn't implemented to start with? Currently I have an
> application that allows searching based on user profile fields. However
> it's not taking advantage of the full text indexing.
> How much code change is involved? Thanks.
>
|||> First you need to create a catalog and build the index for the column you
> wish to index.
> Then you add a contains or freetext clause to your where query, ie:
> select col1, col2, col3 from tablename where contains(IndexedColumnName,
> 'SearchPhrase').
> For performance reasons you will want to limit your results set and use
> ContainsTable, and possibly put your data you are indexing in a child
table.
Why should the data be put in a child table? How does that help with
performance?
Right now, I have a "Users" table. It contains various profile fields.
Some of the fields contain comma-delimited list items (hobbies, separated by
commas). When viewing the user's profile, these hobby items are displayed
and linked to a search, item by item.
I suggested to the programmer it would be much better to put the individual
hobby items in a separate table, linked with an index of the user ID.
However he's saying it would cause more overhead during the display of the
user profile page. What are your thoughts? (I know this is a tangeant from
the original question).
|||Shabam,
Actually, it is not necessary to put the data in a child table and this
alone will not help performance and your programmer is correct as it would
cause more overhead during the display of the user profile page. Depending
upon the number of rows (see below sql code), it is best to keep everything
in one table.
What is important is what is your SQL Server version and determine if the
Full-text Search (FTS) components are installed. Could you post the full
output of the following sql code:
use <your_database_name_here>
go
SELECT @.@.version
SELECT FullTextServiceProperty('IsFulltextInstalled')
SELECT count(*) from Users
EXEC sp_help Users
go
The above info will give me a better understanding of your environment and
allow me to reply with the correct information and estimates of performance
as well as the code necessary to implement FTS. Depending upon the OS
platform (from @.@.version), there may be issues with FTS queries against
column data with comma-delimited list items, unless you use the Neutral
"Language for Word Breaker" or are have SQL Server (version?) installed on
Window Server 2003 or Windows XP.
Thanks,
John
"Shabam" <blislecp@.hotmail.com> wrote in message
news:vIidnfVIBYAy5eDcRVn-3A@.adelphia.com...[vbcol=seagreen]
you
> table.
> Why should the data be put in a child table? How does that help with
> performance?
> Right now, I have a "Users" table. It contains various profile fields.
> Some of the fields contain comma-delimited list items (hobbies, separated
by
> commas). When viewing the user's profile, these hobby items are displayed
> and linked to a search, item by item.
> I suggested to the programmer it would be much better to put the
individual
> hobby items in a separate table, linked with an index of the user ID.
> However he's saying it would cause more overhead during the display of the
> user profile page. What are your thoughts? (I know this is a tangeant
from
> the original question).
>
>
|||The textual content in the parent table will make the "table wider". If this
textual column is involved in other queries, storing it in a child table
will require the use of a join and you will not get performance
improvements. However, if this textual column is not used in most queries or
to resolve queries, you will get better performance by storing it in a child
table, as your remaining parent table will be considerably narrower and
hence more rows can be stored per database page. Consequently SQL Server
will have to go to disk less to return pages.
Also when you are doing a ContainsTable or FreeTextTable query, you can join
on the parent table and not have to query the child table directly at all.
Depending on the number of rows and the size of your tables the performance
improvement can by huge.
If everytime you need to display a record you have to return the contents on
this field, it probably should be in the parent.
You will have to test to see what works best for you. In my experience on
most of the search applications I have worked on, splitting your textual
data which you are indexing into a seperate table is a must. Your results
may vary.
"Shabam" <blislecp@.hotmail.com> wrote in message
news:vIidnfVIBYAy5eDcRVn-3A@.adelphia.com...[vbcol=seagreen]
you
> table.
> Why should the data be put in a child table? How does that help with
> performance?
> Right now, I have a "Users" table. It contains various profile fields.
> Some of the fields contain comma-delimited list items (hobbies, separated
by
> commas). When viewing the user's profile, these hobby items are displayed
> and linked to a search, item by item.
> I suggested to the programmer it would be much better to put the
individual
> hobby items in a separate table, linked with an index of the user ID.
> However he's saying it would cause more overhead during the display of the
> user profile page. What are your thoughts? (I know this is a tangeant
from
> the original question).
>
>

Different result when using Indexing Service Query Form and SQL Server

When I use the "Indexing Service Query Form", searching documents for
the word "D=E9sir=E9", I get 50 matched records.
If I run the following query from SQL-Server 2005 (on the same machine)
:
SELECT * FROM
OPENQUERY
(CVCATALOG,
'SELECT FILENAME, RANK FROM SCOPE()
WHERE CONTAINS(''"d=E9sir=E9"'')
')
I only get 45 results.
This seems to be linked to the special characters (=E9) since it gives
the same result if I specify a word without accents.
Any idea?
By default IIRC SQL Server 2005 is accent insensitive whereas indexing
services is not. You have the option of making your catalog accent
sensitive.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
<pierre.laduron@.thalesgroup.com> wrote in message
news:1160492048.051686.161080@.i3g2000cwc.googlegro ups.com...
When I use the "Indexing Service Query Form", searching documents for
the word "Dsir", I get 50 matched records.
If I run the following query from SQL-Server 2005 (on the same machine)
:
SELECT * FROM
OPENQUERY
(CVCATALOG,
'SELECT FILENAME, RANK FROM SCOPE()
WHERE CONTAINS(''"dsir"'')
')
I only get 45 results.
This seems to be linked to the special characters () since it gives
the same result if I specify a word without accents.
Any idea?
|||Hello Hilary,
Thanks for your quick reply. This seems to be an interesting track.
Can you tell me how I can make the catalog accent sensitive?
Thanks again,
Pierre
Hilary Cotter wrote:
> By default IIRC SQL Server 2005 is accent insensitive whereas indexing
> services is not. You have the option of making your catalog accent
> sensitive.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> <pierre.laduron@.thalesgroup.com> wrote in message
> news:1160492048.051686.161080@.i3g2000cwc.googlegro ups.com...
> When I use the "Indexing Service Query Form", searching documents for
> the word "D=E9sir=E9", I get 50 matched records.
> If I run the following query from SQL-Server 2005 (on the same machine)
> :
> SELECT * FROM
> OPENQUERY
> (CVCATALOG,
> 'SELECT FILENAME, RANK FROM SCOPE()
> WHERE CONTAINS(''"d=E9sir=E9"'')
> ')
> I only get 45 results.
> This seems to be linked to the special characters (=E9) since it gives
> the same result if I specify a word without accents.
> Any idea?
|||use the alter fulltext catalog statement. Here is an example.
Warning - this will entail a catalog rebuild.
Create database fulltext
GO
create fulltext catalog testcatalog
GO
--this may return an erroneous result - consult
http://support.microsoft.com/?kbid=910067 for more info
select fulltextcatalogproperty('testcatalog','isaccentsen sitive')
GO
select is_accent_sensitivity_on from sys.fulltext_catalogs where
name='testcatalog'
GO
--setting it off
alter fulltext catalog testcatalog
rebuild with ACCENT_SENSITIVITY =OFF
GO
select fulltextcatalogproperty('testcatalog','isaccentsen sitive')
GO
select is_accent_sensitivity_on from sys.fulltext_catalogs where
name='testcatalog'
GO
--setting it on
alter fulltext catalog testcatalog
rebuild with ACCENT_SENSITIVITY =ON
GO
select fulltextcatalogproperty('testcatalog','isaccentsen sitive')
GO
select is_accent_sensitivity_on from sys.fulltext_catalogs where
name='testcatalog'
GO
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
<pierre.laduron@.thalesgroup.com> wrote in message
news:1160557828.399727.117000@.c28g2000cwb.googlegr oups.com...
Hello Hilary,
Thanks for your quick reply. This seems to be an interesting track.
Can you tell me how I can make the catalog accent sensitive?
Thanks again,
Pierre
Hilary Cotter wrote:
> By default IIRC SQL Server 2005 is accent insensitive whereas indexing
> services is not. You have the option of making your catalog accent
> sensitive.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> <pierre.laduron@.thalesgroup.com> wrote in message
> news:1160492048.051686.161080@.i3g2000cwc.googlegro ups.com...
> When I use the "Indexing Service Query Form", searching documents for
> the word "Dsir", I get 50 matched records.
> If I run the following query from SQL-Server 2005 (on the same machine)
> :
> SELECT * FROM
> OPENQUERY
> (CVCATALOG,
> 'SELECT FILENAME, RANK FROM SCOPE()
> WHERE CONTAINS(''"dsir"'')
> ')
> I only get 45 results.
> This seems to be linked to the special characters () since it gives
> the same result if I specify a word without accents.
> Any idea?