The Chatterbox

Speeding up x-cart when using variants

I recently was called in to help on an x-cart (Version 4.1.7 in case it matters) site that was having some serious performance issues. The issues being a combination of the inefficient way x-cart handles database queries for variants and in stock items and the sheer number of visitors this particular site attracts.

This project was a dress site that had recently been migrated to the x-cart platform. There were some 6,000+ dress styles by different designers, with each dress having somewhere in the neighborhood of 20 variants when you looked at the different sizees and colors that might be available. This site was somewhat unusual in that all items are available to be ordered, whether they are in stock or not. In stock being in stock and everything else being special order.

The way x-cart is coded this meant that every time one of the thousands of product pages was hit by a surfer or bot the backend code would have to roll through over 600,000 lines in the database in order to see which variants were In Stock and which were Special Order. As you might imagine, having this happen on every single product page ended up eating server resources pretty quickly. Especially when you're talking about a site that gets over 10,000 unique visits per day and pretty much every visitor is going to be hitting mulitple product pages.

We'd already done all of the normal things to help speed up x-cart, like disabling statistics, enabling zlib compression, using the html catalog feature, enabling template caching, etc. They helped a some, but it wasn't nearly enough. We went even farther by off-loading the database to a separate dedicated MySQL server, and while it helped a good bit this still didn't solve the load issue entirely. There were still spikes during extremely high traffic times that slowed things to a crawl.

After reviewing the situation I decided there were probably two things still giving us problems. One being that the queries to show what was in stock and what wasn't for a particular product was simply inefficient. The second being that because the query was inefficient and taking too long to complete, people were quite likely clicking numerous times when trying to get to the product pages. When loads were high nothing appeared immediately, so I made the leap that users were clicking and clicking and clicking trying to move forward, which of course only exaccerbates the problems and creates more load for everyone.

I'm going to document what we did to fix these load issues in hopes it gives others in the same boat some ideas. I could find no documentation of anything like this anywhere, but the approached worked for our situation.

First, I wanted to off-load the In Stock bit from the product pages. The theory being that the girls looking for a dress would click on the smaller thumbnail image in the Category area to see the full-sized view to see if they really liked the dress or not, but at this point of the buying process they really didn't need to know what was in stock and what was a special order dress.

To start the process the store staff cloned every product they had in stock to become part of a special category. The new category was simply called In Stock Dresses, and the products carried the same Product Name as the original, except that the text string "IS-" was appened to the beginning of the product name. So if they had an Alfred Angelo dress style 3207 that was available in sizes 0-8 and with colors of midnight blue and red it would stay there in the normal Alfred Angelo category as product name Alfred Angelo 3207 with lots of variants and 0 availability of each. However if they had a size 2 dress in blue and a size 4 dress in red actually in stock there would also be another entry as IS-Alfred Angelo 3207, showing just the two dress variations that were actually in stock.

Note: Make sure to back up any templates you alter just in case!

Then in the x-cart product template (/skin_name/customer/main/product.tpl) we removed the section that normally would show which items were In Stock variants on the product page. It was a fairly sizable chunk of code with a couple of {if} statements, staring with {if $in_stock_variants} in case you need help finding it.

After removing this we added a bit of smarty code and language to the normal product variants section to give people a way to check the available stock. This was just after the code block for the <form name="orderform" and table cell that creates the section in the product.tpl file. Here's what was added.

{if $product.product|truncate:3:"":true !="IS-"}

<tr>

<td colspan="2" class="instock"'>

<a href="javascript:to_instock_window('http://www.somedomain.com/store/get_instock.php?pn={$product.product|escape:"quotes"}')">Click here</a> to see if there are any sizes and colors of this style

<br />

in stock and available for immediate shipment.

</td>

</tr>

{/if}

What does the above code do?

It analyzes the first three characters of the product name field to see if the product starts with the string "IS-" or not. If it's not there people see the text and a link. If someone is already on an IS- product page they see nothing.

Notice that the link is firing a javascript event. We need to add this to the product.tpl template as well. You'll probably already have some JS in this template enclosed in {literal} smarty tags. If not, make sure the following javascript is inside a {literal} {/litersal} section towards the top of the product.tpl template.

<script language="JavaScript" type="text/javascript">

<!--

var newwindow = '';

function to_instock_window(url) {
if (!newwindow.closed && newwindow.location) {
newwindow.location.href = url;
}
else {
newwindow=window.open(url,'get_instock','height=400,width=400,scrollbars=yes');
if (!newwindow.opener) newwindow.opener = self;
}
if (window.focus) {newwindow.focus()}
}
//-->
</script>

Okay, so we have our template ready. With a bit of css styling thrown in for good measure the relevant portion of our product pages for items that are not in stock should now look similar to this.

So let's get on with making this thing work. We need to next create our get_instock.php page. The code for this page looks like:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<html>
<head>
<title>In Stock Availibility</title>

<link rel="stylesheet" href="/store/skin_name/skin_name.css" />

