Hello,

I did my project locally on my computer using the FieldFullTextSearch with the Full Text Indexing activated on my computer, and when I wanted to implement my project on real web hosting I had a problem that all of the hosting sites said that they doesn't have the Full Indexing service, it is only availble on Dedicated server !! And I can't afford it since I am just a student who is doing his project and it is non profitable

So what can I do now ? I thought that i change my way of searching, so what is best other option of searching ?

I search on only one field which is called "productName" and the FieldFullTextSearch was giving very good results, but it also had a problem that I have to every time I insert a new product, I have to go to the Enterprise Manager and start or rebuild the Indexing everytime so the user can see what is he/she searching for, because if I didn't do that there will be no results of the new added product.

I hope you can help me quickly because the time for handing in my project is very close.

Thank you.

Yeah, ISP's are not fond of full text search indeed. Partly because this requires MS Search services and forces a lot of load on the db server as well as uses huge catalog files.

Have you tried a LIKE query? If you add an index on the column, and use a like query, it might be your results are pretty good as well. (if you search for exact matches also, you can use a compare value search and with an index the results should be good).

As you search on a single field which has a single word, it's not necessary to implement a keyword to entity index in a separate table.

Otis wrote:

Have you tried a LIKE query? If you add an index on the column, and use a like query, it might be your results are pretty good as well. (if you search for exact matches also, you can use a compare value search and with an index the results should be good).

As you search on a single field which has a single word, it's not necessary to implement a keyword to entity index in a separate table.

What do you mean add an index on the column ? .... sorry but i'm not that good in SQL Sever

I don't "search on a single field which has a single word", I want to search on single field which has a lot of word whether from the input (user) and each productName entity has more than one word for sure.

So what would I do ?

Thanks for your time.

Zonkle wrote:

Otis wrote:

Have you tried a LIKE query? If you add an index on the column, and use a like query, it might be your results are pretty good as well. (if you search for exact matches also, you can use a compare value search and with an index the results should be good).

As you search on a single field which has a single word, it's not necessary to implement a keyword to entity index in a separate table.

What do you mean add an index on the column ? .... sorry but i'm not that good in SQL Sever

If you search on a single column called 'productname' you should define an index on that column in the enterprise manager (design the table, click on one of the buttons at the top)

Though I think I misunderstood you: you want to find a productname in a lot of text.

I don't "search on a single field which has a single word", I want to search on single field which has a lot of word whether from the input (user) and each productName entity has more than one word for sure. So what would I do ? Thanks for your time.

Ok, just so we are talking about the same things: you want to search through large blocks of text in entities for a given productname? (like this forum, you search for a term and it wades through all the form messages) ?

Hi man,

Take a look at this pic and you will now what i want to search in :

So in the search box at the website, the user can enter more than one word, like: LLBLGen Pro

Did you get me ?

Thank you.

Zonkle wrote:

Hi man,

Take a look at this pic and you will now what i want to search in :

So in the search box at the website, the user can enter more than one word, like: LLBLGen Pro

Did you get me ?

Thank you.

Ok, that would result in something like: SELECT * FROM products where productname LIKE '...' OR productname LIKE '...' OR...

OR it would result in something like: SELECT * FROM products where productname IN ('..', '..', '..' )

IF you're searching through a single column.

IF you want to search blocks of texts for the productnames specified, you can't use the second option, you can use the first: this forum for example first used full text search but I had the same problem as you: this site is hosted on a shared server of an ISP, so full text search wasn't possible, I then rewrote the search to use LIKE .. OR LIKE... OR... searching, to search in the text fields which contain the message texts.

If the text to search through isn't that big, you get good results.

So it's not how they specify the product names but what you want to search that's important for my answer The reason I ask this is because if you have to search through 100,000 textblocks of 4KB of text or more using LIKE, it isn't going to be fast. You then have to go a different route: keyword lists.

Add a table which contains per word a list of records which textblock contains that word. You can store this in your situation in a productname | articleID fashion for example if you have to search through articles to see if they contain the product name.

You can then find back the articles very fast by querying that table.

To fill that table, either use a custom crawler process which strips articles for keywords and stores the keyword-articleid in the table, or do that when you store a new article/alter the article.

This forum will get such a system soon, as the search begins to slow down (it has to wade through 14,000 messages already)

Otis wrote:

Ok, that would result in something like: SELECT * FROM products where productname LIKE '...' OR productname LIKE '...' OR...

OR it would result in something like: SELECT * FROM products where productname IN ('..', '..', '..' )

keyword lists.

Add a table which contains per word a list of records which textblock contains that word. You can store this in your situation in a productname | articleID fashion for example if you have to search through articles to see if they contain the product name.

