Performance Optimization

Comprehensive guide to optimizing GSM Middleware performance, from database indexes to caching strategies and React component optimization.

Overview

GSM Middleware handles high-volume data operations across multiple systems:

• 1000s of orders daily
• 10,000+ inventory SKUs
• Real-time sync with Business Central, BigCommerce, WooCommerce
• Multi-site management (10-50+ sites)

This guide covers proven optimization techniques implemented across the plugin, including:

• Database indexing strategies
• Query optimization patterns
• Caching layers (HTTP, object, transient)
• React component performance
• Background job optimization

---

Database Performance

Index Strategy

Purpose: Speed up frequently filtered/sorted queries

Implemented Indexes:

rm_order Performance Indexes

Migration: 011_add_orders_performance_indexes.php (v1.9.2)

-- Speed up ORDER BY dateadded DESC queries (Orders page default sort)
ALTER TABLE rm_order ADD INDEX idx_dateadded (dateadded DESC);

-- Speed up shipped filter queries
ALTER TABLE rm_order ADD INDEX idx_ship_status (ship_status);

Impact:

• Orders page load time: 300ms → 80ms (73% reduction)
• Shipped filter query: Full table scan → Index scan
• Sort performance: Eliminates filesort operation

Before optimization:

EXPLAIN SELECT * FROM rm_order ORDER BY dateadded DESC LIMIT 20;
-- Using temporary; Using filesort; 6,847 rows examined

After optimization:

EXPLAIN SELECT * FROM rm_order ORDER BY dateadded DESC LIMIT 20;
-- Using index idx_dateadded; 20 rows examined

---

rm_sites Indexes

Purpose: Fast lookups by active status and platform

-- Baseline schema indexes
ALTER TABLE rm_sites ADD INDEX idx_is_active (is_active);
ALTER TABLE rm_sites ADD INDEX idx_platform (platform);

Impact:

• Site dropdown population: 150ms → 10ms
• Active site filters: Index scan vs. full table scan

Query optimization:

-- Bad (full table scan)
SELECT * FROM rm_sites;

-- Good (index scan)
SELECT * FROM rm_sites WHERE is_active = 1;

---

Analytics Performance Indexes

Migration: 020 (v1.18.4)

Purpose: Speed up Data Analytics report queries on high-volume tables

-- Top Selling Products report (rm_lineitems: 2.6M+ rows)
ALTER TABLE rm_lineitems ADD INDEX idx_item (item);

-- Shipping Performance and fulfillment KPI (shipments: 866K+ rows)
ALTER TABLE shipments ADD INDEX idx_order_id (order_id);
ALTER TABLE shipments ADD INDEX idx_shipping_provider (shipping_provider);

-- Site-filtered date range queries (all 10 analytics reports)
ALTER TABLE rm_order ADD INDEX idx_site_dateadded (site, dateadded);

-- Geographic Sales report (rm_address: 1.7M+ rows)
ALTER TABLE rm_address ADD INDEX idx_order_type (`order`, type);

-- SKU lookups in reports and order processing
ALTER TABLE rm_items ADD INDEX idx_sku (sku);

Dropped redundant indexes:

• rm_address.order — superseded by composite idx_order_type
• rm_items.rm_items_site_IDX — redundant with primary key

Impact:

• Report queries on multi-million-row tables: full table scan → index scan
• Typical report execution: 3-5s → 200-500ms

---

rm_queue Composite Index

Purpose: Optimize job queue processing

-- Baseline schema index
ALTER TABLE rm_queue 
  ADD INDEX idx_status_priority (status, priority, created_at);

Impact:

• Queue worker job selection: 200ms → 5ms
• Next job query: Index scan only (no full table scan)

Worker query:

SELECT * FROM rm_queue
WHERE status = 'pending'
ORDER BY priority ASC, created_at ASC
LIMIT 1;
-- Uses idx_status_priority (covering index)

---

Query Optimization

Pagination Best Practices

Problem: Large result sets slow down UI

Solution: Reduce page size

Orders/Items pages:

