Define population order

Hi,
Is it possible to define a specific order for full-text data to be
populated ? I've got a few hundred thousands rows indexed that can be
completely renewed sometimes... When this "big update" happens, I
always rebuild the FT index, to avoid delivering inaccurate results
(as almost all the key changed), and start a full population.
In these rows, some of them (15%) contains almost all the revelant
information, and all the others (85%) contains only complementary
information. Is there a way to force the FT index to index the 15%
interesting rows first, and then to complete with the 85%. That way, I
could deliver accurate information asap...
Does someone knows how FT determines the order to populate data in the
index ? I tried to modify the data order in the table where FT is
enabled, but it seems to be useless..
Thanks,
Fred
Not really. The crawl or population is done on a per table basis. For both
the incremental and full population it starts with row 1 and then progresses
to the last rows.
Your only solution would be to partition the table into two or more tables
and then you can schedule the population on a per table basis.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Fred" <flaignel@.yahoo.fr> wrote in message
news:fd23b8be.0408152032.617400c4@.posting.google.c om...
> Hi,
> Is it possible to define a specific order for full-text data to be
> populated ? I've got a few hundred thousands rows indexed that can be
> completely renewed sometimes... When this "big update" happens, I
> always rebuild the FT index, to avoid delivering inaccurate results
> (as almost all the key changed), and start a full population.
> In these rows, some of them (15%) contains almost all the revelant
> information, and all the others (85%) contains only complementary
> information. Is there a way to force the FT index to index the 15%
> interesting rows first, and then to complete with the 85%. That way, I
> could deliver accurate information asap...
> Does someone knows how FT determines the order to populate data in the
> index ? I tried to modify the data order in the table where FT is
> enabled, but it seems to be useless..
> Thanks,
> Fred
|||> Not really. The crawl or population is done on a per table basis. For both
> the incremental and full population it starts with row 1 and then progresses
> to the last rows.
Ok thanks Hilary for the reply. I've made some tests and here are some
interesting results. The population starts on row 1 and then
progresses to the last rows only if you don't have any integer indexes
(standard indexes, not FT ones) defined on the table. If you've got
indexes on int columns, it takes the last one to determine the crawl
order...
It means that if you want to specify the order the index should be
populated, it is possible to create a int column 'priority', for
example with values from 1 to 100 and create an index on it (it should
be the last one listed in enterprise manager). The sorting method of
the index can also reverse the crawl order !
So that's great, but I can't figure out why Microsoft doesn't specify
more about this : it could be useful in many scenarios.
Fred
|||"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:%23oKC%23d4gEHA.3536@.TK2MSFTNGP12.phx.gbl...
> Not really. The crawl or population is done on a per table basis. For both
> the incremental and full population it starts with row 1 and then
progresses
> to the last rows.
>
What defines "row 1" I assume the primary key used as part of the FT?
Does setting this with ASC or DESC make any difference?

> Your only solution would be to partition the table into two or more tables
> and then you can schedule the population on a per table basis.
>
|||Very interesting research Fred!
Is it possible that this last index is the unique index that you have
created for Full Text Indexing?
The way it works is that SQL FTI issues a
exec sp_fulltext_getdata 5, 1223675407
where 5 is the db_id and the number is the number in sysobjects that
corresponds to the object_id of the table or id in sysobjects.
This call returns a row set which is stored in ram and it is ordered by
whatever index you are using as your unique index, and this is the order in
which rows are extracted AFAIK.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Fred" <flaignel@.yahoo.fr> wrote in message
news:fd23b8be.0408161433.20a75ae7@.posting.google.c om...[vbcol=seagreen]
both[vbcol=seagreen]
progresses
> Ok thanks Hilary for the reply. I've made some tests and here are some
> interesting results. The population starts on row 1 and then
> progresses to the last rows only if you don't have any integer indexes
> (standard indexes, not FT ones) defined on the table. If you've got
> indexes on int columns, it takes the last one to determine the crawl
> order...
> It means that if you want to specify the order the index should be
> populated, it is possible to create a int column 'priority', for
> example with values from 1 to 100 and create an index on it (it should
> be the last one listed in enterprise manager). The sorting method of
> the index can also reverse the crawl order !
> So that's great, but I can't figure out why Microsoft doesn't specify
> more about this : it could be useful in many scenarios.
> Fred
|||I believe its the first row ordered by the unique index that SQL FTS
requires.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:9XeUc.17$2s.14@.twister.nyroc.rr.com...[vbcol=seagreen]
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:%23oKC%23d4gEHA.3536@.TK2MSFTNGP12.phx.gbl...
both[vbcol=seagreen]
> progresses
> What defines "row 1" I assume the primary key used as part of the FT?
> Does setting this with ASC or DESC make any difference?
>
tables
>
|||Hilary, I *believe* you meant to say the first and only column in the
single, non-nullable column required for the 'regular' unique index that SQL
FTS requires.
Fred, I would agree with Hilary that this is most interesting research!
Could you provide more details about your table's structure via the
following SQL script?
use <your_database_name_here>
go
EXEC sp_help_fulltext_catalogs
EXEC sp_help_fulltext_tables
EXEC sp_help_fulltext_columns
EXEC sp_help <your_FT-enable_table_name_here>
go
Thanks,
John
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:uK9$xTGhEHA.2684@.TK2MSFTNGP10.phx.gbl...
> I believe its the first row ordered by the unique index that SQL FTS
> requires.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in
message
> news:9XeUc.17$2s.14@.twister.nyroc.rr.com...
> both
> tables
>