# GB5Framework — Performance & Memory Analysis

**Date:** 2026-03-02
**Scope:** All layers — Framework, Shared, Solution modules
**Focus:** Database performance, async patterns, memory allocation, caching, connection management

---

## Performance Impact Matrix

| Issue | Requests Affected | Estimated Overhead | Priority |
|-------|------------------|-------------------|----------|
| DB connection leak (pool exhaustion) | ALL under load | App failure at ~100 req | 🔴 P0 |
| HttpClient per-request (socket exhaustion) | Auth requests | App failure at ~100 req | 🔴 P0 |
| N+1 queries in WorkFlow | Workflow ops | 10–50x query overhead | 🟠 P1 |
| 3-roundtrip workflow definition load | Workflow ops | 30–50ms added latency | 🟠 P1 |
| Separate DB call per validated field | Save operations | 20+ extra DB calls per save | 🟠 P1 |
| Task.Run on sync code | All async ops | Thread pool contention | 🟠 P1 |
| Reflection in event publish hot path | Every event | 10–50x slower | 🟠 P1 |
| JSON serialization round-trips | ALL requests | ~2x CPU per request | 🟡 P2 |
| ToList() instead of FirstOrDefault | List queries | 10–100x memory alloc | 🟡 P2 |
| SQL string manipulation | DailyAttendance | Fragile + query plan miss | 🟡 P2 |
| SHA256 hash of full DTO | Cached requests | Unnecessary CPU | 🟢 P3 |
| Null assignment in finally | Save operations | No actual impact | 🟢 P3 |

---

## PERF-01: Database Connection Leak — Connection Pool Exhaustion

**Severity:** 🔴 CRITICAL (Application failure under load)
**File:** `GB5Shared/QueryExecutor/QueryExecutor.cs:85–121`

**Problem:**
```csharp
public async Task<DbTransaction> BeginTransactionAsync(LoginDTO LoginDTO)
{
    var connection = await CreateNewConnectionAsync(LoginDTO);
    await connection.OpenAsync();
    var transaction = connection.BeginTransaction();
    return (transaction);  // Connection is LOST — never disposed
}

private async Task CleanupAsync(DbTransaction? transaction)
{
    if (transaction != null)
        try { await transaction.DisposeAsync(); } catch { }
    // Connection cleanup COMMENTED OUT — permanent leak
}
```

**What happens at scale:**
- SQL Server default pool: 100 connections
- Each `BeginTransactionAsync` leaks 1 connection
- After 100 transactions: `InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool`
- Application becomes completely unresponsive to all requests

**Fix:**
```csharp
// Return a composite disposable that owns both connection and transaction
public async Task<ITransactionScope> BeginTransactionAsync(LoginDTO loginDTO)
{
    var connection = await CreateNewConnectionAsync(loginDTO);
    await connection.OpenAsync();
    var transaction = await connection.BeginTransactionAsync();
    return new TransactionScope(connection, transaction);
}

// TransactionScope implements IAsyncDisposable
public sealed class TransactionScope : IAsyncDisposable
{
    private readonly DbConnection _connection;
    private readonly DbTransaction _transaction;

    public DbTransaction Transaction => _transaction;

    public async ValueTask DisposeAsync()
    {
        await _transaction.DisposeAsync();
        await _connection.DisposeAsync(); // Connection returned to pool
    }
}

// Usage (in BLL/DAL):
await using var scope = await _queryExecutor.BeginTransactionAsync(login);
try
{
    await _queryExecutor.ExecuteAsync(login, sql, param, scope.Transaction);
    await scope.Transaction.CommitAsync();
}
catch
{
    await scope.Transaction.RollbackAsync();
    throw;
}
```

---

## PERF-02: HttpClient Created Per Request — Socket Exhaustion

**Severity:** 🔴 CRITICAL (Application failure under auth load)
**File:** `GB5Framework/FrameworkSL/Controllers/KeyCloakService.cs:148, 508, 548, 584, 628`

