Is Full-Text Index really that much overhead?

Brian Kitt

I am a developer, and I have a disagreement with my DBA. He has convinced management, that SQL 2005 FullText Index is so much overhead on production, that it should NEVER be used under any circumstances. We have a Cold Fusion site, and somehow he convinced management that a bunch of Cold Fusion developers can create a more efficient full text indexing method than by using SQL 2005 Full Text Index. So now we have to come up with a method for doing this in Cold Fusion.

Is there any statistical data that could possible support or refute his statements

Thanks

Adam D. Turner

Brian,

I feel your pain my friend. Although the DBA is more than partially correct, the overhead will be equivalent regardless of where the query is run. Web Server or SQL Server (Hopefully they are not one in the same otherwise you are not solving a problem, you are relocating it.) That's the decision.

As far as a statisical answer...it's dependant on your hardware/software of the server itself.

Ultimately, you won't know until you actually implement and test.

Adam

Brian Kitt

I can go with 'the overhead would be equal', but I'm hoping for some information to go back and argue that it does not make sense to invent a Cold Fusion 'Full Text Index' when Microsoft has already done that. If we say overhead is equal, then I say go with SQL Full Text Index, then we don't have to maintain the code. However, he is arguing that SQL Full Text Index is SOOOO bad that it makes business sense to invent our own That is the part I am having trouble swallowing.

richbrownesq

I'm not buying what he's selling. Sure, FTI adds overhead but i would doubt that you could write something significantly faster with the same functionality.

There are so many factors that will influence the performance of FTI (hardware, size of fulltext indexes, query frequency/complexity) that i don't think that your DBA can just say its bad. Unless of course he has already benchmarked it.

I'd go the other way and ask him to provide some figures supporting the claim. The amount of time/money you'd have to invest to develop something comparable would surely not stack up compared to SQL Servers out of the box technology which is being used by many other organisations. It would take less than a day to set up some simple prototype to be able to benchmark the overhead of FTI and give you some facts to kick around. I wonder how long it will take to build a ColdFusion prototype and benchmark that

HTH!

Adam D. Turner

As Rich has mentioned,

A solid argument would be that an out-of-the-box solution already exists. It doesn't make sense to re-invent the wheel especially considering the time/labor costs associated with development, testing, and documentation.

Equally, cursors carry a lot of overhead on the server but they do have their place. It wouldn't make sense for a DBA to argue that a developer can easily loop through the records without an equal amount of overhead. This simply isn't logical.

Adam

Tom Phillips

If you really NEED a full text index, then the way to go is probably SQL Server, instead of "inventing your own". You will never be able to match the speed of the full text index on SQL server, if the data you are indexing is on SQL server in the first place.

How would you do it if not in SQL Server You would retrieve each field from SQL Server and search it on the web server. This is VERY process heavy. And I would assume you are doing it on your WEB/Cold Fusion server, which should not need as much processor, memory, etc as the SQL Server.

With that said, the overhead is very high for a full text index. But if you need it, you will have overhead either way.