Reports

To prevent needless changes, make use of the @SqlResultSetMapping. Instead of manually altering the results, return a list of DTOs straight from the repository.

Improved Query Technique

WITH DateRange AS (
    SELECT date 
    FROM PrecomputedDateRange 
    WHERE date BETWEEN :fromDate AND :toDate
)
SELECT
    FORMAT(d.date, 'MM/dd/yyyy') AS label,
    COALESCE(SUM(abc.data1), 0) AS data1,
    COALESCE(SUM(abc.data2), 0) AS data2,
    COALESCE(SUM(abc.data3), 0) AS data3
FROM
    DateRange d
LEFT JOIN ABC abc 
    ON abc.date = d.date -- Avoid casting
WHERE filtering1 AND filtering2
GROUP BY d.date
ORDER BY d.date ASC;

You can make better use of indexes and reduce computation by splitting date range computation from the primary query.

since I have not dealt with such large data I will hope that's helpful. and it's better to use Redis or Memecache for caching if this doesn't work.

79169714