**Problem:**
```csharp
// Called on every Keycloak operation
using (var client = new HttpClient(new HttpClientHandler { ... }))
{
    var response = await client.PostAsync(url, formData);
    // client disposed — but socket stays in TIME_WAIT for 240s
}
```

**What happens:**
- Each login/logout/token-refresh creates and disposes an HttpClient
- Disposed sockets remain in `TIME_WAIT` state for ~4 minutes
- Ephemeral port range: ~16,000 ports on Windows
- At ~70 auth requests/minute: port exhaustion → `SocketException: Address already in use`

**Fix:**
```csharp
// Register in Program.cs — HttpClientFactory manages socket pooling
builder.Services.AddHttpClient("keycloak", client =>
{
    client.BaseAddress = new Uri(config["Keycloak:BaseUrl"]!);
    client.Timeout = TimeSpan.FromSeconds(30);
}).ConfigurePrimaryHttpMessageHandler(() =>
{
    var handler = new HttpClientHandler();
    // Only bypass cert validation in dev (from SEC-01 fix)
    return handler;
});

// Inject IHttpClientFactory in KeyCloakService
private readonly HttpClient _keycloakClient;

public KeyCloakService(IHttpClientFactory httpClientFactory, ...)
{
    _keycloakClient = httpClientFactory.CreateClient("keycloak");
    // Same HttpClient (and underlying socket pool) reused across requests
}
```

---

## PERF-03: N+1 Queries in Workflow Processing

**Severity:** 🟠 HIGH
**File:** `GB5Shared/QueryExecutor/QueryExecutor.cs:640–660` and `WorkFlowEngine.cs:604–635`

**Problem:**
```csharp
// Loads list of workflows, then queries DB for each one
var workflows = await GetPendingWorkflowsAsync(login);
foreach (var workflow in workflows)
{
    // Separate DB call per workflow — N+1 pattern
    await ProcessWorkflowItem(workflow, login);
    // ProcessWorkflowItem internally calls GetRelatedData(workflow.Id, login)
}
```

**Performance impact:**
- 50 pending workflows = 51 DB queries (1 list + 50 detail loads)
- At 10ms per query = 500ms minimum for batch processing
- Under load, this compounds: 100ms list + 50 × 50ms = 2.6 seconds per scheduling cycle

**Fix:**
```csharp
// Load all workflows AND their related data in one query using JOIN/multi-select
var (workflows, taskMap) = await LoadWorkflowsWithTasksAsync(login);

// Or use Dapper multi-mapping:
var sql = @"
    SELECT w.*, t.*
    FROM WorkflowInstances w
    INNER JOIN WorkflowTasks t ON t.WorkflowId = w.Id
    WHERE w.Status = 'PENDING'";

var workflowMap = new Dictionary<int, WorkflowInstance>();
await _db.QueryAsync<WorkflowInstance, WorkflowTask, WorkflowInstance>(
    sql,
    (workflow, task) =>
    {
        if (!workflowMap.TryGetValue(workflow.Id, out var existing))
        {
            workflowMap[workflow.Id] = workflow;
            workflow.Tasks = new List<WorkflowTask>();
        }
        workflowMap[workflow.Id].Tasks.Add(task);
        return workflow;
    });
```

---

## PERF-04: N+1 LINQ Scan in AutoTransitionAsync

**Severity:** 🟠 HIGH
**File:** `GB5Shared/WorkFlow/WorkFlowEngine/WorkFlowEngine.cs:604–635`

**Problem:**
```csharp
// O(n*m) nested iteration — n transitions × m steps
foreach (var transition in def.Transitions)
{
    // def.Steps.First() is O(m) linear scan, called for each of n transitions
    var step = def.Steps.First(s => s.WorkflowDetailId == transition.FromStepId);
}
```

**Performance impact with typical workflow:**
- 20 transitions × 50 steps = 1,000 iterations per auto-transition check
- Called every scheduling cycle (every few seconds)