<script language="JavaScript" type="text/javascript">
<!--
function to_parent_window(url)
{
opener.location.href = url;
self.close();
}
//-->
</script>
<style type="text/css">
body {
background-color: #F9E2EC;
margin: 0px;
}
p {
margin-left: 10px;
margin-right: 10px;
}
</style>

</head>
<body leftmargin="0" topmargin="0" marginwidth="0" marginheight="0">
<img src="/store/skin_name/images/dialog-headers.gif" width="100%" alt="">
<?php
$dbhost=""; // Enter localhost or ip number of remote MySQL server
$dbname=""; // Enter database name
$dbuser=""; // Enter database user
$dbpass=""; // Enter database password

// Connect to the database
mysql_connect($dbhost, $dbuser, $dbpass);
// Select the databse
mysql_select_db($dbname) or die(mysql_error());
$pn = stripslashes(htmlspecialchars($_GET['pn']));

### If not an IS- product name, see if one is available
$IS_product = "IS-" . stripslashes(htmlspecialchars($pn));

$func_IS_product_query = "SELECT productid FROM xcart_products WHERE product = \"$IS_product\"";

$IS_result = @mysql_query($func_IS_product_query);
$IS_rows = mysql_num_rows($IS_result);
if($IS_rows == 0) {
$IS_exists = 0;
?>
<p>
<div align="center">
<?php
echo "<h2>" . $pn . "</h2>";
?>
</div>
</p><p>
There is currently no stock of this item in our store, however we will be happy to order it for you. Please return to the shopping cart and place your order. We will contact you with a shipping date for your special order.
</p><p>
If you need a dress immediately, please check the <a href="javascript:to_parent_window('http://www.somedomain.com/store/catalog/In-Stock-title0-p-1-c-###.html')" style="font-weight:600;">in stock category</a> or call us at ###-###-#### and we will be happy to help you find the perfect selection.
</p>
<p>
<a href="javascript:self.close()" style="float:right;font-weight:600;">Close Window</a>
<?php
}else{
$IS_exists = mysql_result($IS_result,"productid");

$IS_product_tmp = ereg_replace(" ", "-", $IS_product);
$IS_url = "http://www.somedomain.com/store/catalog/".$IS_product_tmp."-p-".$IS_exists.".html";
$get_availables = "SELECT classid FROM xcart_classes WHERE productid = '$IS_exists'";
$classes_result = @mysql_query($get_availables);
$classes_rows = mysql_num_rows($classes_result);
$classid = mysql_result($classes_result,"classid");

$get_options = "SELECT option_name from xcart_class_options WHERE classid = '$classid' AND avail = 'Y'";
$options_result = @mysql_query($get_options);
$options_rows = mysql_num_rows($options_result);

?>
<p>
The following are selections available for immediate shipment:
</p><p>
<div align="center">
<?php
echo "<h2>" . $pn . "</h2>";
for ($k=0; $k<$options_rows; $k++)
{
$new_options = mysql_result($options_result,$k,"option_name");
echo "<strong>" . $new_options . "</strong><br />";
}
?>
</div>
</p><p>
Please <a href="javascript:to_parent_window('<?php echo $IS_url; ?>')" style="font-weight:600;">Click Here</a> if the above In Stock selections meet your needs. Your main browser window will be refreshed to the order page for the above items.
</p><p>
Or you may simply <a href="javascript:self.close()" style="font-weight:600;">close this window</a> to continue shopping normally.
<?php
}

?>
</p>
</body>
</html>

