# Performance Review: Database Loops in ProductionRules

**Date:** 2026-03-29  
**File:** `custom/productionrules/class/productionrules.class.php`  
**Review Focus:** Identify inefficient `while ($obj = $this->db->fetch_object())` patterns that don't scale

---

## Optimized (Fixed)

### ❌ BEFORE: MAX Job Number Query (Lines ~540, ~804)
**Problem:**
```php
// Fetched ALL BOM labels (potentially millions of rows)
$sqlMaxJob = "SELECT b.label FROM llx_bom_bom AS b WHERE ...";
$resMaxJob = $this->db->query($sqlMaxJob);
$maxJobFound = 0;
while ($objM = $this->db->fetch_object($resMaxJob)) {
    if (preg_match('/\(Job\s+(\d+)\)/i', $objM->label, $mJob)) {
        $maxJobFound = max($maxJobFound, (int) $mJob[1]);
    }
}
```

**Issues:**
- O(n) memory: all labels transferred to PHP
- O(n) CPU: regex parsing in PHP loop
- No early termination
- At 10M BOMs: ~500MB transfer + seconds of regex processing

### ✅ AFTER: SQL-side MAX Extraction
```php
// Returns only 1 row with computed MAX job number
$sqlMaxJob = "SELECT MAX("
    ." CAST("
    ."   SUBSTRING_INDEX(SUBSTRING_INDEX(b.label, '(Job ', -1), ')', 1)"
    ."   AS UNSIGNED"
    ." )"
    .") as max_job"
    ." FROM ".MAIN_DB_PREFIX."bom_bom AS b"
    ." WHERE b.entity = ".(int) $conf->entity
    ." AND b.label LIKE '%(Job %)'"
    ." AND (b.note_public LIKE '%Generated by ProductionRules%'"
    ."      OR b.label LIKE '%Auto BOM%Job%')";
$resMaxJob = $this->db->query($sqlMaxJob);
$maxJobFound = 0;
if ($resMaxJob && ($objM = $this->db->fetch_object($resMaxJob))) {
    $maxJobFound = (int) $objM->max_job;
}
```

**Benefits:**
- O(1) result: single integer
- Parsing + MAX in MySQL native code (C++)
- `LIKE '%(Job %)'` pre-filters rows (index range scan if available)
- At 10M BOMs: <1KB transfer, sub-second execution

**Applied to:**
- `generateBomForRuleset()` (~line 540)
- `generateDownstreamBoms()` (~line 810)

---

## Acceptable (No Changes Needed)

### ✅ Line 175: `fetchAll()` - Rule List Retrieval
```php
while ($obj = $this->db->fetch_object($res)) {
    $out[] = $obj;
}
```
**Status:** OK  
**Reason:** Returns data to caller, no aggregation. Expected <1000 rules per user.

---

### ✅ Line 286: `computeRequirements()` - Rule Graph Construction
```php
while ($obj = $this->db->fetch_object($res)) {
    if (!empty($obj->is_raw)) $rawRefs[$obj->from_ref] = true;
    $allRules[$obj->to_ref][] = $obj;
}
```
**Status:** OK  
**Reason:** Builds lookup map for BFS traversal. All rules needed. Filtered by user + entity.  
**Scale:** Expected <10K rules per user.  
**Note:** Pre-load pattern avoids N+1 queries.

---

### ✅ Line 376: `generateBomForProduct()` - Requirement Aggregation
```php
while ($obj = $this->db->fetch_object($resql)) {
    $ref = $obj->from_ref;
    if (!isset($requirements[$ref])) {
        $requirements[$ref] = array('qty' => 0, 'step_order' => (int) $obj->step_order);
    }
    $requirements[$ref]['qty'] += ((float) $qty_fg * (float) $obj->qty_per_from);
    if ((int) $obj->step_order < $requirements[$ref]['step_order']) {
        $requirements[$ref]['step_order'] = (int) $obj->step_order;
    }
}
```
**Status:** Acceptable  
**Potential Optimization:**
```sql
-- Could be replaced with GROUP BY in SQL:
SELECT from_ref, 
       SUM(qty_per_from) as total_qty, 
       MIN(step_order) as min_step
FROM llx_prod_rules
WHERE to_ref = ? AND entity = ? AND fk_user_creat = ?
GROUP BY from_ref
ORDER BY min_step, from_ref
```
**Priority:** Low  
**Reason:** Filtered by `to_ref + user + optional ruleset`. Expected <100 rows per FG product.  
**When to optimize:** If single FG has >1000 incoming rules.

---

