Re: Full-Text population
From: Hilary Cotter (hilary.cotter_at_gmail.com)
Date: 09/04/04
- Next message: Vishal Parkar: "Re: Update statment with two databases"
- Previous message: anonymous_at_discussions.microsoft.com: "Re: SQL Question"
- In reply to: Bruce Parker: "Full-Text population"
- Messages sorted by: [ date ] [ thread ]
Date: Sat, 4 Sep 2004 16:45:29 -0400
this question should be posted to the full text newsgroup at
microsoft.public.sqlserver.fulltext.
However to answer your question, both a incremental and full population
retrieve every row from the table(s) you are full text indexing. It then
compares the timestamp of the row with the timestamp the last time the
population was run. It checks to see which rows have a greater timestamp
than this last run timestamp and if the timestamp is greater, the row is
reindexed. It also adjusts the catalog to scavenge rows which have been
deleted.
For a full population what it does is issues a
sp_fulltext_getdata 10, 517576882 where 10 is the catalog id and the large
number is the object_id of the table (the id column in sysobjects).
This call will return the current database timestamp and then the timestamp
for each row and its pk for the table you are indexing.
sp_fulltext_getdata 10, 517576882
0000000000000490
------------------ --------------------------------------
0x000000000000048B 0x3131312D31312D31313131
0x000000000000044F 0x3131312D31312D31313132
0x0000000000000450 0x3131312D31312D31313133
0000000000000490 is the timestamp for the database, 0x000000000000048B is
the timestamp column for the row I am indexing, and 0x3131312D31312D31313131
is the PK value for the first row in the table I am indexing.
After all the results set of all of the timestamps and PK values are
returned to MSSearch (the service that handles the indexing and querying),
MSSearch will another sp_fulltext_getdata this time one time for every row
in your table. It will look like this:
sp_fulltext_getdata 10, 517576882, 0x3131312D31312D31313131,1
So if you have 50 M rows in your database, this procedure will be issued 50
M times.
If you issue an incremental population MSSearch will issue an initial
sp_fulltext_getdata 10, 517576882, 0000000000000490
where 0000000000000490 is the timestamp from the last call, and then issue
sp_fulltext_getdata 10, 517576882, 0x3131312D31312D31313131,1
for each row in the table you are FTI. Again 0x3131312D31312D31313131 is
your primary key value for the first row. So if you have 50 M rows in your
database, this statement will again be issued 50 M times.
Although Incremental population performance is faster in almost all cases
than Full you can see that there for larger tables it can still be quite
time consuming. If you are modifying a large portion (more than 10%) of your
table you should do a full population over an incremental population, as it
will be faster.
HTH
-- Hilary Cotter Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html "Bruce Parker" <bparkerhsd@nospam.nospam> wrote in message news:EF049EF5-BE25-42E9-85C3-F8BFB84A1C2C@microsoft.com... > I am looking for documentation on how population occurs on a table that has a > date/time stamp. Is there a resource that gets into the details of this?
- Next message: Vishal Parkar: "Re: Update statment with two databases"
- Previous message: anonymous_at_discussions.microsoft.com: "Re: SQL Question"
- In reply to: Bruce Parker: "Full-Text population"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