**Fix:**
```csharp
// Build dictionary once — O(m) setup, O(1) per lookup
var stepById = def.Steps.ToDictionary(s => s.WorkflowDetailId);
var transitionsByFrom = def.Transitions
    .GroupBy(t => t.FromStepId)
    .ToDictionary(g => g.Key, g => g.ToList());

foreach (var transition in def.Transitions)
{
    if (stepById.TryGetValue(transition.FromStepId, out var step))  // O(1)
    {
        // Process transition
    }
}
```

---

## PERF-05: 3–5 Database Roundtrips per Workflow Definition Load

**Severity:** 🟠 HIGH
**File:** `GB5Shared/WorkFlow/WorkFlowRunTime/WorkFlowRunTime.cs:354–403`

**Problem:**
```csharp
// Query 1
var workflow = await _db.QuerySingleAsync<WorkflowDTO>(workflowSql, params);
// Query 2
var steps = await _db.QueryAsync<WorkflowStepDTO>(stepsSql, new { WorkflowId = workflow.Id });
// Query 3
var transitions = await _db.QueryAsync<WorkflowTransitionDTO>(transSql, new { WorkflowId = workflow.Id });
// Query 4
var groups = await _db.QueryAsync<RuleGroupDTO>(groupSql, new { WorkflowId = workflow.Id });
// Query 5
var rules = await _db.QueryAsync<RuleDTO>(rulesSql, new { WorkflowId = workflow.Id });
```

**Performance impact:** 50ms minimum latency per definition load. In a busy workflow scheduler with 100 definitions to check, this is 5 seconds just for loading.

**Fix:** Use Dapper's `QueryMultipleAsync`:
```csharp
var definitionSql = @"
    SELECT * FROM WorkflowDefinitions WHERE Id = @Id;
    SELECT * FROM WorkflowSteps WHERE WorkflowId = @Id;
    SELECT * FROM WorkflowTransitions WHERE WorkflowId = @Id;
    SELECT * FROM WorkflowRuleGroups WHERE WorkflowId = @Id;
    SELECT * FROM WorkflowRules WHERE WorkflowId = @Id;";

using var multi = await _db.QueryMultipleAsync(definitionSql, new { Id = workflowId });
var definition = await multi.ReadSingleAsync<WorkflowDTO>();
definition.Steps = (await multi.ReadAsync<WorkflowStepDTO>()).ToList();
definition.Transitions = (await multi.ReadAsync<WorkflowTransitionDTO>()).ToList();
definition.RuleGroups = (await multi.ReadAsync<RuleGroupDTO>()).ToList();
definition.Rules = (await multi.ReadAsync<RuleDTO>()).ToList();

// 1 roundtrip instead of 5 — 80% latency reduction
```

---

## PERF-06: Separate DB Connection per Validated Field

**Severity:** 🟠 HIGH
**File:** `GB5Shared/Validation/Validation.cs:115–131`

**Problem:**
```csharp
// Called once per field — if 20 fields, 20 DB connections
public async Task<bool> MaxLength(string fieldName, string value, LoginDTO login)
{
    // New connection opened for every single field validation
    var maxLen = await GetColumnMaxLengthAsync(fieldName, login);
    return value.Length <= maxLen;
}
```

**Performance impact:** 20-field form with MaxLength validation = 20 DB connections × ~10ms = 200ms of validation overhead per save operation.

**Fix:** Cache schema metadata at startup (it never changes at runtime):
```csharp
private readonly ConcurrentDictionary<string, int> _maxLengthCache = new();

public async Task<bool> MaxLength(string fieldName, string value, LoginDTO login)
{
    var cacheKey = $"{login.DatabaseName}:{login.Schema}:{fieldName}";

    var maxLen = await _maxLengthCache.GetOrAddAsync(cacheKey, async key =>
    {
        return await GetColumnMaxLengthAsync(fieldName, login);
    });

    return value?.Length <= maxLen;
}
```

**Or** pre-load all column metadata at startup for known entities.

---

