What are the tradeoffs for using CSharpQuery?

Before you go tearing off to download CSharpQuery and rip out SQL Server FTE, there are some tradeoffs to using this system.

Memory Resident

CSharpQuery is currently Memory Resident where I don’t believe SQL Server FTE does that. In the case of my initial imlementation it only resulted in ~16MiB index per language. That entire index would stay in memory. We had 32GiB of memory on that system so that isn’t such a big deal even with the pretty deep set of data indexed. But if you had 100 million or more then this may not be a good solution for you.

Index Creation Time

CSharpQuery is also slower at creating the index. It seems like the index creation for FTE was almost instantaneous where it takes about 10 seconds per language to build mine. Again, not a big deal in most cases but could be problematic for very large data sets.

Initial Index Load

Because CSharpQuery Indexes are memory resident the first time someone does a query the index file will have to be loaded into memory. The larger the index the longer the wait for the first query to return. This could create problems if you start and stop your system frequently or the query is only occationally used and IIS recycles the worker process.

Integration with SQL Server

FTE is able to provide the data right to you in T-SQL and is easy to setup and consume. CSharpQuery will merely give you a list of indexes and ranking info and you are expected to get the data into SQL Server yourself. Although, this does mean that it can be used outside of SQL Server which is a plus!


SQL Server FTE has a nifty little feature in FREETEXTTABLE or CONTAINSTABLE/FORMSOF where it can iliterate the different inflections of a verb so "run" becoms "run, ran, running". I belive this is one of the reasons my queries were slow in SQL Server FTE, but it is pretty neat! You can of course put these in the thesaurus but you would need to have them all in there and that isn't vey practicle.

Last edited Oct 16, 2009 at 6:55 PM by NathanZaugg, version 2


No comments yet.