1. Ben Scofield
  2. Report Editor
  3. Thursday, June 21 2018, 04:05 PM
  4.  Subscribe via email
Hi- I have a query that is somewhat complex. It's running on a postgres db. When I run the query in pgAdmin it shows that the results are returned in less than 50msecs. When I have the query editor open in Valentina Studio, the results are displayed almost instantly in the Test tab. Why does it take over 20 seconds to see the report in the report editor? I have a group header, group footer and a page body as well as some js scripts to do some grand total math in the report footer.
Comment
There are no comments made yet.
Sergey Pashkov Accepted Answer
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?
Comment
There are no comments made yet.
  1. more than a month ago
  2. Report Editor
  3. # 1
Ben Scofield Accepted Answer
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.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Report Editor
  3. # 2
Ben Scofield Accepted Answer
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
Comment
There are no comments made yet.
  1. more than a month ago
  2. Report Editor
  3. # 3
Sergey Pashkov Accepted Answer
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?
Comment
There are no comments made yet.
  1. more than a month ago
  2. Report Editor
  3. # 4
Ben Scofield Accepted Answer
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
Comment
There are no comments made yet.
  1. more than a month ago
  2. Report Editor
  3. # 5
Ben Scofield Accepted Answer
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.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Report Editor
  3. # 6
Sergey Pashkov Accepted Answer
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.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Report Editor
  3. # 7
Ben Scofield Accepted Answer
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
Comment
There are no comments made yet.
  1. more than a month ago
  2. Report Editor
  3. # 8
Sergey Pashkov Accepted Answer
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.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Report Editor
  3. # 9
Ben Scofield Accepted Answer
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
Comment
There are no comments made yet.
  1. more than a month ago
  2. Report Editor
  3. # 10
Ben Scofield Accepted Answer
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.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Report Editor
  3. # 11
Sergey Pashkov Accepted Answer
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.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Report Editor
  3. # 12
Ben Scofield Accepted Answer
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?
Comment
There are no comments made yet.
  1. more than a month ago
  2. Report Editor
  3. # 13
Sergey Pashkov Accepted Answer
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
Comment
There are no comments made yet.
  1. more than a month ago
  2. Report Editor
  3. # 14
Ben Scofield Accepted Answer
Thanks for your help Sergey!

Ben
Comment
There are no comments made yet.
  1. more than a month ago
  2. Report Editor
  3. # 15
  • Page :
  • 1


There are no replies made for this post yet.
However, you are not allowed to reply to this post.

Categories

Announcements & News
  1. 0 subcategories
Valentina Studio
  1. 2 subcategories
Valentina Server
  1. 4 subcategories
Valentina Database ADK
  1. 0 subcategories
Valentina Reports ADK
  1. 0 subcategories
Other Discussions
  1. 2 subcategories
BETA Testing
  1. 0 subcategories
Education & Research
  1. 0 subcategories