• Before: 50 rows per page (1.2s load time)
• After: 20 rows per page (0.3s load time)
• Reason: Smaller result sets = less data transfer + faster React rendering

Implementation: OrdersViewer.jsx and ItemsViewer.jsx

// Before
const perPage = 50;

// After
const perPage = 20; // Configurable via settings in future

Trade-off: More pages to navigate, but each page loads 4x faster

---

SELECT Specificity

Problem: SELECT * fetches unnecessary columns

Solution: Specify needed columns only

Example - Orders endpoint:

Bad:

$wpdb->get_results( "SELECT * FROM rm_order WHERE site = $site_id" );
// Returns 40+ columns per row, many unused

Good:

$wpdb->get_results( 
    "SELECT id, number, dateadded, ship_status, site 
     FROM rm_order 
     WHERE site = $site_id" 
);
// Returns only 5 needed columns

Impact: 70% reduction in data transferred per query

---

JOIN Optimization

Problem: Multiple queries for related data (N+1 problem)

Solution: Use JOINs to fetch related data in one query

Example - Orders with addresses:

Bad (N+1):

$orders = $wpdb->get_results( "SELECT * FROM rm_order WHERE site = $site_id LIMIT 20" );
foreach ( $orders as $order ) {
    $order->address = $wpdb->get_row( "SELECT * FROM rm_address WHERE order = $order->id" );
}
// 1 query for orders + 20 queries for addresses = 21 total queries

Good (JOIN):

