Link to home
Start Free TrialLog in
Avatar of Robert Granlund
Robert GranlundFlag for United States of America

asked on

WordPress Custom Query slows the site WAY down need help with optimizing

WordPress Query is very slow.

I inherited a site with one piece of custom code.  It basically grabs products from the Database and orders them and displays them through a shortcode.  The issue is, when I turn off the code the page loads very fast but with the code working it takes about 3 seconds to return the query to return.  I tried to switch over to the proprietary way that woo would grab products but it is still really slow.  Can anyone look at the following code and suggest how to speed it up?  I even thought of forcing the code to load last?

NOTE: if I change this to "Not Empty" it does not do what it is designed to do but it speeds up:
if ( empty($prodData) ) {


<?php
$shortcodeId = 'swift-cross-sell item-' . random_int(9999, 99999999);
$skus = explode(",", $atts['skus']);


//if( !is_admin() ){
  $cacheKey = 'key_products_' . implode('_', $skus);

 
$loopData = WC()->session->get( $cacheKey );


  if ( empty($loopData) ) {
    $args = [
      'post_type'  => ['product', 'product_variation'],
      'meta_query' => array(
        [
          'key'   => '_sku',
          'value' => $skus,
          'compare' => 'IN',
        ],
      )
    ];

    if ( ! empty( $atts['tag'] ) ) {
      $args['product_cat'] = $atts['tag'];
    }
    $loopData = new WP_Query( $args );
    WC()->session->set( $cacheKey , $loopData );
    
  }
//}
  


$loop = [];
while ( $loopData->have_posts() ) {
  $loopData->the_post();
  $productId = get_the_ID();
  

  //if( !is_admin() ){
    $cacheKey = 'cachekey_product_data_' . $productId;


    $prodData = WC()->session->get( $cacheKey );
   
    //if ( TRUE ) {
     if ( empty($prodData) ) {

      global $product;
      $has_page = "";
      $page = "";
      $link = "";
      $title = get_the_title();
      $custom_link_page_ID = get_post_meta( get_the_ID(), 'custom_link_page_ID', true );
      if ( ! empty( $custom_link_page_ID ) ) {
        $has_page = true;
        $page = get_post( $custom_link_page_ID );
        $link = get_permalink( $custom_link_page_ID );
      }
      $prodSku = $product->get_sku();

      $prodData = [
       'productClass' => implode(' ', wc_get_product_class('product')),
        'isVisible' => $product->is_visible(),
        'custom_link_page_ID' => $custom_link_page_ID,
        'sku' => $prodSku,
        'has_page' => $has_page,
        'page' => $page,
        'link' => $link,
        'title' => $title,
        'price' => $product->get_price_html(),
        'ID' => $product->get_id(),
      ];
      WC()->session->set( $cacheKey , $prodData );
    } 
  //}
  $loop[$prodSku] = $prodData;
   
}


// order by shortcde SKU
$sortedLoop = [];
foreach ($skus as $sku) {
  foreach ($loop as $itemSku => $item) {
    if (trim($itemSku) == trim($sku)) {
      $sortedLoop[] = $item;
      break;
    }
  }
}


?>

<div class="shortcode-cross-sell " . <?= $shortcodeId ?>>
  <ul>
<?php if ($sortedLoop): ?>
  <?php foreach ($sortedLoop as $skuNo => $prodData ): ?>
    <li class="<?= $prodData['productClass'] ?>" data-id="<?php echo $prodData['ID']; ?>">
      <h4>Catalog No <?= $prodData['sku'] ?></h4>
      <?php if ( $prodData['has_page'] ) : ?>
        <a href="<?= $prodData['link'] ?>"><?= $prodData['title'] ?></a>
      <?php else: ?>
        <a href="#"><?= $prodData['title'] ?></a>
      <?php endif; ?>
    <?php if ( $prodData['price'] ) : ?>
      <span class="price"><?= $prodData['price'] ?></span>
    <?php endif; ?>
    <?php if ( detectLocation() ) : ?>
      <?php do_action( 'woocommerce_simple_add_to_cart' ); ?>
    <?php else: ?>
      <a href="<?php echo get_permalink( get_page_by_path( 'distributors' ) ); ?>" class="single_add_to_cart_button button btn-distributors"><?php _e( 'Distributors', 'woocommerce' ); ?></a>
    <?php endif; ?>
    </li>
  <?php
  endforeach;
  wp_reset_query();
  ?>
<?php endif; ?>
  </ul>
</div>

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Terry Woods
Terry Woods
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial