Category Page – How to Fix Slow Loading Issues

You can either stay with the native way Magento handles products and tweak some settings to improve performance, or customise the product relationship.

Really it comes down to what you are trying to do and where your performance and workflow bottlnecks are.

Magento is slow enough with configurable products (getConfigurableAttributes etc.) - and if you continue to go on, you'll grind to a halt.

My advice would be to stick to the Magento way for product relationships/storage and modify the interfaces used for editing/front-end display.

Make a custom product editor for the admin (then you can make it as efficient as you need), and add a custom index for your front end to keep performance high

Sorry I'm being so vague, but your question is both exceptionally broad (ie. what issues exactly are you trying to solve) and incredibly localised.

The built-in Magento Profiler is crap. If you really want to see what is slowing down your page loading speeds I'd suggest using the PhpStorm IDE with Xdebug. If you are developing for Magento or are doing frequent code changes use PhpStorm else you can probably get away with using the Eclipse IDE. Using Xdebug you can run a profile on your pages and then view the output with WinCacheGrind or the built in profiler in PhpStorm and see the EVERYTHING, the WHOLE trace of function calls starting from when Magento gets the first request. Doing so, you can step deeper and deeper into each function and see the time it takes for each function to execute.

Speaking for personal experience one website I worked on had configurable product pages taking up to a minute or more to load, in some extreme cases even more than an hour! They too had hundreds of child products and hundreds of options in one configurable product. After a lot of tracing 90% of speed issues came from the custom built theme from the previous company that used to manage that particular Magento store.

The problem areas were nested loops and large product collections being re-instantiated each time inside the nested loops which will definitely cripple page speeds.

Now product pages load within 2-3 seconds, WITHOUT Magento caching enabled. With caching it's nearly instant, and this is a store with 380K products.

Besides the guide suggested by benmarks do check out that same TurnKeyes Magento Performance blog: http://turnkeye.com/blog/category/magento-performance/ they also released a Part 2 for improving Magento performance which is really good.

You can also use the caching technique mentioned here: http://turnkeye.com/blog/magento-performance-optimization-configurable-products-2/ on static sections of your website bypassing extra Magento function calls. One section that saved a good chunk of time was the navigation bar/menu. After applying the caching technique it saved a good second of loading time from each page load, but in exchange for having Magento remember which menu link you clicked on when selecting something from the dropdown.

Other suggestions, do review your website theme and comment out function calls that your website does not utilize. Since you are using APC, make sure you have 2-layered caching enabled.

PhpStorm: http://www.jetbrains.com/phpstorm/

Xdebug: http://xdebug.org/

WinCacheGrind: http://sourceforge.net/projects/wincachegrind/

Installing Xdebug with PhpStorm: http://confluence.jetbrains.com/display/PhpStorm/Xdebug+Installation+Guide

http://confluence.jetbrains.com/display/PhpStorm/Zero-configuration+Web+Application+Debugging+with+Xdebug+and+PhpStorm