SQL text search using ASP

Text search capabilities with MSSQL without crashing the server?

[alxdean]

Now is this even possible I ask?

here is some background on the scenario:
I've created a guide on topic A this guide has about 500 pages of information all stored in a database using templates and page types and loads of extra fields.
It had to be DB driven as this topic A guide will be implemented using non technical editors with input interfaces, different editorial control, page templates etc.
Also the same system will be used for Guide topic B, Guide on topic C, Guide on Topic D, all related topics, all sharing one common database.

The whole system is built on ASP. I know many people here will cry out loud and say "you dug your own grave" but when miracles had to happen in a inhumane time span, the choice was limited to this language.

So here is my dilemma:
I want to give my users full text search capabilities. But how can I make this happen without crashing the server on every request?
using the "Like '%keyword1%'and like %keyword2%" approach does not give relevant results, as most pages have a title, a description and a body text in different fields. Many more factors I would like to build in like keyword proximity or keyword count for relevancy.

So does anybody have any ideas on how to create a search algo based on a MSSQL database with a set of fields that will not strain on server resources for each search entered?

[JuDDer]

If using SQL Server, you may want to try searching the web for things like 'full text indexing' then 'full text search' as it's possible to build some very complex search queries if you have full text indexing enabled.

To stop the server strain with your current search facility, you could limit your search to 10 results or so which would ease the strain.

[tomasz]

Full Text Indexing will work with text field, like text,ntext.
Try to put your data in as varchar type, there is 8000 characters limit, but if you can put your data in this field and field will be indexed your perfomance should increase.
Another good thing to do is to use store procedures instead of SQL statements.

[alxdean]

thanks for the tips!
I think my end solution will be to enable full text indexing on my database (god nkows how I am going to achive that with a shared SQL server)
and using the stored procedutes and parameters for better performance.

thanks again for all your help!