## PERF-07: Task.Run Wrapping Synchronous Code

**Severity:** 🟠 HIGH (Thread pool anti-pattern)
**Files:**
- `GB5Shared/Validation/Validation.cs:40–52, 56–77, 134–142`
- `GB5Shared/GB5CommonFunction/GB5CommonFunction.cs:120–130`

**Problem:**
```csharp
public async Task<bool> NotNull(string value)
{
    return await Task.Run(() => !string.IsNullOrEmpty(value));
}

public async Task<int> CountArrayAsync<T>(IEnumerable<T>? array)
{
    return await Task.Run(() => array?.Count() ?? 0);
}
```

**What's wrong:**
1. `Task.Run` queues work on the thread pool — costs a context switch (several microseconds)
2. The actual work (`!string.IsNullOrEmpty`) takes nanoseconds
3. Under high load, thread pool saturation causes queuing delays
4. `async` methods have state machine overhead even when no actual I/O occurs

**Fix:** Make these methods synchronous:
```csharp
public static bool NotNull(string? value) => !string.IsNullOrEmpty(value);
public static bool NotEmpty(string? value) => !string.IsNullOrWhiteSpace(value);
public static int CountArray<T>(IEnumerable<T>? collection) => collection?.Count() ?? 0;
```

---

## PERF-08: Reflection in Event Publish Hot Path

**Severity:** 🟠 HIGH
**File:** `GB5Shared/EventLogPublish/EventLogPublish.cs:124–128`

**Problem:**
```csharp
// Called on every event publish — potentially thousands per second
var props = eventData.GetType().GetProperties();  // Reflection every time
foreach (var prop in props)
{
    dict[prop.Name] = prop.GetValue(eventData)?.ToString();
}
```

**Performance impact:**
- `GetType().GetProperties()` is typically 100-500ns per call (vs ~1ns for direct access)
- `GetValue(obj)` via reflection is ~10-50x slower than direct property access
- For high-frequency events this becomes a bottleneck

**Fix:** Cache PropertyInfo per type:
```csharp
private static readonly ConcurrentDictionary<Type, (string Name, Func<object, object?> Getter)[]>
    _propertyCache = new();

private static (string Name, Func<object, object?> Getter)[] GetCachedProperties(Type type)
    => _propertyCache.GetOrAdd(type, t =>
        t.GetProperties()
         .Select(p => (p.Name, Getter: (Func<object, object?>)p.GetValue))
         .ToArray());

// Usage (getters are cached, reflection only happens once per type):
var props = GetCachedProperties(eventData.GetType());
foreach (var (name, getter) in props)
    dict[name] = getter(eventData)?.ToString();
```

**Even better:** Use `System.Text.Json` source generation for zero-reflection serialization.

---

## PERF-09: JSON Serialization Round-Trip in DAL → BLL → SL

**Severity:** 🟡 MEDIUM (Systemic, affects all requests)
**Files:** All DAL files across all 18 modules

**Problem:**
```csharp
// DAL: Deserialize from DB, then serialize to JSON
UserDTO user = await _db.QuerySingleAsync<UserDTO>(sql, params);
string json = JsonConvert.SerializeObject(user);  // Allocates JSON string
return json;                                       // Return string

// BLL: Pass-through (strings all the way up)
return await _dal.GetUser(userId, login);

// SL Endpoint: Deserialize JSON back to DTO
UserDTO dto = JsonConvert.DeserializeObject<UserDTO>(result);
// FastEndpoints then serializes dto back to JSON for HTTP response
```

**3 serialize/deserialize operations per request instead of 0:**
1. Dapper maps DB row → `UserDTO` (fast, compiled)
2. JSON serialize `UserDTO` → `string` (allocates ~KB string on heap)
3. JSON deserialize `string` → `UserDTO` again (unnecessary)
4. FastEndpoints serializes `UserDTO` → HTTP response body (necessary)

**Memory impact:** Each request allocates an intermediate JSON string — for a 1KB DTO at 100 req/s = 100KB/s of unnecessary garbage, causing more frequent GC pauses.

