Investigation: 2026-02-27 — HisdDbReport SQL Performance Analysis

Summary

Analysis of all 8 SQL objects in HisdDbReport (5 functions, 3 stored procedures) plus 2 views in HisdDb revealed systemic performance killers and correctness bugs. The single biggest issue: 4 of 5 functions are multi-statement table-valued functions (msTVFs) that SQL Server cannot inline, producing terrible execution plans and preventing parallelism. The primary string splitter function (rptGetSplittedResult) is called by 50+ stored procedures across the codebase.

Field	Value
Severity	SEV3 — Chronic performance degradation
Status	Root cause identified, fixes pending
Investigated by	Leon Geldenhuys
Investigation date	2026-02-27
Affected system	HisdDbReport — SSRS reporting database
Impact	Slow reports, lock contention with OLTP, potential data correctness issues
Jira	HM-8489 under epic HM-8342

---

Objects Analyzed

Type	Name	Location
Function	rptGetSplittedResult	HisdDbReport/dbo/Functions/
Function	rptfn_Split	HisdDbReport/dbo/Functions/
Function	rptfnSelCampusList	HisdDbReport/dbo/Functions/
Function	rptfnSelServiceList	HisdDbReport/dbo/Functions/
Function	rptfnGetStudentsByDistrictAndTime	HisdDbReport/dbo/Functions/
Stored Proc	rptActivityTracking	HisdDbReport/dbo/Stored Procedures/
Stored Proc	rptGetIEPReport	HisdDbReport/dbo/Stored Procedures/
Stored Proc	3rd SP	HisdDbReport/dbo/Stored Procedures/
View	vwCurrentSchoolYearDate	HisdDb/dbo/Views/

---

CRITICAL: Multi-Statement Table-Valued Functions

This is the #1 performance killer. Four of 5 functions use the RETURNS @table TABLE ... BEGIN ... END pattern (multi-statement TVFs), which SQL Server cannot inline. The optimizer always estimates 1 row of output regardless of actual data, producing terrible execution plans and preventing parallelism.

Function	Problem	Blast Radius
rptGetSplittedResult	WHILE loop to split CSV strings	50+ stored procedures across the codebase
rptfn_Split	Same WHILE loop CSV splitter (duplicate!)	Used by rptfnSelCampusList + rptfnSelServiceList
rptfnSelCampusList	msTVF with data access + calls rptfn_Split	Nested opaque function chain
rptfnSelServiceList	msTVF with data access + calls rptfn_Split	Nested opaque function chain

The Fix — Inline TVF using STRING_SPLIT()

Drop-in replacement (SQL Server 2016+)

CREATE FUNCTION [dbo].[rptGetSplittedResult](@input varchar(2000))
RETURNS TABLE AS
RETURN (
    SELECT LTRIM(RTRIM(value)) AS ID
    FROM STRING_SPLIT(@input, ',')
    WHERE LTRIM(RTRIM(value)) <> ''
);

Single biggest win

This one change improves every stored procedure that calls the function — no calling code changes needed. The optimizer can see through inline TVFs, producing correct cardinality estimates and enabling parallel execution plans.

Jira: HM-8490

---

HIGH: Additional Performance Issues

No READ UNCOMMITTED on Any Report Query

All 3 report stored procedures take shared locks on every read, causing contention with OLTP writes. Reports are read-only and tolerate slightly stale data.

Fix: Add SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; at the top of each SP.

Jira: HM-8491

rptActivityTracking — 4x Function Calls in WHERE

Lines 94-97 — optimizer is blind on each call

AND [CA].[Id] IN (SELECT ID FROM dbo.rptGetSplittedResult(@CampusIDs))
AND [SE].[ServiceId] IN (SELECT ID FROM dbo.rptGetSplittedResult(@ServiceIDs))
AND [VI].[Status] IN (SELECT ID FROM dbo.rptGetSplittedResult(@StatusIDs))
AND [VI].[ClinicianId] IN (SELECT ID FROM dbo.rptGetSplittedResult(@ClinicianIDs))

Each call materializes a table variable with a 1-row estimate. After converting to inline TVF (HM-8490), this becomes efficient.

4 Separate Parameter Extraction Queries

Both rptActivityTracking (lines 30-56) and rptGetIEPReport (lines 15-41) run 4 separate 3-table JOINs to fetch individual parameters. Should be 1 pivot query:

Single query replacement

SELECT
    @DistrictID     = MAX(CASE WHEN RP.ParameterName = 'DistrictId' THEN REP.ParameterValue END),
    @DateRangeStart = MAX(CASE WHEN RP.ParameterName = 'StartDate' THEN REP.ParameterValue END),
    @DateRangeEnd   = MAX(CASE WHEN RP.ParameterName = 'EndDate' THEN REP.ParameterValue END),
    @DistrictSchoolYear = MAX(CASE WHEN RP.ParameterName = 'DistrictScoolYear' THEN REP.ParameterValue END)