There are several things going on in this popup window code. First and foremost being some database queries to ascertain if a given product has an IS- sister. If it does it writes the info to the popup window. (Note: To make things easier we had the store folks combine the size and color variants into a single class. I would highly recommend you do so also if at all possible, otherwise you'll need to alter the code considerably.)

It also has a little javascript function built into it to feed data back to the parent window. So if the user sees something that fits their need they'll get taken to the correct IS- product page in the store when they click on the link.

Lastly, when there is no In Stock availability for a given dress the user gets the option of either closing the popup and going back to the same page they were on in the main window, or they can click on the link we're providing them to take them directly to the jumping off point of our In Stock category. This is hard-coded, so you'll want to check to make sure the ### in the url gets changed to your In Stock catalog page.

That's all there is to this part of the equation. We've effectively off-loaded the In Stock variants to a separate popup window, which should decrease the server load considerably.

Next up, we wanted to give users some sort of visual queue that the server was trying to satisfy their request for a product page so that the girls wouldn't keep clicking and clicking and clicking and simply adding more load with their impatience.

I took a two-pronged approach to this. First, I simply added a standard "Loading..." graphic via a javascript onClick event when someone clicked on a product page link from a category page. I coded the JS function so that the loading image would be centered in the users browser window no matter where they were in the page. At the same time I added a Opacity routine so that the colors on the category page would fade out if there was going to be much of a wait. And lastly, I disabled their ability to click on other links on the page. Or I should say they can still click on them, but nothing happens if they do because the script heads off the click before it can add any server load.

Since there are several things going on I simply added these javascript functions to the end of the common.js file that x-cart already uses. This file can be found at /skin_name/common.js. The additonal javascript for all of the magic looks like:

// Added for fade out and loading graphic on category pages

function f_clientWidth() {

return f_filterResults (

window.innerWidth ? window.innerWidth : 0,

document.documentElement ? document.documentElement.clientWidth : 0,

document.body ? document.body.clientWidth : 0

);

}

function f_scrollTop() {

return f_filterResults (

window.pageYOffset ? window.pageYOffset : 0,
document.documentElement ? document.documentElement.scrollTop : 0,

document.body ? document.body.scrollTop : 0

);

}

function f_filterResults(n_win, n_docel, n_body) {

var n_result = n_win ? n_win : 0;

if (n_docel && (!n_result || (n_result > n_docel)))

n_result = n_docel;

return n_body && (!n_result || (n_result > n_body)) ? n_body : n_result;

}

function toggle_visibility(id) {
var e = document.getElementById(id);
if(e.style.visibility == 'visible')
e.style.visibility = 'hidden';
else
e.style.visibility = 'visible';
e.style.top = f_scrollTop() +120 + "px";
e.style.left = f_clientWidth()/2 - 30 + "px";
}

function opacity(id, opacStart, opacEnd, millisec) {
//speed for each frame
var speed = Math.round(millisec / 100);
var timer = 0;

//determine the direction for the blending, if start and end are the same nothing happens
if(opacStart > opacEnd) {
for(i = opacStart; i >= opacEnd; i--) {
setTimeout("changeOpac(" + i + ",'" + id + "')",(timer * speed));
timer++;
}
} else if(opacStart < opacEnd) {
for(i = opacStart; i <= opacEnd; i++)
{
setTimeout("changeOpac(" + i + ",'" + id + "')",(timer * speed));
timer++;
}
}
}

//change the opacity for different browsers
function changeOpac(opacity, id) {
var object = document.getElementById(id).style;
object.opacity = (opacity / 100);
object.MozOpacity = (opacity / 100);
object.KhtmlOpacity = (opacity / 100);
object.filter = "alpha(opacity=" + opacity + ")";
}

function DisableLinks(xHow){
objLinks = document.links;
opacity('thefade', 100, 60,500);
// Pause 2 seconds before loading the loading graphic
setTimeout("toggle_visibility('loading')", 2000);

for(i=0;i<objLinks.length;i++){
objLinks[i].disabled = xHow;

//link with onclick
if(objLinks[i].onclick && xHow){
objLinks[i].onclick = new Function("return false;" + objLinks[i].onclick.toString().getFuncBody());
}
//link without onclick
else if(xHow){
objLinks[i].onclick = function(){return false;}
}
//remove return false with link without onclick
else if(!xHow && objLinks[i].onclick.toString().indexOf("function(){return false;}") != -1){
objLinks[i].onclick = null;
}
//remove return false link with onclick
else if(!xHow && objLinks[i].onclick.toString().indexOf("return false;") != -1){
strClick = objLinks[i].onclick.toString().getFuncBody().replace("return false;","")
objLinks[i].onclick = new Function(strClick);
}
}
}

String.prototype.getFuncBody = function(){
var str=this.toString();
str=str.replace(/[^{]+{/,"");
str=str.substring(0,str.length-1);
str = str.replace(/\n/gi,"");
if(!str.match(/\(.*\)/gi))str += ")";
return str;
}

Lastly, we need to tie it all together by making a couple of tweaks to our category pages. IMO this template has a wacky name in x-cart, but it's the one called products_t.tpl located at /skin_name/customer/main/products_t.tpl

First we need to add a couple of new div layers that are initially going to be hidden by our css rules. Add this to the top of the products_t.tpl file, right after the $Id comment section:

<!-- Added for fadeout loading layer -->

<div id='thefade'>

<div id="loading" style="position: absolute; width: 160px; height: 160;">

<img id="loadingImg" src="/images/loadingb.gif" alt="Loading..." width="160" height="160" border="0" />

</div>

Then a bit farther down in this same template look for the section that kicks off the loop through the products. You should see an A HREF reference just below a section that says:

{section name=product loop=$products}

There may be a few of these, depending upon what you have enabled in x-cart. Have a look at each one as the View Source of your original cart page and you'll know which you need to edit. All we're going to do is add an onClick event to trigger the fadeout, so there's not much danger in playing with it a bit. Most installations I've seen are going to have three places you'll need to add the onClick event. Once for the product title, once for the thumbnail image and once for the Details language.

Our addition to these A HREF tags is simply:

onClick="DisableLinks(true);

That's it, you should be done now!

By utilizing the above we were able to take a site where the combination of x-cart's inefficiencies and sheer numbers were killing a pair of servers and get everything running smoothly again.

I hope this helps someone out there who finds themselves in a similar situation!

You can ping this entry by using http://www.randycullom.com/chatterbox/mt-tb.cgi/36 .