Hello Ben,

I reproduced the problem, the original report takes 30 seconds to generate 38 pages.
There are pre_place scripts for expression controls in the page body.
I copied all scripts to the pre_place script of the page body and time reduced to 7 seconds.

Then I removed the script and changed these expressions to be evaluated by PostgreSQL, like
=CASE WHEN totalappts=0 THEN 0 ELSE totalissues / totalappts END

Now the time is 4 seconds.

So it is necessary to use as fewer separate scripts as possible because each script takes some time to initialize the engine.
We'll continue to check what can be improved

Thanks for your help Sergey!

Ben

Hello Ben,

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?

Hi Sergey,

1- It's all running locally on my system.
2- 11 pages are generated.
3- There are about 15-20 expressions
4- I'll try and see.

Hello Ben,

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?

Hi again Sergey,

I created a new report (duplicate) and removed the grand totals. The difference in negligible... got down to about 18 seconds or so. What could be the reason why the enormous difference? I noticed in an error message that some of the expressions I wrote seem to be wrapping an sql query around my sql. Would this be the cause for that? If so, does that mean I should be doing all my math via js?

Ben

Hi Ben,

Right with the expressions, the source query is wrapped with additional SQL, something like that
SELECT *, f1 + f2 AS expr1 FROM ( SELECT f1, f2 FROM table1 )

Without the expressions, the source query is executed as it was written.
Can you check the report without the expressions?

Maybe some of the expressions cause the slowdown.

We need to reproduce the issue, maybe it is possible to fix it.
Could you please show the source query and the expressions?

Sergey,

The query can be seen here:

SELECT b.branchname, u.wholename, SUM(COALESCE(TotalDials,0)+COALESCE(TotalNCAFDials,0)+COALESCE(TotalNCAMDials,0)+COALESCE(TotalNCPMDials,0)) ALLDials,

SUM(hrs.hours) AllHours,

SUM(t.TotalDemos) TotalDemos, 

SUM(TotalSales) TotalSales,

SUM(TotalCancels) TotalCancels,

SUM(TotalRejects) TotalRejects,

SUM(TotalSaved) TotalSaved,

SUM(TotalOneLeg) TotalOneLeg,

SUM(TotalResetToClose) TotalResetToClose,

SUM(TotalLeads) TotalLeads,

SUM(TotalApptsIn) TotalApptsIn,

SUM(TotalApptsOut) TotalApptsOut,

SUM(TotalRepTied) TotalRepTied,

SUM(TotalNoShow) TotalNoShow,

SUM(TotalAdjustment) TotalAdjustment,

SUM(TotalIssues) TotalIssues,

SUM(t.TotalTMKRate) TotalTMKRate,

SUM(t.TotalTMKMinutes) TotalTMKMinutes,

SUM(TotalDead) TotalDead,

SUM(TotalResched) TotalResched,

SUM(NetAmount) NetAmount,

SUM(TotalAppts) TotalAppts,

SUM(TotalContacts) TotalContacts,

SUM(TotalDials) TotalTranDials

