We have over 5000 items and over 80 categories. Everything in the site (including searches) is fast except when you click on a category under departments (Even empty categories).

I am running the performance monitor on our dedicated SQL Server. Memory, disk and page utilization hardly get hit however Processor goes to 100% for roughly 12 seconds. This ONLY happens when you click on the category. I have tested a number of different sections of the site and none push the processor to 100% except clicking on a category in the store. All categories are Static and I have the category set to Detail and sort my product name. I have tried simple and manual with no change in results. I have not made any customizations to BVC 5.

It does not happen in the admin section or in any of the user functions. Only started happening when we passed 60 categories or 3000 items. Please help as the trial customers have all said it is too slow. We are trying to bring the site up for production.

Thank you in advance for your help.

Michael Otsuji

www.da-planet.com/store

I just installed Hot Fix F with no change (except the search seems faster). If I do a Search and return all items in a specific category it is super fast with minimal processor usage <30%. The Search rips and works perfect but the category pages do not.

When I go and click on the category link under departments and it brings up a page the CPU utilization shoots up to 100% for the exact same list of products.

I just tried removing all the options on the page such as the category list and search and made the category a simple list and this did not affect anything.

If you could please check out www.da-planet.com/store for an example. Click on any of the departments and you will see what I am talking about.

Thanks for your help.

Dear Matt,


Thanks for your response. The Yu-Gi-Oh Category has 4 subcategories. The subcategory Single Cards has 78 subcategories. Those subcategories are based on the sets. Each set has a max of 150 products. I hope the software can support more than 24 products per subcategory because if it does not I have no way to classify the cards. The problem seemed to appear once we started entering the Magic singles and those sets have well over 300 cards per category.



The anomoly is that the Search works PERFECT and the category pages were blazing until I added the last 1000 or so products. I am super concerned as we are not even close to finishing the number of product entries that we have. Our current ASP3.0 application has 31000 records so we have a long way to go.



I tried turning everything I could off but nothing has any affect. I hope this is something simple as in its current state the site is no longer usable. We had 3 people try to browse the site and basically it stops working for over a minute. The SQL server P4 2.4 2GB RAID 5 is pegged at 100% for the whole time. The Disk Usage, and Memory Usage are super low and barely even move. The web server is basically idle during the whole time the SQL Server is pegged.



Sincerely,



Michael Otsuji

Thanks for your suggestions and help.
1. All Categories are Static.

2. No modifications other than the theme.

3. SQL is on a dedicated box. 2003 Server P4 2.4 2GB RAID 5 ATA.



We have all our own gear and a dedicated T1.



Usually our products are categorized by set.



My paradox is that the search works super fast. I can select a Category and the products are instantly returned with minimal CPU usage. I can use the Admin pages and everything works perfect. Just when I use the customer pages that are accessed when clicking the category does everything get slow.



I am guessing it is some index that a stored procedure is using. I work some test ASPX pages and SQL queries against the database to test and everything rips. So lost. No idea.

Hey Michael, did you try switching the display to a grid? Not sure if you said you tried this.

I know that the one thing that's going to add overhead is that the Detailed Display has to filter through every product that is in a category to determine if it has choices or not to display the Add To Cart button or the View Details button.

Having a category with what I perceive to be 4700 products assigned to it is going to add a tremendous amount of overhead. Even without the add to cart button, a category that has to display a pager system that goes to 476 has to take the time to determine how many pages to display.

When I do a search on your site, it's snappy, but it's a grid display. Application doesn't have to filter through each product to see if there are choices.

The Product Batch Edit would be the solution, but I'm not seeing the ability to juggle category assignments with this feature.

Marcus, would SQL F&R be a candidate for this?

I just clicked the Yu-Gi-Oh category and noticed a tremendous decrease in load time. Still hefty, but nowhere near the same as before. But now the pager count is at 794.



I've been trying to get it across without offending you and I hope you see it only as constructive, 4700 products in 1 category is just not proper.