**Fix:** Return typed DTOs from DAL:
```csharp
// DAL
public async Task<UserDTO?> GetUser(int userId, LoginDTO login)
    => await _queryExecutor.QuerySingleAsync<UserDTO>(login, sql, new { userId });

// BLL
public async Task<UserDTO?> GetUser(int userId, LoginDTO login)
    => await _userDAL.GetUser(userId, login);

// Endpoint — FastEndpoints handles HTTP serialization
var user = await _userBLL.GetUser(req.UserId, loginDTO);
if (user is null) { await SendNotFoundAsync(ct); return; }
await SendOkAsync(user, ct);  // Only ONE serialization — to HTTP response
```

---

## PERF-10: ToList() When FirstOrDefault() Suffices

**Severity:** 🟡 MEDIUM
**File:** `Admin/AdminDAL/CustomCode/BIZTransactionType/BIZTransactionTypeDAL.cs:116, 191, 210`

**Problem:**
```csharp
// Materializes ALL matching records into a List
List<BIZTransactionTypeDTO> types =
    (await GetBizTransactionTypeNew(BIZTransactionTypeId, LoginDTO)).ToList();

// Then only uses element [0]
DateTime Dt = await GetBasicLockDateForOpenAndClose(
    types[0].BIZTransactionTypeId, ...);
```

**Memory impact:** If `GetBizTransactionTypeNew` returns 500 records, all 500 are allocated into a `List<>` when only index 0 is used.

**Fix:**
```csharp
var firstType = (await GetBizTransactionTypeNew(BIZTransactionTypeId, LoginDTO))
    .FirstOrDefault()
    ?? throw new KeyNotFoundException($"BIZTransactionType {BIZTransactionTypeId} not found");

DateTime Dt = await GetBasicLockDateForOpenAndClose(firstType.BIZTransactionTypeId, ...);
```

**Better:** Fix the query in `GetBizTransactionTypeNew` to select only the single record needed (add `TOP 1` or `LIMIT 1`).

---

## PERF-11: MemoryCache for Workflow Definitions — No Invalidation

**Severity:** 🟡 MEDIUM
**File:** `GB5Shared/WorkFlow/WorkFlowEngine/WorkFlowEngine.cs:29–30`

**Problem:**
- Workflow definitions cached for 10 minutes with `IMemoryCache`
- No invalidation when workflow definition is modified
- In multi-instance deployment, different instances may have different cached versions
- If workflow is modified, changes don't take effect for up to 10 minutes

**Fix:** Use distributed cache with explicit invalidation:
```csharp
// When workflow definition is saved/modified (in WorkflowBLL or DAL):
await _daprClient.DeleteStateAsync("statestore", $"workflow:def:{workflowId}");

// Or publish cache invalidation event:
await _daprClient.PublishEventAsync("pubsub", "workflow-def-invalidated",
    new { WorkflowId = workflowId });

// All instances subscribe and clear their local cache:
// [Topic("pubsub", "workflow-def-invalidated")]
// public async Task OnWorkflowDefInvalidated(WorkflowInvalidatedEvent evt)
// {
//     _memoryCache.Remove($"workflow:def:{evt.WorkflowId}");
// }
```

---

## PERF-12: SHA256 Hash of Full CriteriaDTO for Cache Key

**Severity:** 🟢 LOW-MEDIUM
**File:** `GB5Shared/DaprCache/CacheKeyGeneration.cs:89–100`

**Problem:**
```csharp
// Serialize entire CriteriaDTO to JSON, then hash it
string json = JsonConvert.SerializeObject(criteriaDTO);
using var sha256 = SHA256.Create();  // Creates new SHA256 instance each time
byte[] hash = sha256.ComputeHash(Encoding.UTF8.GetBytes(json));
```

