The Question and Answer Board

[jhalada]

Bob,

I wonder how Microsoft implements their text search if it the performance is so poor. Did they make any improvements in Yukon?

I think the problem is that the text seart and the rest of the SQL server are not integrated, text search is a bit of an add-on, and that they don't interact, until the very last moment.

I think the way to roll your own would involve building additional table with structure like this:

message_id id
text_string char or varchar

with index on text_string

This table would give you an inclusion of a text_string in the message, excluding multiple instances. The table would be huge. What's the average length of the message? 50 unique words, excluding "The", "a" etc.? I guess 20M x 50 = 1 billion records is not pretty. The size of data would be approximately 15 bytes, or 15GB, plus a similar size for index.

I guess you could test the response time. It should not be too bad, as the binary search in the index should be fast )unless SQL server for some reason decides not to use it).

I should work fine for something like Expression AND Expression etc. For more complicated queries, a bitmap index manipulation (something like what Sybase IQ server does) would be needed.

Anyway, having the text search part of the same SQL expression that could determine membership on criteria other than text (thread Id, date range etc.) with the rest of text search like statement could introduce some efficiencies (By SQL server engine).

Anyway, just some ideas...

Joe