1) Is the server running on the same system? Maybe SSH is used?
2) How many pages are generated?
3) Are there any expressions?
4) What if you create a new report without these grand total calculations - will it run faster?
SELECT b.branchname, u.wholename, SUM(COALESCE(TotalDials,0)+COALESCE(TotalNCAFDials,0)+COALESCE(TotalNCAMDials,0)+COALESCE(TotalNCPMDials,0)) ALLDials,
Select branch_id, t.tmk_id, SUM(t.demos) TotalDemos,
SUM(CASE WHEN t.type = 'ENL' THEN appts ELSE 0 END) TotalApptsIn,
SUM(CASE WHEN t.type <> 'ENL' THEN appts ELSE 0 END) TotalApptsOut,
SUM(COALESCE(t.cncts_pm,0) + COALESCE(t.cncts_af,0) + COALESCE(t.cncts_am,0)) TotalContacts,
SUM(COALESCE(t.dials_pm,0) + COALESCE(t.dials_af,0) + COALESCE(t.dials_am,0)) TotalDials
FROM leadtrans t LEFT JOIN tmk ON t.tmk_id = tmk.user_id
WHERE t.activitydate >= $P(startDate) and t.activitydate <= $P(endDate) and t.type <> 'CFM' and tmk.inactive = FALSE and t.branch_id <> 3 AND t.branch_id > 0 and t.branch_id IS NOT NULL AND $P(limitBranch) AND $P(limitStatus) AND $P(limitTMK) AND $P(limitTeam)
group by branch_id, tmk_id
) as t
LEFT JOIN LATERAL
SELECT sum(nc.dials_am) TotalNCAMDials,
FROM nocont nc
WHERE nc.activitydate >= $P(startDate) and nc.activitydate <= $P(endDate) and nc.tmk_id = t.tmk_id and nc.branch_id = t.branch_id AND nc.branch_id <> 3 and nc.branch_id > 0 and nc.branch_id IS NOT NULL AND $P(limitBranch)
) as nc ON TRUE
LEFT JOIN LATERAL
SELECT sum(hourstowork) hours
FROM user_timeclock tc
WHERE tc.login >= $P(startDate) and tc.login <= $P(endDate) and tc.user_id = t.tmk_id
) as hrs ON TRUE
JOIN usuarios u on t.tmk_id = u.id
JOIN branches b on t.branch_id = b.id
GROUP BY b.branchname, u.wholename
ORDER BY b.branchname, u.wholename
Thank you, well the query is complex, it will be hard to reproduce without the sample data.
Maybe you can check after each step - when does the slowdown happen?
1. Create a plain report based on this query - it should be executed nearly as fast as the original query
2. Add expressions
3. Add grouping
If the expression is the problem - it is possible to add it to the source query to avoid query nesting.
I've rebuilt (or rather duplicated and then deleted things step by step) the report, 1 thing at a time, and the slow down is definitely the JS. Is this normal for the js execution to slow things down? Maybe I need to recheck my js.
We don't expect it to be so slow.