### ✅ Line 613: `generateBomForRuleset()` - Routing Line Collection
```php
while ($obj = $this->db->fetch_object($resql)) {
    $step = (int) $obj->step_order;
    $op   = trim($obj->operation);
    $ws   = trim((string) $obj->workstation);
    if ($op === '') continue;
    $line = $step . ' ' . $op;
    if ($ws !== '' && strcasecmp($ws, $op) !== 0) $line .= ' ' . $ws;
    $lines[] = $line;
}
```
**Status:** OK  
**Reason:** `SELECT DISTINCT step_order, operation` filtered by ruleset. Small result set (<50 rows).

---

### ✅ Line 766: `generateDownstreamBoms()` - Edge Graph Construction
```php
while ($obj = $this->db->fetch_object($resql)) {
    $from = trim($obj->from_ref);
    $to   = trim($obj->to_ref);
    // ... builds $edges, $routing, $roots arrays
}
```
**Status:** OK  
**Reason:** Filtered by `fk_ruleset + active + entity`. Expected <500 rules per ruleset.

---

### ✅ Line 937: `buildServiceMap()` - Service Product Lookup
```php
while ($obj = $this->db->fetch_object($res)) {
    $id  = (int) $obj->rowid;
    $ref = trim($obj->ref);
    $lbl = trim($obj->label);
    $map[strtolower($ref)] = $id;
    $map[strtolower(str_replace('_', ' ', $ref))] = $id;
    // ... mapping variations
}
```
**Status:** Acceptable  
**Concern:** Fetches ALL service products (no user filter). Could be 1000s in multi-tenant setup.  
**Optimization Options:**
1. Cache map in PHP session (`$_SESSION['serviceMap']`)
2. Add WHERE filter if services are user-specific
3. Lazy load only when routing services detected

**Priority:** Low to Medium  
**When to optimize:** If service product count >10K or function called frequently.

---

### ✅ Line 959: `buildServiceMap()` - Workstation Label Matching
```php
while ($obj = $this->db->fetch_object($resWs)) {
    $wsRef   = strtolower(trim($obj->ref));
    $wsWords = preg_split('/\s+/', trim($obj->label));
    // ... progressive prefix matching
}
```
**Status:** OK  
**Reason:** Workstations typically <100 per entity. Prefix matching logic necessary for fuzzy resolution.

---

### ✅ Line 991: `buildWorkstationIdMap()` - Workstation ID Lookup
```php
while ($obj = $this->db->fetch_object($res)) {
    $map[strtolower(trim($obj->ref))] = (int) $obj->rowid;
}
```
**Status:** OK  
**Reason:** Simple ref→id mapping. <100 workstations expected.

---

## Summary

| Line | Function | Status | Scale | Action |
|------|----------|--------|-------|--------|
| ~540 | `generateBomForRuleset()` MAX job | **FIXED** | 10M BOMs | SQL MAX optimization |
| ~810 | `generateDownstreamBoms()` MAX job | **FIXED** | 10M BOMs | SQL MAX optimization |
| 175 | `fetchAll()` | ✅ OK | <1K rules | No change |
| 286 | `computeRequirements()` | ✅ OK | <10K rules | No change |
| 376 | `generateBomForProduct()` | ⚠️ Low priority | <100 rules/FG | Could use GROUP BY |
| 613 | `generateBomForRuleset()` routing | ✅ OK | <50 steps | No change |
| 766 | `generateDownstreamBoms()` edges | ✅ OK | <500 rules | No change |
| 937 | `buildServiceMap()` services | ⚠️ Medium priority | <10K products | Could cache |
| 959 | `buildServiceMap()` workstations | ✅ OK | <100 ws | No change |
| 991 | `buildWorkstationIdMap()` | ✅ OK | <100 ws | No change |

**Critical optimization completed.** Remaining loops are acceptable for current scale.

---

## Recommendations

### Immediate (Done)
- ✅ MAX job number query optimized with SQL `SUBSTRING_INDEX` + `CAST`

### Future (If Scaling Issues)
1. **Line 376** - Aggregate requirements in SQL:
   ```sql
   SELECT from_ref, SUM(qty_per_from * ?) as total_qty, MIN(step_order) as min_step
   FROM llx_prod_rules WHERE to_ref = ? GROUP BY from_ref
   ```

2. **Line 937** - Cache service map:
   ```php
   private static $serviceMapCache = null;
   if (self::$serviceMapCache === null) {
       self::$serviceMapCache = $this->buildServiceMap();
   }
   ```

3. **General** - Add composite index for job queries:
   ```sql
   ALTER TABLE llx_bom_bom ADD INDEX idx_entity_label (entity, label(50));
   ```

### Monitoring
- Watch for BOM table growth >1M rows
- Profile `buildServiceMap()` if service count >5K
- Consider caching if `generateBomForRuleset()` called >100/min