**Fix:**
```csharp
// 1. Use SHA256.HashData static method (.NET 5+) — no instantiation needed
// 2. Hash only query-relevant fields, not entire object

public string GenerateKey(CriteriaDTO criteria, string entityName, string operation)
{
    // Only hash fields that actually affect query results
    var keySource = $"{entityName}|{operation}|{criteria.PageSize}|{criteria.PageNumber}" +
                    $"|{criteria.SortField}|{criteria.SortDirection}|{criteria.FilterJson}";

    var hash = SHA256.HashData(Encoding.UTF8.GetBytes(keySource));
    return $"v1:{Convert.ToHexString(hash)[..16]}"; // First 16 hex chars = 64 bits, sufficient for cache key
}
```

---

## Memory Management Best Practices Summary

### Patterns to Eliminate

| Pattern | Replacement | Where Found |
|---------|-------------|-------------|
| `new HttpClient(...)` in methods | `IHttpClientFactory` | KeyCloakService (5 places) |
| `new SqlConnection(...)` per call | Connection pooling (already exists, just don't leak) | ApplicationConnection |
| `await Task.Run(() => syncOp)` | Make method synchronous | Validation, CommonFunction |
| `list.ToList()` before `[0]` | `.FirstOrDefault()` | BIZTransactionTypeDAL |
| JSON `string` return from DAL | Return typed `DTO` | All 18 modules |
| `GetType().GetProperties()` in loop | Cache PropertyInfo array | EventLogPublish |
| `finally { variable = null; }` | Remove (GC handles it) | Multiple DAL files |
| Unused injected services | Remove constructor parameter | AccountsDAL (DaprClient) |

---

## Async Pattern Best Practices

### Anti-patterns Found

```csharp
// ❌ Anti-pattern 1: Task.Run on synchronous code
public async Task<bool> IsNull(string value)
    => await Task.Run(() => value == null);

// ❌ Anti-pattern 2: Async method with no actual I/O
public async Task<string> FormatName(string first, string last)
    => await Task.FromResult($"{first} {last}");

// ❌ Anti-pattern 3: .Result or .Wait() (not found but warn against)
var result = asyncMethod().Result; // Deadlock risk
```

### Correct Patterns

```csharp
// ✅ Synchronous where no I/O
public bool IsNull(string? value) => value is null;
public string FormatName(string first, string last) => $"{first} {last}";

// ✅ ConfigureAwait(false) in library code (not strictly needed in ASP.NET Core but good practice)
var data = await _db.QueryAsync<T>(sql, param).ConfigureAwait(false);

// ✅ Cancellation token propagation
public async Task<T> GetAsync(int id, CancellationToken ct = default)
{
    return await _db.QuerySingleAsync<T>(sql, new { id }, cancellationToken: ct);
}

// ✅ ValueTask for frequently synchronous paths
public async ValueTask<T?> GetFromCacheAsync(string key)
{
    if (_cache.TryGetValue(key, out T? cached)) return cached; // Sync path
    var value = await LoadFromDbAsync(key);  // Only async when needed
    _cache.Set(key, value);
    return value;
}
```

---

## Estimated Performance Gains After Fixes

| Fix | Estimated Gain |
|-----|---------------|
| Fix connection leak (PERF-01) | Prevents application failure at scale |
| Fix HttpClient per-request (PERF-02) | Prevents socket exhaustion under auth load |
| Fix N+1 workflows (PERF-03/04) | ~10x reduction in workflow processing time |
| Fix 5-roundtrip definition load (PERF-05) | ~80% reduction in definition load latency |
| Fix per-field DB validation (PERF-06) | ~200ms faster per save (removes 20 DB calls) |
| Fix Task.Run on sync (PERF-07) | Reduces thread pool pressure, faster response at scale |
| Fix reflection caching (PERF-08) | ~50x faster per event publish |
| Fix JSON round-trip (PERF-09) | ~50% GC pressure reduction per request |
| Fix ToList() → FirstOrDefault (PERF-10) | Significant memory reduction for large queries |

**Overall estimated throughput improvement after all fixes: 3–10x under concurrent load.**