We are currently migrating a clients site to a new platform. One of the original complaints with regard to the existing site was slowness. Think I found the answer today.
It takes 119 queries to display a product page. There was one query (exactly the same, no updates in between) executed 16 times! And another one run 30+ time because the author of the code didn't want to use the SQL operator IN or do a JOIN.
When you're writing a store front, ask your self. How often do you need to run
SELECT COUNT(*) FROM product_option where product_id = 345
We think 20 is too many.
Come on guys, trace your code and monitor SQL query logs when you're developing. These kinds of things are easy to catch and fix. Computer are fast but, lets not waste resources.