FullText Search data model for speed

Which method is faster for full-text search
One row big varchar field
comment VARCHAR(1000)
on lots of small varchar fieds
like 10 rows comment VARCHAR(100)
Message posted via http://www.sqlmonster.com
Kuido,
Could you post the full output of the below SQL code as this is very helpful
to understanding your environment as well as troubleshooting SQL FTS issues
as both SQL Server version and the OS platform play a part in FTS
performance tuning:
use <your_database_name>
SELECT @.@.version
SELECT @.@.language
SELECT count(*) from <your_true_FT-enabled_table_name>
The biggest factor in both FT Indexing and FT Searching is the number of
rows in your FT-enable table. Specifically, for a table with one row of
large text will be just as fast as 10 rows with smaller text. Furthermore,
in this situation (1 row table vs. 10 row table), T-SQL LIKE will be faster
as with very small tables all the rows will fit in one or a couple of data
pages, while the CONTAINS FTS queries will have to use the external MSSearch
service.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Kuido K?lm via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:35824a78f71441eda1460869a906ea07@.SQLMonster.c om...
> Which method is faster for full-text search
> One row big varchar field
> comment VARCHAR(1000)
> on lots of small varchar fieds
> like 10 rows comment VARCHAR(100)
> --
> Message posted via http://www.sqlmonster.com
|||SQL FTS query performance is most sensitive to the number of rows returned
in a query. So if you can limit the number of rows returned you will get
better performance. So 1 big varchar field would probably offer better
performance.
However if you can partition your table into sub tables, you will get even
better performance this way as long as you are only doing a single hit on
MSSearch.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Kuido K?lm via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:35824a78f71441eda1460869a906ea07@.SQLMonster.c om...
> Which method is faster for full-text search
> One row big varchar field
> comment VARCHAR(1000)
> on lots of small varchar fieds
> like 10 rows comment VARCHAR(100)
> --
> Message posted via http://www.sqlmonster.com
|||I'm just planning database application
SQL-server will be Microsoft SQL Server 2000
Language - eesti (Estonian)
and there will be 15 000 000 rows in database
Message posted via http://www.sqlmonster.com
|||Kuido,
Then you should review all the SQL FTS links and resources at:
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.entry
You should also review SQL Server 2000 Books Online (BOL) and using the
search tab, search on "full text" (with the double quotes) and especially
the BOL title: "Full-text Search Recommendations". Additional, since your
language will be Estonian, you will need to use the "neutral wordbreaker" as
Estonian is not one of the subset of languages supported by SQL FTS.
Specifically, for each of your FT-enabled columns, set the "Language for
Word Breaker" to Neutral.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Kuido K?lm via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:677e90c6408e4927ad6a290c05d61745@.SQLMonster.c om...
> I'm just planning database application
> SQL-server will be Microsoft SQL Server 2000
> Language - eesti (Estonian)
> and there will be 15 000 000 rows in database
> --
> Message posted via http://www.sqlmonster.com