Recently I was asked to look at a website for a company that was hosting a couple CFWebstores and looking to add another. They were concerned because the site was drawing so much database traffic and loading very slow. They saw what looked to be huge number of queries on the site and wanted to know if there was a bug or issue with the software that was causing it. It took only a quick glance to see what the problems were and how to easily address them.

First, they were using the standard product templates for all the categories. It's important to note that when you use the standard product listing, a great deal of product information is included on the page, including all the product options and addons, pricing, review summary, etc. Each of these requires database calls to retrieve. I actually code the software to retrieve as much of this as possible in a single query, and then loop through it to output each, but there's still some that run for each product. To compound the problem, the user was not making good use of the "Maximum Product per Page" function so some pages were trying to output as many as 50 products with full detailed information. No wonder the database was getting taxed!

There are many ways to improve performance on your site, so you always want to evaluate how much information you are trying to show per page and see if there are better approaches. Try using the different listing methods: short, vertical, horizontal and gallery (some of these are not available in older versions, all are found in the current 6.3 release). You can change the listing method simply by entering it as a parameter on the category page down below the description. All of these alternate listings use much less information on the category page and thus are much faster and less taxing on the database. On the site I was reviewing, simply switching from the standard product listing to the short listing improved the average load time about four-fold.

Setting a maximum products per page is also a good idea if you have a lot of products per category, and more friendly for your users as well. You can add default settings in the Main Settings and override this if needed on a per-category basis. The fewer products per page, the faster your pages will load and overall you'll reduce load on the server. You can also set the store to Cache Products which will save product queries into server memory rather than having to get them from the database for every page hit. If you have control of your server, you may want to look into Trusted Cache which saves the CFML templates into memory and greatly improves load time as well (but can make updates to the code more tedious so only use if you are not actively working on your site).

Hope these tips help get your site running faster than ever!