Already had I look at that and called in a DBA consult to see if there was any tweaking that could help. Rebuilt the indexes for the bvc_Product and bvc_ProductXCategory tables (had to do the first offline ) and did a few other assorted checks but no change in the online results (i.e. no items showing up). According to what we can see the SQL settings are fine and the server is not having a problem serving up results, they just don't show up online.

I have a question for the group: does anyone have a single category (or sub category) that has more than 10,000 items in it? If so can I get a link to it so I can see a working example.

I didn't think we had to rebuild the indexes, we just did it to placate a request and to see if it made any difference. It didn't so that eliminates one possible cause of the problem.

I understand that it returns only what is supposed to be shown on that page but somewhere there is a bottleneck in the programming.

We are running 5.5 which was the last version we were able to get access to. Our license agreement ran out just after 5.6 was issued but we were not able to download it before all access was cut off.

The store was built on a clean (new) install.

Paul,

Are you running any sales or discounts? Depending how those are setup they can adversely affect performance. Also, did you ever copy the site to your SQL Server and test it?

Update:

We separated the Licensing category in to sub categories. As soon as this was done and the items moved the main category became available again. Unfortunately two groups of items exceeded what appears to be a "magic number" of approx 6000 items and now will not show up unlike the rest of the sub categories.

The odd thing in this is that if you do an "empty" search with one of those "broken" sub categories, all items in that category are returned without a problem. This points to a failure of the code that controls the rendering of category results. Turning off URL rewriting has no effect.

Without putting to fine a point on it, BVCommerce does not seem to be ready for use as a ecommerce application for large scale product deployment. The fact that the issue "moves" with the data indicates a failure of the application in dealing with large data sets when called by users via the application itself. While the continued failure of the application to render all items in the admin tool (under Products) is annoying, the fact that customers will not see any products when going to categories that cross some unknown limit is unacceptable.

We are currently looking into further consultation options to see if there is a fix for BVCommerce before being forced to replace it, we have invested too many hours of hard work to quit yet. Should any consultants here wish to contact me directly you should do so via links at our site. Please serious inquiries only, we are a multi-million dollar corporation and this is not a joking matter.

Sadly this has all simply confirmed my original statement.

Peace and good luck to you all. I will continue to monitor this tread for any new ideas.

Thanks, I will be contacting you soon.

Paul,

After looking into this the bottleneck isn’t with the application code (ASP.NET) but rather in the SQL stored procedure that performs the product lookup, bvc_Product_ByCategoryFiltered_s. The SQL query tests you ran were rather simple compared to what this procedure does. In addition to finding the products that belong to a given category, it also sorts them according to the category sorting setting in the admin and performs an inventory check. The sorting requires an additional table join and the inventory check performs another join and also runs a SQL function. In other words, there’s a lot going on so when you have a large category this negatively impacts performance. Note that it is the category size, not the total size of your catalog, that is causing the problem.

After taking a closer look at how the procedure works I found a few places to optimize it. First, it looks like there are two unnecessary joins. Removing these provides a small improvement in performance. With a category containing 9,000 products I saw the execution time decrease about 1 second from 6 seconds to 5 seconds.

The other place that the query can be optimized is by removing the inventory check. Unfortunately you can only do this if you are not tracking inventory in BV. If that is the case, removing this check will save another 2 seconds, so your execution time is now down to 3 seconds (from 6 seconds).

Attached is a SQL query that will modify the original stored procedure to implement the above optimizations. Note that the changes are simply commented out with a double dash (“--“) at the beginning of the line, so you can always revert back by simply removing them.

To test the procedure before and after, you can execute the following query which returns the first 50 products of your largest category, Supplies:

1) Our willingness to pay you more money is directly related to the return on our investment so far. Business 101 (or maybe 102). The situation is compounded by the fact that critical information that would inform us on whether or not the upgrade is worth the plan is hidden so that only those currently IN the plan can see it. You might want to fix that.

2) Regarding not being constructive: I think the responses speak for themselves. Regarding accuracy: I stand by the results to date. With the excellent aid of Aaron Sherrick we have been able to mitigate the category product listing issue that we were having and we are greatly appreciative of his help.

I can't stop you from doing what you feel is right regarding your forums. I won't take umbrage on your implied statement regarding my character but instead I will offer this: if you want I will be glad to email you the issues that have occurred, what was done to address them, and what might be useful improvements from our internal discussion and customer reviews. Let me know if you are interested in this.

Good day.

Gaynes, your site is slow... Your do need to do something about that :)
Noted you are not running http compression ???
Looking over some traffic stats for your domain a dedi or VPS looks like a good option for you.

I just moved this site off of a split server that was having the same issue.

http://www.sportoptics.com/

Performance is substantially better than before.
Looks to be similar content and load as your site.
Wonder if you were on same split :) or host.

He has two more sites I am moving still that are on a splits also.

Current machine is a 64bit Win2008 with 4g mem.
Database server is on same box.

Edit: Wow, just went back there and now you are in zippy fast mode.
I would guess if it does that most of the time you are in good shape on the application.