Replies to post #38185 on The Question and Answer Board (IHUB)

[jhalada]

Bob,

I have not really done much lately with full text searches on SQL Server I have done some work on the topic years ago with dedicated a product (programmers' library) for a static sets (with infrequent updates), and it seemed amazingly efficient.

But I guess a dynamic, (constantly updating set) faces different set problems.

I don't want to take too much of your time, but if you have some time, what is the bottleneck in this situation (22M messages that taking up, say 100GB, excluding indexes, SQL server, 8GB of memory).

I guess adding records causes some overhead, I guess most of te overhead is in performing searches, as each search needs a good chunk of memory while running, and they consume resources (disk, memory). Each search probably takes more resources than 10 or 20 users just reading / posting the whole day.

I guess the Holy Grail would be to have the entire database, (plus indexes plus some margin for interim uses of data) in memory. on one big monolythic server (which would not require much additional programming, but the cost of such server is prohibitive).

Joe

PS: Looking into Opteron is worthwile, IMO, especially now that Intel has validated its instruction set as a path to 64 bit for volume server market. But, you will not be able to buy one from Dell, as Dell is the last major server vendor not offering Opteron.

[jhalada]

PS on that Search for SI:

If limiting the features would make things easier, I think many would be willing to sacrifice the search on the entire database. Search on thread or on a person would be sufficient.

Joe