BV Commerce Forum
»
BV Commerce Support
»
General Support
»
HOTFIX SP3-J - Update to search stored procedure
Rank: Member
Joined: 3/1/2006(UTC)
Posts: 1,142
|
This update will replace all previous search stored procedure updates. It fixes an issue with inactive items being counted in the paging. In order to run this file you will need to run SQL Server Management Studio and open the file, then select the database you want to patch from the "Available Databases" drop down list. You can hit F5 to run the patch or click "Execute" on the menu. File Attachment(s):  HotFix-SP3-J.zip (4kb) downloaded 29 time(s).You cannot view/download attachments. Try to login or register. |
Justin Etheredge
Senior Software Engineer
BVSoftware |
|
|
|
|
|
Rank: Member
Joined: 4/30/2007(UTC)
Posts: 383
|
Does this fix the inability to page through items and the lack of sense in the search terms? IE it showing donuts before nuts if a customer searches for nuts?
|
|
|
|
|
|
Rank: Member
Joined: 4/30/2007(UTC)
Posts: 383
|
The short answer is it's not fixed. A search for attache on our site returns a bottle opener that has the words attached in the description.
A search for tshirt returns sweatshirts....this is silly, can this finally get fixed to return whole matches/perfect matches FIRST.
|
|
|
|
|
|
Rank: Member
Joined: 5/14/2005(UTC)
Posts: 398
|
C
I think you might be happier switching to a full text catalog type of search like we did for 2004.7
this current query doesn't use weighting, inflectional, phonetics or ranking |
|
|
|
|
|
|
Rank: Member
Joined: 4/30/2007(UTC)
Posts: 383
|
Great, who sells one?
I'm just kind of shocked (and I'm sure others are here as well) that the search function is as simplistic. This one is pretty basic, why would ANY search want to take out parts of a word over the full word? I can't think of any major retail site I've ever visited that functions this way. If I type "on" into the bv search it will return any product with the words long gone done cone lone...often before a specific product named on. It makes no sense.
|
|
|
|
|
|
Rank: Member
Joined: 11/5/2003(UTC)
Posts: 2,136
Was thanked: 1 time(s) in 1 post(s)
|
C, I just checked several gallery sites from 3 of the competitors to BVC5 (similar price range and features). All of them matched on partial words and did not seem to favor full words higher. I was suprised that none of them would search on 2 letters or less. Is this an unspoken rule? Some would refuse to take a 2 letter search term. Some would take the term but not return any results. But as BetterBuilt wrote, what you describe sounds like a full-text index search. There are generic versions available the attach to any site (http://www.easysearchasp.net/, http://www.google.com/services/search.html). If you are handy at coding, you can probably turn on the full-text search in SQL Server (pretty good...but not great) and modify the stored procedure to use full-text search vernacular. Or have a programmer like BetterBuilt do that for you. |
|
|
|
|
|
|
Rank: Member
Joined: 4/30/2007(UTC)
Posts: 383
|
Andy my example was figurative just to be clear.
All we want, and I presume any BV customer would want, is for the built in search to weigh exact matches of words over partial matches from words. Is this not something most merchants want? I'm serious in asking this, Ijust assumed all vendors would want to be able to return search results that match.
|
|
|
|
|
|
Rank: Member
Joined: 3/1/2006(UTC)
Posts: 1,142
|
The latest query is supposed to match whole words higher than partial word matches. In fact, I had it doing just that on my local site. I'll have to look into your instance to see why this isn't happening. Are other people seeing this same behavior? |
Justin Etheredge
Senior Software Engineer
BVSoftware |
|
|
|
|
|
Rank: Member
Joined: 11/5/2003(UTC)
Posts: 2,136
Was thanked: 1 time(s) in 1 post(s)
|
My guess is you are right since there seems to be a whole after-market industry for adding more sophisticated search to ecommerce sites (see http://nextopia.com/ecommerce-search.html for example). The fact that these products exist implies that there are quite a few merchants that would like sophisticated search features (enough to support multiple after-market vendors), and that many ecommerce packages do not include them out-of-the-box. I am not familiar with the specific search features in BVC5 nor do I know if BV Software is planning any changes to their search feature. |
|
|
|
|
|
|
Rank: Member
Joined: 3/1/2006(UTC)
Posts: 1,142
|
Does anyone have a site with this stored proc live that we can use to test this? I have a site running here that the whole word matching is working on, and I have tested it on one other site and have found that the whole word matching is working there as well. We would like to get SP3.1 out, but we won't release it until we can get more feedback on this stored proc. |
Justin Etheredge
Senior Software Engineer
BVSoftware |
|
|
|
|
|
Rank: Member
Joined: 5/14/2005(UTC)
Posts: 398
|
It is really impossible for any one search query to work for all merchants/products. And BV can't assume that every host will allow an FTC to be set up and scheduled. For most merchants that have smaller catalogs or small targeted descriptions, the stock search is probably fine... but if your descriptions and text fields are long and full of descriptive terms where it is possible to return results that don't make sense, then you really won't be happy until you have a search custom programmed for you to respond the way you want it to. If you have the same word in most or all of your meta info (like a generic word or your store name) that resembles one of your products, you're customers may search for something that returns your entire catalog. With personal attention to the search query you can limit the search to the right places, or weight each field, or alter the search string (by replace common misspellings etc) before it is processed.
I don't know if bv5 is recording search strings yet or not, but knowing what people are searching for after they get to your site is very helpful to marketing and can also point out people's spelling/word choice preference. and that can help you fine tune your title/descriptions to make sure people get what they want. |
|
|
|
|
|
|
Rank: Member
Joined: 4/30/2007(UTC)
Posts: 383
|
Justin...my question originally was....is this fix on this thread encompassing the fix you had on another thread for the full word? I'll submit a ticket with the information for you to check.
Tell me what you need and we'll get it to you.
|
|
|
|
|
|
Rank: Member
Joined: 3/1/2006(UTC)
Posts: 1,142
|
If possible I need to get access to your database (a connection string), permission to modify that stored proc to confirm it is updated to the latest version that is in this thread, and permission to run that stored proc on your db so that I can see the results that are coming back from it. |
Justin Etheredge
Senior Software Engineer
BVSoftware |
|
|
|
|
|
Rank: Member
Joined: 4/30/2007(UTC)
Posts: 383
|
Justin ticket sent, use the email from that ticket to contact us.
I think probably one of the updates did not get applied and was assumed.
|
|
|
|
|
|
Rank: Member
Joined: 4/30/2007(UTC)
Posts: 383
|
Ping...ticket sent early this afternoon...if you want the login information or the SP let us know.
|
|
|
|
|
|
Rank: Member
Joined: 4/30/2007(UTC)
Posts: 383
|
Justin to make this as easy as possible...I sent the entire stored procedure to the ticket just now. I'm hoping it's as simple as a few missing lines not carried across updates.
|
|
|
|
|
|
Rank: Member
Joined: 4/30/2007(UTC)
Posts: 383
|
Any progress on this? We sent you the SQL SP.
|
|
|
|
|
|
Rank: Member
Joined: 3/1/2006(UTC)
Posts: 1,142
|
Yes, we looked into it and found that everything was working as expected. When I went to the site and searched for "pens" I got a list of pens, and when I searched for "tshirt" we didn't get anything meaningful back because all your products use the term "t-shirt". These variations in words are what the "keywords" field is for. The only way for us to make the search better without dramatically slowing it down is to implement SQL server full text indexing. The problem is that 90% of our customers don't control their own SQL server and most hosts don't allow full text indexing. Full text indexing would allow us to do things like linguistic stemming (which allows you to search for plural or singular forms of a word no matter what is entered), allow us to strip out stop words (words such as "a", "an", "the", etc...), and allow us to strip out non-alphanumeric characters for searches like yours where tshirt didn't match t-shirt. Searching is not a simple problem.
Yes, we would like to implement a better searching algorithm, but we would have to build our own search indexer (which is not really feasible), or we would need to use a tool like SQL Server Full text indexing. We have also looked at programs like Lucene.net which is a text indexing program, but there are performance considerations, and there are licensing issues since it is licensed under the Apache 2.0 license and we have versions of our software that don't contain source.
With that being said, splitting out the store search stored proc was one step in the direction of freeing the store search from other searches in the application. This will enable us in the future to add options like full-text indexing for those who can use it. But right now it was not feasible for us to implement this for this current service pack, and it likely will not be in SP4 either since that is only a few short months away. |
Justin Etheredge
Senior Software Engineer
BVSoftware |
|
|
|
|
|
Rank: Member
Joined: 10/28/2003(UTC)
Posts: 168
|
any one else have a problem of search page throwing errors after sp3 ? i applied sp3.1 but still have the issue. http://otc.sequent-tech.com/Search.aspxfound the problem: You must specify following in the Admin->Options->Search Settings Results Per Page -> 10 (anything more than 1) Items Per Row -> (anything more than 1) In my case it was set by default to 0 |
|
|
|
|
|
|
Rank: Member
Joined: 3/3/2006(UTC)
Posts: 1,737
|
Justin, thanks for the long version of what's going on. |
Optimists invent airplanes,
Pessimists buy parachutes. |
|
|
|
|
|
Forum Jump
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.