So we have a store front and many custom pages. All is working fine except ...

1. when a category is selected no products show up yet the only difference between that category and others is the number of items in it.
2. when accessing the catalog via the Catalog tab the interface hangs for a time then shows no items.

From various tests and troubleshooting it seems that BVC is totally crippled when the database for items goes beyond 10k to 15k product listings. It simply can't handle the amount of data involved with running a serious e-comm site.

Does anyone else here run a e-comm site that has more than 40k products in their catalog? If so how did you work around this rather sad short coming of BVC?

Thanks for any input.

The link(s) are in my sig.

If you click on any category a listing will come up (some faster than others ofc) except the "Licensing" category. The category holds just over 13,000 items yet not a single one shows up. If you use the search function and pick Licensing and any manufacturer that has licensed software while leaving the keyword field blank, you do get a result thus proving the category is in fact populated.

The same effect happens when accessing the admin Catalog tab. Nothing shows up unless you specify a manufacturer and/or a category.

I've tested the SQL server (2005 on a separate dual core server) and it responds quickly to other queries both local and remote. However there is a noticeable lag when doing full table queries on the 'bvc_Product' table. Doing a query on the bvin field is as fast as expected (since it's a PK field), i.e. very fast.

Paul -- some thoughts;

1. Way too many products for any given category -- first, lets forget the fact that the database search is too intense to load that many products, secondly as a customer I would NEVER browse that many product pages, on top of the fact that you have to scroll to far down to see all that are on a single page (you are much better with a grid layout).

2. Your small images are fairly large (12-14k) -- so when you are talking about 20 products on a page, that is quite a load for any system/browser.

You would do much better to break out into subcats -- for example take accessories into mice, keyboards, speakers -- etc. -- both from an admin standpoint and from a customer standpoint, you are going to get more "product looks" that way.

I think given your structure, ANY ecommerce platform, short of paying a developer for a custom app, is going to give you similar trouble.

1. Customers rarely use categories regardless of size, more often they use the search function. As for it being "too intense" I guess I can see that now but I did not think that this app would be so crippled. We had a perl driven store front app before going to this one and it would've run circles around this (if we still had it in place) and it's was not even running native (ActivePerl under IIS 6). Preliminary testing did not show such a weakness but then we originally weren't going to have so much online. Change in policy caused us to add a larger swath of our offerings. That is when we found this problem and have been hobbled ever since.

2. For the Licensing category we aren't using the detailed list template, FYI, we are using the simple list template. So image size should not be a factor. However the search function is using a grid format yet it is loading an empty keyword search just fine, including images. So again I can't see how images could be crippling one part while not crippling another, just doesn't make sense.

We have contemplated sub categories but we'd still have to have something show up for the 'prime' category and then we are back to the same problem. So far the only problem we are having is with the licensing category, others like accessories and peripherals are unaffected. However other categories do not have as many items to list.

I don't agree that we have such a huge product base that a custom app is needed. Ours, going by the statement of Marcus, is relatively small at around 43k items, so it has to be something in the code that is broken that is preventing the listing to show up.

One more question: are you seeing any errors in the BV event log?

I was just getting some fairly good search result returns on your site. You change anything?
"2005 on a separate dual core server" This is on its own server right? Also are you using internal private IP's between the servers? 1gig? Dual core is a little light weight for an e-commerce database server. Rebuild indexes, proper maintenance etc. ? Check this site for some tips http://www.sql-server-performance.com/ Do not think it is the application here.

Just looked again and 10 secs between pages when looking at memory category. Would be good to find a solution here for faster paging. I can see why you might be frustrated with 10 sec page loads.

Frustrating yes, scary no. I've been in this field too long to be scared of much.

I don't disagree that reducing image size might help the end user load times but my point was that as a factor of the problem at hand I don't see the relevance.

Well we do deal with very few "window" shoppers and more "handyman" shoppers in general. Those type usually know what they are after, many even know the manufacturer part number, and use the search function to a larger degree than sorting through categories. These are IT directors, CTOs and other in charge of technology at their locations. We rarely sell to the "average" individual and then those are usually students / teachers and are looking for specific products also.

Do you have the solution setup in Visual Studio? Step through in this process to maybe pinpoint your bottleneck? Do you have site and database also setup on local test servers to see if you are experiencing the same performance issues?

I agree with Kman, SQL query performance (I mean generally, not BV specific) can be a bit obscure at times.
I'd recommend having a qualified dba rebuild any indexes and also update statistics, particulary if there are any Views involved, just to eliminate the possibilities.

I've seen 20:1 query performance increases in some cases across a larger record set with no change to the SQL sent to the database after indexes/stats have been fixed.

No, I do not but I will look into setting this up. I was hopeful that I wouldn't have to dive into the code base to fix this but that might have been a false hope. :D

Moving it to a test server is possible but I will have to look into what I have available.