So i'm looking at some of your cards. I type "Cyberdark" in the search and get 9+ products. If cyberdark is a family of cards. That's a Yu-Gi-Oh Subcategory. I type in "Destruction of Destiny", if that's a family of cards, there's another sub category.



Again, I don't know the nature of the products and how they are related so I'm going on what I see. When I see the same name on a deck of cards only difference being a booster pack 1, 2, 3, etc... I see a grouping and a resulting sub category.



If you sub-categorize your catalog to the level I'm mentioning, remove 4700 products from a category, you'll see page loads a million times faster and have a field day in the search engines!

Hey Michael,

I would uncheck the parent and leave the sub checked. But again, if it's going to be 4000+ products in a category, it's only moving the problem down a level.

In the stores that I maintain, I'll use the parent category (call it a department) to highlight a select number of products. Let's say you have a brand new set of YGO cards available that you want push. I would assign those sets to the parent as well as it's corresponding sub-category.



Call it In-Store Marketing.



As a designer of many ecommerce stores, I strongly advise that you evaluate the catalog in it's entirety. As a search engine freak, I strongly advise 100's of subcategories!



All in all, it's a ton of work. I do it 365 days a year and boy do my eyes get tired of it, you may see it as an impossibly daunting task, but longterm thinking will set you up for extreme success in a year or two! You get 100% out, if you put 100% in.



Good luck with your store! Thanks for taking my advice in stride :)

Didn't you say that empty categories are behaving this way as well?

Dear Matt and BV Software Programmers,

OK I did what you said kind of. I wrote a script to uncheck the parent categories. Thus no category has the 1000's of items. It does not seem to have helped.

My reply to the previous post is not correct. If a category has ANY products associated with it then when you click on the category it is slow. If the category has only subcategories in it then the page opens quickly.

In the subcategory World of Warcraft | Booster boxes I have only 5 products and the behavior remains.

Is it possible that an index is corrupt or something? Totally lost.

Sincerely,

Michael Otsuji

I have gone through the SQL Trace from the Profiler. The delays seem to come from the following statements. All the other statements have a duration of 0. I don't know if this helps.




IF (SELECT SettingValue FROM bvc_WebAppSetting WHERE SettingName = 'DisableInventory') = 1 (Delay of 2-5 appears roughly 16 times everytime a category is clicked)





The following statements have longer delays



SP:StmtCompleted SELECT
Count(*) As [Count]
FROM bvc_Product p JOIN bvc_ProductXCategory px ON p.bvin = px.ProductID
WHERE p.ParentID=''
AND px.CategoryID = @bvin
AND dbo.bvc_ProductAvailableAndActive(px.ProductId, 0) = 1


241 (Duration)


RPC:Completed exec bvc_ProductCount_ByCategory_s @bvin=N'94c73c8c-5c1b-4f0a-99d1-c48a6fe97b80'



241 (Duration)


SP:StmtCompleted SELECT
Count(*) As [Count]
FROM bvc_Product p JOIN bvc_ProductXCategory px ON p.bvin = px.ProductID
WHERE p.ParentID=''
AND px.CategoryID = @bvin
AND dbo.bvc_ProductAvailableAndActive(px.ProductId, 0) = 1


221 (Duration)



RPC:Completed exec bvc_ProductCount_ByCategory_s @bvin=N'962193c6-3c7d-4f52-b26b-19fbdb281f90'



222 (Duration)



SP:StmtCompleted SELECT
Count(*) As [Count]
FROM bvc_Product p JOIN bvc_ProductXCategory px ON p.bvin = px.ProductID
WHERE p.ParentID=''
AND px.CategoryID = @bvin
AND dbo.bvc_ProductAvailableAndActive(px.ProductId, 0) = 1


128 (Duration)


RPC:Completed exec bvc_ProductCount_ByCategory_s @bvin=N'd9882ba5-ff56-43f0-a431-9eb90b6297cb'



129 (Duration)