FROM (

	Select branch_id, t.tmk_id, SUM(t.demos) TotalDemos, 

	SUM(sales) TotalSales,

	SUM(cancels) TotalCancels,

	SUM(reject) TotalRejects,

	SUM(saved) TotalSaved,

	SUM(oneleg) TotalOneLeg,

	SUM(broken) TotalResetToClose,

	SUM(count) TotalLeads,

	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(reptied) TotalRepTied,

	SUM(noshow) TotalNoShow,

	SUM(adjustment) TotalAdjustment,

	SUM(issues) TotalIssues,

	SUM(t.tmkrate) TotalTMKRate,

	SUM(t.tmkminute) TotalTMKMinutes,

	SUM(dead) TotalDead,

	SUM(resched) TotalResched,

	SUM(amount) NetAmount,

	SUM(appts) TotalAppts,

	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,

	sum(nc.dials_af) TotalNCAFDials,

	sum(nc.dials_pm) TotalNCPMDials

	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

Expressions appear rather simple to me:

$E(totaltmkminutes / 60)

$E(totaltmkminutes / 60 / allhours)

$E(alldials / allhours)

Now the JS has a little bit more code but I assumed it would be quite fast:

Report body is doing some math for GRAND Totals as well as group totals:

report.sum_hours += report.cursor.columnValue( 'allhours' );
report.sum_dials += report.cursor.columnValue( 'alldials' );
report.sum_cont += report.cursor.columnValue( 'totalcontacts' );
report.sum_appts += report.cursor.columnValue( 'totalappts' );
report.sum_inappts += report.cursor.columnValue( 'totalapptsin' );
report.sum_outappts += report.cursor.columnValue( 'totalapptsout');
report.sum_resets += report.cursor.columnValue( 'totalresched' );
report.sum_issues += report.cursor.columnValue( 'totalissues' );
report.sum_demos += report.cursor.columnValue( 'totaldemos' );
report.sum_sales += report.cursor.columnValue( 'totalsales' );
report.sum_cns += report.cursor.columnValue( 'totalnoshow' );
report.sum_rns += report.cursor.columnValue( 'totalreptied' );
report.sum_oneleg += report.cursor.columnValue( 'totaloneleg' );
report.sum_rtc += report.cursor.columnValue( 'totalresettoclose' );
report.sum_tmkmins += report.cursor.columnValue( 'totaltmkminutes' );
report.hours[report.cursor.columnValue('wholename')] = parseInt(report.cursor.columnValue( 'allhours' ));


report.h_sum_hours += report.cursor.columnValue( 'allhours' );
report.h_sum_dials += report.cursor.columnValue( 'alldials' );
report.h_sum_cont += report.cursor.columnValue( 'totalcontacts' );
report.h_sum_appts += report.cursor.columnValue( 'totalappts' );
report.h_sum_inappts += report.cursor.columnValue( 'totalapptsin' );
report.h_sum_outappts += report.cursor.columnValue( 'totalapptsout');
report.h_sum_resets += report.cursor.columnValue( 'totalresched' );
report.h_sum_issues += report.cursor.columnValue( 'totalissues' );
report.h_sum_demos += report.cursor.columnValue( 'totaldemos' );
report.h_sum_sales += report.cursor.columnValue( 'totalsales' );
report.h_sum_cns += report.cursor.columnValue( 'totalnoshow' );
report.h_sum_rns += report.cursor.columnValue( 'totalreptied' );
report.h_sum_oneleg += report.cursor.columnValue( 'totaloneleg' );
report.h_sum_rtc += report.cursor.columnValue( 'totalresettoclose' );
report.h_sum_tmkmins += report.cursor.columnValue( 'totaltmkminutes' );
report.h_hours[report.cursor.columnValue('wholename')] = parseInt(report.cursor.columnValue( 'allhours' ));

And several expressions are blank with something like this in the pre_place for JS:

if (report.cursor.columnValue( 'totalissues' )==0) {
report.controls.demoIssues.label = 0;
} else {
report.controls.demoIssues.label = (report.cursor.columnValue( 'totaldemos' ) / report.cursor.columnValue( 'totalissues' ));
}


Ben

Not sure if the query is the most efficient, but like I said it executes in an acceptable time frame. But the report takes quite a long time to generate.

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.

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.

Sergey,

What about the option of doing all my expression work via JS? Would that speed things up? JS doesn't cause query nesting does it?

Ben

Hi Ben,

Yes, when you use JavaScript there is no subqueries.
Also, it may be easier to include these expressions in the original query.

But we don't know yet what is the reason for slowness, so I'd start with the simplest report and add functionality step by step.

Sergey,

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.

Ben

Sergey,

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.

Ben

I checked my JS and the most complicated thing I do is a for loop and deleting those has no effect on speed. Other than that it is just math, storing a value and multiplying or dividing in the group and page footers.

We don't expect it to be so slow.
Is it possible to get a test data and the project? Maybe not original database structure, just the result of the source query as CSV would be enough to execute JavaScript and find slow places.

We don't expect it to be so slow.
Is it possible to get a test data and the project? Maybe not original database structure, just the result of the source query as CSV would be enough to execute JavaScript and find slow places.

What is the best way to send you the files?