Optimizing Woocommerce order items query

I have a custom admin page that needs to search through all shop orders to see if they contain a specific item. The relevant code is this:

$args = array(
 'post_status' => 'any',
 'post_type' => 'shop_order',
 'posts_per_page' => -1
);

$order_query = new WP_Query($args);

if ($order_query->have_posts()) {

  while ($order_query->have_posts()) : $order_query->the_post(); 

    global $post;

    $order = wc_get_order($post->ID);
    $items = $order->get_items();

    foreach($items as $item) {
      // Check if item data matches what was searched for
      if($item['variation_id'] == $product_variation_ID && $item['start-date'] == $start_date && $item['location'] == $location) { 
        // This order contains the item we're looking for     
      }

    } 

  endwhile; 
}

wp_reset_postdata(); 

Basically I get all shop orders, call wc_get_order() on each shop order ID, and then call get_items() on each order object. I then loop through the items to see if each item matches what was searched for. Each order only contains a single item, although that shouldn’t change anything.

This code works fine, except that now that the site has over 1,000 orders, the query is simply too resource intensive on shared hosting, I’m hitting the host’s memory limit and it’s failing to complete. Specifically, it’s failing when calling wc_get_order() on each shop_order post ID.

Is there any way I can make this query more efficient? I can’t think of any other way to get the items from a shop_order post type without calling wc_get_order(), even though every order only contains a single item. I’d rather not do this over 1,000 times but I just can’t see another way.

I may have to move to a VPS to resolve this temporarily, but I feel like eventually I’ll run into the same issue even with the additional resources of a VPS, as the number of shop_order posts to loop through is only increasing.

I’m doing something similar. I started off with an approach similar to yours, but made it slightly faster by directly using WC API Query instead of WP Query.

I am still looking for a way to query the variation from all orders…

//get number of orders per variation_id
function getOrdersfromVariation($variation_id){
  $numberOfOrders = 0;
  ip_write_log("getOrdersfromVariation varid: $variation_id");

  // rewrite with wc_get_orders
  $args = array(
    'status' => array(  'processing', 'completed'),
      'limit' => -1,
   );
  $orders = wc_get_orders( $args );
  if(isset($orders)){
  //TODO: Get order count - $total_orders = $orders->total;

    foreach ($orders as $order){
       foreach ($order->get_items() as $key => $lineItem) {
           $item_data = $lineItem->get_data();

           if ($item_data['variation_id'] == $variation_id) {
              $numberOfOrders++;

           }
       }
    }
    if(isset($numberOfOrders)){
       return $numberOfOrders;
    }
   }
 return;
}