Microsoft Indexing Servces with SQL Server 2000

[tbke]

Hi,
i have a large database and manuy fields I am searching through contain large chunks of text. Searching for specific words or set of words in these fields slows down my response time considerably despite the fact that all these fields are already indexed in my database. I also scheduled an incremental index on them but the query still takes too long.
Mu question is: can i use Microsoft Indexing Services in combination with the SQL server in order to create catalogs from the database fields which I am using in my queries?
And if yes how woudl I do that? from the SQL console?
Thank you in advance.

Tibi

 

[miq]

hi,

An additional service comes with ms sql server called mssearch. Main purpose of this service is to provide fast serarching service to ms products inclusing sql server. Accoring to the options you selected while installing sql server you may have to install search services through sql server setup cd.

Serach service needs catalog which in tern need unique index (with no null values acceptance) to be created on any column of a table on which you want to use search services. Seach column can be different from the column that have unique index. Besides this you have to populate catalog with data so seaching can be done using mssearch service. mssearch service should be running.

learn more about CONTAINS/CONTAINSTABLE , FREETEXT/FREETEXTTABLE.

also
select anytable and right click it in enterprise manager and select full-text index table. Step through the wizard for better understanding.

I hope enough details are provided here for a quick start

bye
miq

 

[miq]

hi,

An additional service comes with ms sql server called mssearch. Main purpose of this service is to provide fast serarching service to ms products inclusing sql server. Accoring to the options you selected while installing sql server you may have to install search services through sql server setup cd.

Serach service needs catalog which in tern need unique index (with no null values acceptance) to be created on any column of a table on which you want to use search services. Seach column can be different from the column that have unique index. Besides this you have to populate catalog with data so seaching can be done using mssearch service. mssearch service should be running.

learn more about CONTAINS/CONTAINSTABLE , FREETEXT/FREETEXTTABLE.

also
select anytable and right click it in enterprise manager and select full-text index table. Step through the wizard for better understanding.

I hope enough details are provided here for a quick start
good luck !!!
Happy Serarchi...

bye
miq

 

[tbke]

Hi there,

thanks for the sufggestion. I am currently using the table indexing and the transact predicates CONTAINS...
I also created a field in my table called "timestamp" and i gave it the timestamp datatype. I know this field will be required when an incremental indexing is done on on my table every hour.
I have created this field when my table had already a large number of records. When I now apply the incremental indexing this field does not show a dat/time value in it. Instead is populated with the value '<binary>'.
I asume this isn't what it should be, hence the indexing is taking very long. To my knowlege the incremental indexing based on this timestamp field should run much faster.

Any suggestions on how this 'timestamp' need be set? Why does my indexing takes this long.

Thnk you

tbke

 

[miq]

well,

I looks as if records already present before implementing timestamp field are causing some sort of problem.

There are two things can do.
1) run DBCC command


DBCC CHECKDB
DBCC REINDEX ( for table indexes only)
It may help by tweaking database objects including currently present indexes and tables.
However, data in timestamp field will not be updated.

2) create temporaty table (worktable) polulate it with data from all fields except timestamp. truncate table and repopulate it with the data in worktable.

or you can simply export data to new table using bcp or DTS

bye
miq

 

[SQLSister]

OK first, timestamp should not show a date and time; it is not, despite the name, a datetime field. When it says binary that is correct.

So that isn't likely to be your problem. However, if you allowed nulls when creating the field perhaps it did not insert a binary value into the existing records?

How many records do you have? The initial population of the catalog should take quite awhile if you have a lot of records. There are some good suggestions for things to do when using full-text search to make your system operate better. Look in books online under Full Text search, best practices.