FROM ReportExecution RE
JOIN ReportExecutionParameter REP ON REP.ReportExecutionId = RE.Id
JOIN ReportParameter RP ON RP.Id = REP.ReportParameterId
WHERE RE.ReportExecutionId = @ReportExecutionId;

Jira: HM-8492

~240 Lines of Duplicated Code + 6x Correlated Subquery

rptActivityTracking has 3 nearly identical IF branches (lines 100-344) differing only in the WHERE clause. The same NOT EXISTS / EXISTS check against StudentMedicaidEffectiveDate runs 6 times.

Fix: Pre-compute HasMedicaid BIT flag once, collapse to single query.

Jira: HM-8493

SELECT * and Missing Temp Table Indexes

• 6 instances of SELECT * materializing unnecessary columns to tempdb
• 5 temp tables with no indexes — every IN (SELECT ... FROM #t) is a full scan
• LIKE '%Transportation%' repeated 5 times — leading wildcard prevents index use

Jira: HM-8494

---

Correctness Bugs

OR Precedence Bug — Wrong District Data in Reports

rptfnGetStudentsByDistrictAndTime (lines 24-31): AND/OR without proper parentheses means the 2nd and 3rd OR branches bypass District, Campus deletion, and Organization checks. This may be silently returning students from wrong districts or deleted records.

Data correctness issue

Reports using this function may include students from wrong districts. Fixing this will change report output — compare before/after carefully.

Jira: HM-8496

vwCurrentSchoolYearDate — Non-Deterministic

TOP 1 without ORDER BY returns a random row. CONVERT(varchar(10), getDate(), 1) in WHERE is non-SARGable.

Jira: HM-8497

Typo: ‘DistrictScoolYear’

Missing ‘h’ in parameter name. If someone corrects the spelling in the ReportParameter table, the query silently returns NULL.

---

Impact Visualization

graph TD
    A[rptGetSplittedResult<br/>msTVF - WHILE loop] -->|called by| B[50+ stored procedures]
    A -->|1-row estimate| C[Bad execution plans]
    C --> D[No parallelism]
    C --> E[Wrong join strategies]

    F[rptfn_Split<br/>duplicate msTVF] -->|called by| G[rptfnSelCampusList]
    F -->|called by| H[rptfnSelServiceList]
    G -->|nested opacity| I[Optimizer blind<br/>to campus filtering]
    H -->|nested opacity| J[Optimizer blind<br/>to service filtering]

    K[No READ UNCOMMITTED] --> L[Shared locks on reads]
    L --> M[Contention with<br/>OLTP writes]
    M --> N[Application slowdown<br/>during report execution]

    style A fill:#ff6b6b,color:#fff
    style F fill:#ff6b6b,color:#fff
    style K fill:#ffa94d,color:#fff

---

Fix Priority

Priority	Ticket	Fix	Risk
1	HM-8490	Replace rptGetSplittedResult + rptfn_Split with inline TVFs	Low
2	HM-8491	Add READ UNCOMMITTED to all 3 report SPs	Low
3	HM-8492	Consolidate parameter extraction (4 queries → 1)	Low
4	HM-8493	Deduplicate rptActivityTracking IF branches, pre-compute Medicaid	Medium
5	HM-8494	Add temp table indexes, replace SELECT *	Low
6	HM-8495	Inline rptfnSelCampusList/rptfnSelServiceList	Medium
7	HM-8496	Fix OR precedence bug (correctness)	Medium
8	HM-8497	Fix vwCurrentSchoolYearDate TOP 1 / SARGability	Low

Parent story: HM-8489 under epic HM-8342

Related: HM-8336 (report slowness investigation), HM-8341 (Azure Read Replica)

---

Lessons Learned

1. Multi-statement TVFs are performance poison in SQL Server. The optimizer cannot inline them, always estimates 1 row, and disables parallelism. Use inline TVFs (RETURNS TABLE AS RETURN (...)) wherever possible.

2. STRING_SPLIT() has been available since SQL Server 2016. There’s no reason to use WHILE-loop string splitters in modern SQL Server. A drop-in inline TVF replacement unlocks massive performance gains across 50+ callers.

3. Reports without READ UNCOMMITTED steal locks from the application. Read-only reporting queries should never compete with OLTP writes for locks. This is standard practice for SSRS/BI workloads.

4. SQL AND/OR precedence is a common correctness trap. Always use explicit parentheses when mixing AND and OR in WHERE clauses. The bug in rptfnGetStudentsByDistrictAndTime may have been returning wrong-district data for an unknown period.