If your WordPress website has poor page load times or recurrent database time outs (does “Error Establishing Database Connection” sound familiar?) despite stellar hosting environment and back end optimizations, you may need to check under the hood at your custom code. Use a plugin such as Query Monitor to view all the database queries made on a particular page. Implementing efficient code along with caching frequently used and complex queries can help improve performance.

This example is an updated version of the code from step one of Breadcrumb Dropdown on WooCommerce Page. We used the Transient API for persistent caching because the product category data is not updated often and our functions are used multiple times on a page across several pages on the site (for additional purposes other than the dropdown). If you do not need the data to be persistent across pages, you should check out the articles mentioned below. Non-persistent object caching may suit your needs and you can easily tweak our code to accommodate.

1. Cache frequently used data and remove unnecessary recursion

da_get_root_term was a recursive function to find the root parent/ancestor of a particular product category. For sites with heavy traffic or, in this example, sizable nested product categories, recursive functions containing queries can quickly lead to a significant number of database calls and possible timeouts. We replaced da_get_root_term with da_get_root_product_category and da_get_cached_main_product_category. Our client chose to have exactly two root product categories by design so we cached an array of the root ID, slug, name, and children IDs for each root. Another approach you could use is store the data of all roots together as an array of arrays, then loop through your results with PHP to find the winner. Be mindful to cache only the data you need; avoid caching arrays of entire post objects.

//get the root product category of a term
function da_get_root_product_category($cat_id){
	$cat_theme = da_get_cached_main_product_category("root-cat-1");
	//check if the $cat_id is in the children array for root-cat-1
	if(in_array($cat_id, $cat_theme["children"])){
		return $cat_theme;
	}
	//otherwise it must be a (grand)child of root-cat-2
	return da_get_cached_main_product_category("root-cat-2");
}
//get the cached product category per slug
function da_get_cached_main_product_category($slug){
	$key = "da_main_prod_cat_" . $slug;
	
	//get the data from cache
	$data = get_transient($key);
	
	//if the key does not exist in cache then get the data and cache it
    if ($data === false) {
		//get the product category
		$term = get_term_by("slug", $slug, "product_cat");
		
		//get the children IDs for that product category
		$args = array(
			'hide_empty' => false,
			'child_of'    => $term->term_id,
			'fields' => 'ids',
			//for efficiency
			'no_found_rows' => true,
			'update_term_meta_cache' => false
		);
		$children = get_terms( 'product_cat', $args );
		
		//cache only the data we need
		$data = array(
			"slug" => $term->slug, 
			"term_id" => $term->term_id, 
			"name" => $term->name,
			"children" => $children
		);	
        set_transient($key, $data, 3600 * 12);	//seconds
    }

    return $data;
}

2. Improve code efficiency

Due to the nature of da_get_terms_indented (generate hierarchical dropdown options), we kept it as a recursive function. However, we added optional parameters so that we can grab all the data we’ll need at the start and pass it along on each recursive call instead of querying the database each time around. Again, since this function is reused so often, we cached the basic term data (IDs, names, slugs) in da_get_cached_term_list_hierarchy and da_get_cached_term_list.

//Recursive helper method, returns string of <option> in hierarchical order and formatted 
function gf_get_terms_indented($taxonomy, $parent_id, $selected = 0, $level = 0, 
													$hierarchy = null, $terms = null){
	$options = "";
	
	$prefix = str_repeat(" ", $level * 3);
	if(!empty($prefix)) $prefix .= " ";
	
	//get full list of descendents properly ordered/grouped
	if($hierarchy == null) $hierarchy = da_get_cached_term_list_hierarchy($taxonomy);
	
	//get full list of terms (ids and names)
	if($terms == null) $terms = da_get_cached_term_list($taxonomy);
	
	//if the parent has children
	if( $hierarchy != null && array_key_exists($parent_id, $hierarchy) ) {
		
		//get the direct children's names and sort alphabetically
		$children = array();
		foreach($hierarchy[$parent_id] as $term_id) {
			$children[$term_id] = $terms[$term_id];
		}
		asort($children);
		
		//generate for each child term
		foreach($children as $term_id => $term_name){
			$is_selected = "";
			if($selected_id > 0 && $selected_id == $term_id) $is_selected = 'selected="selected"';
			
			//check for error in case term no longer exists
			$link = get_term_link( $term_id );
			if( !is_wp_error($link) != null && $term_name != null	) {
				$options .= '<option value="' . $link . '" ' . $is_selected . '>' 
						. $prefix . $term_name . '</option>';

				//gets <option>s for direct children of current child term
				$options .= da_get_terms_indented($taxonomy, $term_id, $selected_id, $level + 1,
																		$hierarchy, $terms);
			}
		}
	}
	
	return $options;
}
//cached full list of descendants properly ordered/grouped
function da_get_cached_term_list_hierarchy($taxonomy){
	$key = "da_term_list_hrch_" . $taxonomy;
	$data = get_transient($key);
	
	if ($data === false) {
		$data = _get_term_hierarchy($taxonomy);
		set_transient($key, $data, 3600 * 12);
	}
	
	return $data;
}
//cached full list of terms (ids and names)
function da_get_cached_term_list($taxonomy){
	$key = "da_term_list_" . $taxonomy;
	$data = get_transient($key);
	
	if ($data === false) {
		$args = array(
			'orderby'    => 'title',
			'order'      => 'asc',
			'hide_empty' => false,
			'fields' => 'id=>name',
			//for efficiency
			'no_found_rows' => true,
			'update_term_meta_cache' => false
		);
		$data = get_terms( $taxonomy, $args );
		set_transient($key, $data, 3600 * 12);
	}
	
	return $data;
}

For more information on caching and other ways to scale your WordPress site (hosting, code improvements, plugins to avoid, back end optimizations), here are a few articles I found helpful:
How to Scale WordPress
How does Object Caching Work
DIY Caching Methods