$orders = $wpdb->get_results( "
    SELECT o.*, a.address, a.city, a.state, a.zip
    FROM rm_order o
    LEFT JOIN rm_address a ON a.order = o.id
    WHERE o.site = $site_id
    LIMIT 20
" );
// 1 query total

Impact: 21 queries → 1 query (95% reduction)

---

Query Logging & Analysis

Feature: Built-in query logging for performance analysis

Enable: Add to wp-config.php

define( 'GSM_LOG_QUERIES', true );

Output location: wp-content/debug.log

Example log output:

[2024-12-15 10:30:45] GSM Query #1 (0.082s): SELECT * FROM rm_order WHERE site = 5 ORDER BY dateadded DESC LIMIT 20
[2024-12-15 10:30:45] GSM Query #2 (0.015s): SELECT COUNT(*) FROM rm_order WHERE site = 5
[2024-12-15 10:30:45] Total queries: 2 | Total time: 0.097s

Analysis workflow:

1. Enable query logging
2. Load Orders page
3. Check debug.log for query times
4. Identify slow queries (>0.1s)
5. Run EXPLAIN on slow queries
6. Add indexes or optimize WHERE clauses

Disable after debugging: Remove GSM_LOG_QUERIES to avoid log file bloat

Documentation: Query Logging Guide

---

Caching Strategies

HTTP Caching (Browser Cache)

Purpose: Reduce server requests for static responses

Implementation: REST API endpoints set cache headers

Orders endpoint caching:

// src/API/Orders_REST_Controller.php
public function get_items( $request ) {
    // Set cache headers (5 minutes)
    header( 'Cache-Control: public, max-age=300' );
    header( 'Expires: ' . gmdate( 'D, d M Y H:i:s', time() + 300 ) . ' GMT' );
    
    // Return data
    return $this->site_repository->get_orders_paginated( /* ... */ );
}

Impact:

• First load: 300ms (server query)
• Subsequent loads within 5 min: 0ms (browser cache, no server request)
• Cache invalidation: Automatic after 5 minutes

When to use HTTP caching:

• ✅ List endpoints (orders, items, sites) — Data doesn't change every second
• ✅ Read-only endpoints — No user-specific data
• ❌ Real-time data — Stock levels during high-traffic sale
• ❌ User-specific data — Current user's cart, profile

---

WordPress Transients

Purpose: Cache expensive computations

Analytics Transient Caching

Dashboard KPIs (v1.18.11): KPI results are cached with a 5-minute TTL, preventing repeated heavy queries when the analytics page is reloaded.

Site List (v1.18.13): The analytics page site dropdown filter caches the site list for 10 minutes, avoiding a database round-trip to the external tasks DB on every page load.

Health Check Example

Example:

// Check cache first
$health_summary = get_transient( 'gsm_health_summary' );

if ( false === $health_summary ) {
    // Cache miss - compute expensive data
    $health_summary = [
        'orders'    => $this->get_orders_health(),     // 0.2s
        'inventory' => $this->get_inventory_health(),  // 0.3s
        'tracking'  => $this->get_tracking_health(),   // 0.2s
    ];
    
    // Cache for 5 minutes
    set_transient( 'gsm_health_summary', $health_summary, 5 * MINUTE_IN_SECONDS );
}

return $health_summary;

Impact:

• First request: 0.7s (computes all health checks)
• Cached requests: 0.001s (retrieves from wp_options)
• Cache duration: 5 minutes (configurable)

Invalidation strategies:

• Time-based: Transient expires after TTL (most common)
• Event-based: Delete transient when data changes:

  delete_transient( 'gsm_health_summary' );

---

Object Caching (WordPress Object Cache)

Purpose: In-memory caching for repeated queries within same request

Persistent object cache: Requires external cache (Redis, Memcached)

Example - Site repository:

class Site_Repository {
    public function get_by_id( int $site_id ) {
        $cache_key = "gsm_site_{$site_id}";
        
        // Try cache first
        $site = wp_cache_get( $cache_key, 'gsm_sites' );
        
        if ( false === $site ) {
            // Cache miss - query database
            $site = $wpdb->get_row( 
                $wpdb->prepare( "SELECT * FROM rm_sites WHERE id = %d", $site_id ) 
            );
            
            // Cache result for subsequent calls in same request
            wp_cache_set( $cache_key, $site, 'gsm_sites', 3600 );
        }
        
        return $site;
    }
}

Impact:

• First call: 0.01s (database query)
• Subsequent calls in same request: 0.0001s (in-memory cache)

WordPress default: Non-persistent (cache cleared after request ends)

With Redis/Memcached: Persistent across requests (requires object cache plugin)

---

Deferred Loading (Lazy Loading)

Purpose: Delay loading heavy components until after initial page render

Deferred Cron Object Creation (v1.18.27)

Problem: Plugin bootstrap eagerly instantiated heavy objects (Queue_System, Webhook_Logger, Product_Webhook_Handler, Inventory_Sync_Coordinator, Dispute_Processor, etc.) on every request — even frontend page loads that never execute cron.

Solution: Plugin::register_cron_hooks() now uses closures instead of eager instantiation:

// Before — objects created on every request
$queue = new Queue_System( $this->tasks_db );
$handler = new Product_Webhook_Handler( $queue, $this->logger );
add_action( 'gsm_process_webhooks', [ $handler, 'process' ] );

// After — objects created only when cron action fires
add_action( 'gsm_process_webhooks', function () {
    $queue = new Queue_System( $this->tasks_db );
    $handler = new Product_Webhook_Handler( $queue, $this->logger );
    $handler->process();
} );

Impact: Eliminates unnecessary object creation and database connections on frontend/admin page loads.

Deferred REST Controller Loading (v1.18.11)

All 17 REST controllers are now instantiated only on rest_api_init instead of on every page load. Admin pages, report manager, and cron hooks are similarly scoped so non-admin front-end requests skip the entire plugin bootstrap.

Dashboard Data Inlining (v1.18.8)

Problem: Dashboard loaded with a sequential waterfall — two REST API calls for reports list and sites data added ~4.5s of WordPress bootstrap overhead.

Solution: Reports and sites data are inlined into the page via wp_localize_script:

wp_localize_script( 'gsm-analytics', 'gsmAnalytics', [
    'reports' => $report_manager->get_all_reports(),
    'sites'   => $site_repository->get_active_sites(),
] );

Impact: Page renders instantly; only one REST call (KPI dashboard) needed on load.

Dashboard KPI Query Optimization (v1.18.11)

Problem: Four separate dashboard KPI queries caused slow initial load.

Solution: Merged 4 queries into 2 using scalar subqueries. Order stats, chargeback count, and average fulfillment time are now fetched in a single SQL query.

Orders page optimization:

// OrdersViewer.jsx
const [sites, setSites] = useState([]);
const [sitesLoaded, setSitesLoaded] = useState(false);

useEffect(() => {
    // Load orders immediately (critical data)
    fetchOrders();
    
    // Defer sites dropdown loading by 200ms (non-critical)
    setTimeout(() => {
        fetchSites().then((data) => {
            setSites(data);
            setSitesLoaded(true);
        });
    }, 200);
}, []);

Impact:

• Before: Page render blocked until sites loaded (600ms)
• After: Page renders immediately, sites dropdown populates 200ms later (perceived load: 300ms)

User experience: Page appears instantly, filters populate progressively

Best practices:

• Defer non-critical UI elements (filters, secondary data)
• Load critical data first (main table content)
• Show loading placeholders for deferred elements

---

React Performance

Component Memoization

Purpose: Prevent unnecessary re-renders

Use React.memo for pure components:

import { memo } from '@wordpress/element';

// Before - Re-renders on every parent update
const OrderRow = ({ order }) => (
    <tr>
        <td>{order.number}</td>
        <td>{order.customer}</td>
        <td>${order.total}</td>
    </tr>
);

// After - Only re-renders if 'order' prop changes
const OrderRow = memo(({ order }) => (
    <tr>
        <td>{order.number}</td>
        <td>{order.customer}</td>
        <td>${order.total}</td>
    </tr>
));

Impact: 50% reduction in DOM operations when filtering/sorting

---

Callback Memoization

Purpose: Prevent recreating functions on every render

Use useCallback for stable callbacks:

// Before - New function created on every render
const handleFilter = (siteId) => {
    setSiteFilter(siteId);
    fetchOrders({ site: siteId });
};

// After - Function reference stays stable
const handleFilter = useCallback((siteId) => {
    setSiteFilter(siteId);
    fetchOrders({ site: siteId });
}, [fetchOrders]); // Only recreate if fetchOrders changes

Impact: Prevents child component re-renders when passing callbacks as props

---

Virtualization (Future Enhancement)

Problem: Large tables (1000+ rows) slow to render

Solution: Render only visible rows (react-window, react-virtualized)

Example implementation:

import { FixedSizeList } from 'react-window';

const OrdersList = ({ orders }) => (
    <FixedSizeList
        height={600}
        itemCount={orders.length}
        itemSize={50}
        width="100%"
    >
        {({ index, style }) => (
            <div style={style}>
                <OrderRow order={orders[index]} />
            </div>
        )}
    </FixedSizeList>
);

Impact: 1000+ rows render in 100ms (vs. 5s without virtualization)

Status: Not yet implemented, planned for v2.0

---

Background Job Optimization

Queue Worker Performance

Problem: Processing 1000 jobs sequentially takes hours

Solution: Batch processing with concurrency limits

Implementation:

class Queue_Worker {
    const BATCH_SIZE = 10; // Process 10 jobs in parallel
    const MAX_EXECUTION = 30; // 30 seconds max per batch
    
    public function process_batch() {
        $start_time = time();
        
        while ( ( time() - $start_time ) < self::MAX_EXECUTION ) {
            // Fetch batch of pending jobs
            $jobs = $this->queue_repository->get_pending( self::BATCH_SIZE );
            
            if ( empty( $jobs ) ) {
                break; // No more jobs
            }
            
            // Process batch in parallel (if async available)
            $results = array_map( [ $this, 'process_job' ], $jobs );
            
            // Update job statuses
            $this->update_job_results( $results );
        }
    }
}

Impact:

• Before: 1000 jobs @ 0.5s each = 500s (8.3 minutes) sequentially
• After: 1000 jobs / 10 batch size @ 0.5s per batch = 50s (0.8 minutes) with concurrency

Tuning parameters:

• BATCH_SIZE: Higher = more memory usage, faster processing
• MAX_EXECUTION: PHP max_execution_time - 10 seconds (safety buffer)

---

Job Prioritization

Purpose: Process critical jobs first

Priority levels:

Priority	Use Case	Example
1 (Highest)	Real-time customer-facing	Order placed, send confirmation email
5 (Default)	Normal background sync	Inventory update every 15 min
10 (Lowest)	Maintenance tasks	Monthly report generation

Queue query optimization:

SELECT * FROM rm_queue
WHERE status = 'pending'
ORDER BY priority ASC, created_at ASC
LIMIT 10;
-- Index: idx_status_priority (status, priority, created_at)
-- Ensures high-priority jobs processed first

Impact: Critical jobs complete in seconds, bulk operations deferred to off-peak

---

Monitoring & Profiling

Performance Monitoring

Enable debug logging:

// wp-config.php
define( 'WP_DEBUG', true );
define( 'WP_DEBUG_LOG', true );
define( 'WP_DEBUG_DISPLAY', false );
define( 'SAVEQUERIES', true ); // Log all queries
define( 'GSM_LOG_QUERIES', true ); // GSM-specific query logging

View logs:

# Real-time log monitoring
tail -f wp-content/debug.log

# Filter GSM queries only
grep "GSM Query" wp-content/debug.log

# Find slow queries (>0.1s)
grep "GSM Query.*([0-9]\.[1-9]" wp-content/debug.log

---

Query Analysis Workflow

1. Identify slow queries:

grep "GSM Query.*([0-9]\.[3-9]\|[1-9]\." wp-content/debug.log
# Finds queries taking >0.3s

2. Run EXPLAIN:

EXPLAIN SELECT * FROM rm_order WHERE site = 5 ORDER BY dateadded DESC;

3. Look for red flags:

• Using temporary — Needs temp table (bad for large result sets)
• Using filesort — Sorting not using index (slow)
• ALL scan type — Full table scan (worst case)
• High rows count — Examining too many rows

4. Add index if needed:

-- If query filters by site and sorts by dateadded
ALTER TABLE rm_order ADD INDEX idx_site_dateadded (site, dateadded DESC);

5. Re-run EXPLAIN, verify improvement:

EXPLAIN SELECT * FROM rm_order WHERE site = 5 ORDER BY dateadded DESC;
-- Should now show "Using index" instead of "Using filesort"

---

Performance Benchmarks

Target metrics:

Metric	Target	Acceptable	Poor
Orders page load	<300ms	<500ms	>1s
Items page load	<300ms	<500ms	>1s
API response time	<100ms	<200ms	>500ms
Queue job processing	<0.5s/job	<1s/job	>2s/job
Database query	<0.05s	<0.1s	>0.3s

Measuring:

// Add to start of controller method
$start = microtime( true );

// ... your code ...

// Add to end
error_log( sprintf( 'Orders endpoint: %.3fs', microtime( true ) - $start ) );

---

Server-Side Optimizations

PHP Configuration

Recommended php.ini settings:

; Memory
memory_limit = 256M              ; Default: 128M (increase for large imports)
upload_max_filesize = 10M        ; For Export/Import page
post_max_size = 10M

; Execution
max_execution_time = 60          ; Default: 30 (increase for queue worker)
max_input_time = 60

; Opcache (PHP 7.0+)
opcache.enable = 1               ; Cache compiled PHP bytecode
opcache.memory_consumption = 128 ; MB
opcache.interned_strings_buffer = 8
opcache.max_accelerated_files = 4000
opcache.revalidate_freq = 60     ; Seconds

Check current values:

php -i | grep memory_limit
php -i | grep max_execution
php -i | grep opcache

---

MySQL Configuration

Recommended my.cnf settings:

[mysqld]
# Query cache (MySQL 5.7 and earlier)
query_cache_type = 1
query_cache_size = 64M
query_cache_limit = 2M

# InnoDB (default engine)
innodb_buffer_pool_size = 512M   ; 50-70% of available RAM
innodb_log_file_size = 128M
innodb_flush_log_at_trx_commit = 2

# Connections
max_connections = 150

# Temporary tables
tmp_table_size = 64M
max_heap_table_size = 64M

Monitor slow queries:

-- Enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5; -- Log queries >0.5s

-- Check slow query log location
SHOW VARIABLES LIKE 'slow_query_log_file';

---

Web Server (Nginx/Apache)

Gzip compression (Apache .htaccess):

<IfModule mod_deflate.c>
    AddOutputFilterByType DEFLATE text/html text/css text/javascript application/json
</IfModule>

Browser caching (Apache .htaccess):

<IfModule mod_expires.c>
    ExpiresActive On
    ExpiresByType text/css "access plus 1 month"
    ExpiresByType application/javascript "access plus 1 month"
    ExpiresByType image/png "access plus 1 year"
</IfModule>

Nginx gzip:

gzip on;
gzip_types text/css application/javascript application/json;
gzip_min_length 1000;

---

Optimization Checklist

Database

• Add indexes to rm_order (dateadded, ship_status)
• Add indexes to rm_sites (is_active, platform)
• Add composite index to rm_queue (status, priority, created_at)
• Use specific SELECT columns (not SELECT *)
• Optimize JOINs (avoid N+1)
• Implement query result caching (transients)
• Archive old orders (>2 years) to separate table

API Endpoints

• Implement HTTP caching (5 min cache headers)
• Reduce pagination (50 → 20 rows)
• Add query logging (GSM_LOG_QUERIES)
• Implement rate limiting (prevent abuse)
• Add compression (gzip responses)

React Components

• Implement deferred loading (sites dropdown)
• Add React.memo to pure components
• Use useCallback for stable callbacks
• Implement virtualization for large lists (future)

Queue System

• Implement priority-based processing
• Add composite index for queue queries
• Implement job retry with exponential backoff
• Add failed job archiving (>7 days old)

Server Configuration

• Enable PHP opcache
• Tune MySQL innodb_buffer_pool_size
• Enable slow query log
• Configure gzip compression
• Set up Redis/Memcached (object cache)

---

Troubleshooting Performance Issues

"Orders page slow to load"

Diagnosis:

1. Enable GSM_LOG_QUERIES
2. Load Orders page
3. Check debug.log for query times

Common causes:

• Missing indexes (check EXPLAIN output)
• Large result set (reduce pagination)
• N+1 queries (implement JOINs)

Solution:

• Add missing indexes (Migration 011)
• Reduce perPage to 20
• Check query logging guide

---

"API timeouts on large requests"

Diagnosis:

1. Check PHP max_execution_time
2. Check MySQL slow query log
3. Profile query with EXPLAIN

Common causes:

• PHP timeout (30s default)
• Slow query (>1s)
• Large dataset (10,000+ rows)

Solution:

• Increase max_execution_time to 60s
• Add indexes to filtered columns
• Implement pagination

---

"Queue worker processing slow"

Diagnosis:

1. Check queue job count: SELECT COUNT(*) FROM rm_queue WHERE status = 'pending'
2. Check average job time: Enable query logging
3. Check batch size: Queue_Worker::BATCH_SIZE

Common causes:

• Sequential processing (no batching)
• External API rate limits
• Database deadlocks

Solution:

• Increase batch size to 10-20
• Implement retry logic with backoff
• Add job prioritization

---

Related Documentation

• Query Logging Guide — Enable and analyze queries
• Orders Page Queries — Query breakdown
• Database Schema — Index reference
• Database Migrations — Apply index migrations

---

Maintenance

Quarterly:

• Review slow query log
• Analyze top 10 slowest queries
• Add indexes where beneficial
• Archive old data (orders >2 years)

Monthly:

• Check Orders/Items page load times
• Review queue job processing times
• Monitor database size growth

Weekly:

• Check error logs for performance warnings
• Review failed queue jobs

Daily:

• Monitor Control Panel health checks
• Check for API timeout errors