hmmm keyword lists, this sounds very interesting. Frans can you answer a quick question? If you went down the keyword list route would you use the second SELECT option as above. i.e.

SELECT * FROM products where productname IN ('coffee','cup')

or would you use the first one:

SELECT * FROM products where productname LIKE 'coffee' AND productname LIKE 'cup'

In the example above I want to look for both words.

Would you also set up the table like so:

wordID int PK word varchar(50) articleID int FK

Geoff.

davisg wrote:

Otis wrote:

Ok, that would result in something like: SELECT * FROM products where productname LIKE '...' OR productname LIKE '...' OR...

OR it would result in something like: SELECT * FROM products where productname IN ('..', '..', '..' )

keyword lists.

Add a table which contains per word a list of records which textblock contains that word. You can store this in your situation in a productname | articleID fashion for example if you have to search through articles to see if they contain the product name.

hmmm keyword lists, this sounds very interesting. Frans can you answer a quick question? If you went down the keyword list route would you use the second SELECT option as above. i.e.

SELECT * FROM products where productname IN ('coffee','cup')

or would you use the first one:

SELECT * FROM products where productname LIKE 'coffee' AND productname LIKE 'cup'

In the example above I want to look for both words.

IN is an OR query, so it doesn't work for an AND query. your like example indeed works in that case.

Would you also set up the table like so:

wordID int PK word varchar(50) articleID int FK

One way to do it yes, another is to just pick the 2 fields and make them the PK. Either way, you just grab the plain text, split it on whitespace, grab the list of words to skip (Index server has nice lists of words to skip for this) and what you've left is addable to the table.

Though this can grow very large, pretty quickly, so some people use packing of article ID's in a blob, though that's not what I think is necessary in a lot of situations.

It is a bit of tuning I guess: do you use a list of words which should be skipped (which can result in a lot of entries) or do you use a list of words to include (which could miss entries, but keeps the database compact). You can also make the engine selflearning, by first using a list of words to pick and use a slower LIKE query system if there are no matches. If the like query results in matches, add the term to the list of words to pick and re-crawl the articles on a scheduled time.

An ideal sunday afternoon project

Otis wrote:

So it's not how they specify the product names but what you want to search that's important for my answer

Hi man,

What I want to search products !

Anyway, I found a very good hosting with Full Text Indexing with really cheap price. Now the thing is after I added a few products I did Full Text Catalog thing ,,,, I searched and It worked wonderfully .... but the problem came out when I added new products ! ... So when I add new product and then go and try to search for it , it is like my program doesn't see the new added product

I think there is a way to do it with your code like to luanch the Text Indexing everytime I add new record ??

I really appreciate your patiance.

Thanks.

Zonkle wrote:

Otis wrote:

So it's not how they specify the product names but what you want to search that's important for my answer

Hi man,

What I want to search products !

Anyway, I found a very good hosting with Full Text Indexing with really cheap price. Now the thing is after I added a few products I did Full Text Catalog thing ,,,, I searched and It worked wonderfully .... but the problem came out when I added new products ! ... So when I add new product and then go and try to search for it , it is like my program doesn't see the new added product

I think there is a way to do it with your code like to luanch the Text Indexing everytime I add new record ??

I really appreciate your patiance.

Thanks.

Hi Zonkle

Yes there is a away, I did this for a project about 6 months ago but it depends how much access you have to the Full Text Catalog generation because it can be quite process intensive.

Usually ISP's who offer Full Text Catalogs will set them up for you and only perform a incremental update on them once a day but what you are looking for is called 'Change Tracking' and a simple command into query analyser like below will start this running and track changes made to the table and update the catalog index but you can only run this if you have permission to do so:

EXEC sp_fulltext_table '<MyTable>', 'Start_background_updateindex'

Can't remember though if you need a timestamp column in the table for this to work.

btw. If you need a SQL tutorial for this sort of stuff I recommend this:

http://www.sqlserverfaq.com/content/kbase/store/Tutorials/61.htm#_Toc514412927

ttfn,

Geoff.

Zonkle wrote:

Otis wrote:

So it's not how they specify the product names but what you want to search that's important for my answer

Hi man,

What I want to search products !

Anyway, I found a very good hosting with Full Text Indexing with really cheap price. Now the thing is after I added a few products I did Full Text Catalog thing ,,,, I searched and It worked wonderfully .... but the problem came out when I added new products ! ... So when I add new product and then go and try to search for it , it is like my program doesn't see the new added product

I think there is a way to do it with your code like to luanch the Text Indexing everytime I add new record ??

If I'm not mistaken, you could configure the fulltextsearch setting on a table to re-index if a new record was added. At least I know for sure it can be configured to be fully automatic.