How I Cut a 35‑Second SQL Query to Under 2 Seconds with Indexes and CTE Refactoring

While maintaining a company project, the author discovered that loading a page with about 4,000 rows required 35 seconds, and projected that 40,000 rows would be unusable for end users. The goal was to optimise the query and bring the response time down to a few seconds.

The original query selected many columns from Pub_AidBasicInformation, Pub_Application, Pub_User, Pub_Consult1 and several derived tables, joining them with multiple INNER JOIN and LEFT JOIN clauses and invoking scalar functions dbo.f_GetClinical and dbo.f_GetAidNamebyConsult1. The full statement is reproduced below:

SELECT Pub_AidBasicInformation.UserName,
       Pub_AidBasicInformation.District,
       Pub_AidBasicInformation.Street,
       Pub_AidBasicInformation.Community,
       Pub_AidBasicInformation.DisCard,
       Pub_Application.CreateOn AS AppCreateOn,
       Pub_User.UserName AS DepartmentUserName,
       Pub_Consult1.ConsultId,
       Pub_Consult1.CaseId,
       Clinicaltb.Clinical,
       cte3.AidName,
       Pub_Application.IsUseTraining,
       Pub_Application.ApplicationId,
       tab.num
FROM   Pub_Consult1
INNER JOIN Pub_Application ON Pub_Consult1.ApplicationId = Pub_Application.ApplicationId
INNER JOIN Pub_AidBasicInformation ON Pub_Application.AidBasicInfoId = Pub_AidBasicInformation.AidBasicInfoId
INNER JOIN (
    SELECT ConsultId, dbo.f_GetClinical(ConsultId) AS Clinical
    FROM   Pub_Consult1
) Clinicaltb ON Clinicaltb.ConsultId = Pub_Consult1.ConsultId
INNER JOIN (
    SELECT DISTINCT ApplicationId, SUM(TraniningNumber) AS num
    FROM   dbo.Review_Aid_UseTraining_Record
    WHERE  AidReferralId IS NULL
    GROUP BY ApplicationId
) tab ON tab.ApplicationId = Pub_Consult1.ApplicationId
LEFT JOIN (
    SELECT ConsultId, dbo.f_GetAidNamebyConsult1(ConsultId) AS AidName
    FROM   Pub_Consult1
) AidNametb ON AidNametb.ConsultId = Pub_Consult1.ConsultId
LEFT OUTER JOIN Pub_User ON Pub_Application.ReviewUserId = Pub_User.UserId
WHERE  Pub_Consult1.Directory = 0
ORDER BY Pub_Application.CreateOn DESC;

Running the query showed a plan dominated by clustered index scans and high I/O costs, indicating that the optimizer was scanning large tables row‑by‑row. The Database Engine Tuning Advisor suggested creating several non‑clustered indexes, for example:

CREATE NONCLUSTERED INDEX idx_Pub_Application
ON dbo.Pub_Application (AidBasicInfoId ASC, ApplicationId ASC, ReviewUserId ASC, CreateOn ASC);

CREATE NONCLUSTERED INDEX idx_Pub_Consult1
ON dbo.Pub_Consult1 (ConsultId ASC);

CREATE NONCLUSTERED INDEX idx_Review_Aid_UseTraining_Record
ON dbo.Review_Aid_UseTraining_Record (AidReferralId ASC, ApplicationId ASC);

After applying these indexes the execution time only improved to about 30 seconds, revealing that the indexes alone were insufficient. Further investigation of the execution plan highlighted that the scalar functions f_GetClinical and f_GetAidNamebyConsult1 were the real bottlenecks, each being executed for every row.

To eliminate the expensive functions, the query was rewritten using common table expressions (CTEs) and the FOR XML PATH('') technique to perform string aggregation directly in SQL. The new approach first builds a CTE that joins the necessary tables, then aggregates the AidName values without invoking any user‑defined function. A simplified version of the rewritten query is:

WITH cte1 AS (
    SELECT A.AdapterAssessmentId,
           CASE WHEN B.AidName IS NULL THEN A.AidName ELSE B.AidName END AS AidName
    FROM   Report_AdapterAssessment_Aid AS A
    LEFT JOIN Pub_ProductDir AS B ON A.ProductDirAId = B.ProductDirAId
),
cte2 AS (
    SELECT DISTINCT ApplicationId, SUM(TraniningNumber) AS num
    FROM   dbo.Review_Aid_UseTraining_Record
    WHERE  AidReferralId IS NULL
    GROUP BY ApplicationId
)
SELECT DISTINCT Pub_AidBasicInformation.AidBasicInfoId,
                Pub_AidBasicInformation.UserName,
                Pub_AidBasicInformation.District,
                Pub_AidBasicInformation.Street,
                Pub_AidBasicInformation.Community,
                Pub_AidBasicInformation.DisCard,
                Pub_Application.CreateOn AS AppCreateOn,
                Pub_User.UserName AS DepartmentUserName,
                Pub_Consult1.ConsultId,
                Pub_Consult1.CaseId,
                Clinicaltb.Clinical,
                cte1.AidName,
                Pub_Application.IsUseTraining,
                Pub_Application.ApplicationId,
                cte2.num
FROM   Pub_Consult1
INNER JOIN Pub_Application ON Pub_Consult1.ApplicationId = Pub_Application.ApplicationId
INNER JOIN Pub_AidBasicInformation ON Pub_Application.AidBasicInfoId = Pub_AidBasicInformation.AidBasicInfoId
INNER JOIN (
    SELECT ConsultId, dbo.f_GetClinical(ConsultId) AS Clinical
    FROM   Pub_Consult1
) Clinicaltb ON Clinicaltb.ConsultId = Pub_Consult1.ConsultId
LEFT JOIN cte1 ON cte1.AdapterAssessmentId = Pub_Consult1.AdapterAssessmentId
LEFT JOIN cte2 ON cte2.ApplicationId = Pub_Consult1.ApplicationId
LEFT OUTER JOIN Pub_User ON Pub_Application.ReviewUserId = Pub_User.UserId
WHERE  Pub_Consult1.Directory = 0
ORDER BY Pub_Application.CreateOn DESC;

After removing the functions and adding a final set of indexes that covered the new join columns (e.g., an index on

Pub_Application(AidBasicInfoId, ApplicationId, ReviewUserId, CreateOn)

), the query execution time dropped to 3.5 seconds, roughly a ten‑fold improvement. Adding the index on Pub_Application and fine‑tuning the CTEs further reduced the time to 1.6 seconds, and with the original index set the total runtime was about 1.6 seconds.

Key take‑aways from the optimisation process are:

Identify and add appropriate non‑clustered indexes, especially on foreign‑key, join, filter and sort columns; include covering columns when necessary.

Avoid scalar user‑defined functions in large result sets; replace them with set‑based logic using CTEs, sub‑queries or FOR XML PATH for string concatenation.

Use the execution plan and Database Engine Tuning Advisor to pinpoint high‑cost operators such as clustered index scans.

Consider table partitioning for very large datasets and, if needed, upgrade hardware to reduce I/O latency.

These steps collectively transformed a painfully slow report into a responsive query suitable for production use.