I just modified the api source and using full-text catalog for search is amazingly fast for our large product catalog (400,000 products-120 categories). Results return nearly instantaneously. Searches before always timed-out (over 30 seconds), maxing out our production sql server's HDD.
We're hosting on our own servers so building the full-text catalog and populating on a schedule isn't a problem--I don't know about shared hosting plans. I'd like to see it built-in so others can experience benefits (and to have less code to merge w/ AC updates).
Add option to use full-text catalog for search
-
- Ensign (ENS)
- Posts: 20
- Joined: Wed Oct 22, 2008 4:46 pm
Re: Add option to use full-text catalog for search
Hi, i was reading your post about your full text index having to be 1 mb smaller than the Original one. Is there any problems with yours. Can you share the code for you full text index? im intested in trying it out as well. I also host my own website . http://www.unitedintegral.com
howard
howard
Re: Add option to use full-text catalog for search
Here are steps I took...
1. In ac database created full-text catalog and added full-text indexes to following tables:columns
ac_Categories: Name, Summary
ac_WebPages: Name, Summary
ac_Links: Name, Summary
ac_Products: Name, Summary, sku, searchKeywords, Description, ExtendedDescription
2. In CommerceBuilder solution search code for "LIKE" and change those queries to use "FREETEXT"
e.g.
WHERE P.Description LIKE @keyword
changed to
WHERE FREETEXT(P.Description, @keyword)
*or use CONTAINS command for advanced searching
3. In those methods where I changed query to use freetext I commented out line that added LIKE's wildcard character '%' around the keyword string
4. I also modified the basic search to search SKU, description and extended description. I'm guessing AC doesn't search those fields because it would be slower w/o full-text catalog.
Note: you need to buy the API source code to change the queries in AC search controls or I suppose you could create your own search controls and bypass AC's. We bought source because our large catalog chokes AC in other places too like creating xml sitemaps and product feeds and managing product catalog in admin.
1. In ac database created full-text catalog and added full-text indexes to following tables:columns
ac_Categories: Name, Summary
ac_WebPages: Name, Summary
ac_Links: Name, Summary
ac_Products: Name, Summary, sku, searchKeywords, Description, ExtendedDescription
2. In CommerceBuilder solution search code for "LIKE" and change those queries to use "FREETEXT"
e.g.
WHERE P.Description LIKE @keyword
changed to
WHERE FREETEXT(P.Description, @keyword)
*or use CONTAINS command for advanced searching
3. In those methods where I changed query to use freetext I commented out line that added LIKE's wildcard character '%' around the keyword string
4. I also modified the basic search to search SKU, description and extended description. I'm guessing AC doesn't search those fields because it would be slower w/o full-text catalog.
Note: you need to buy the API source code to change the queries in AC search controls or I suppose you could create your own search controls and bypass AC's. We bought source because our large catalog chokes AC in other places too like creating xml sitemaps and product feeds and managing product catalog in admin.
Re: Add option to use full-text catalog for search
Is Full-Text Search available now?
I'm going to convert an old site to AC7 but can't until I have this feature.
Please let me know the status. Thanks
I'm going to convert an old site to AC7 but can't until I have this feature.
Please let me know the status. Thanks
- Logan Rhodehamel
- Developer
- Posts: 4116
- Joined: Wed Dec 10, 2003 5:26 pm
Re: Add option to use full-text catalog for search
In AC703 (released 1 June 2009) the store can make use of FTS for product searches.
Cheers,
Logan
.com
If I do not respond to an unsolicited private message, it's not because I'm ignoring you. It's because the answer to your question is valuable to others. Try the new topic button.
Logan
.com
If I do not respond to an unsolicited private message, it's not because I'm ignoring you. It's because the answer to your question is valuable to others. Try the new topic button.