Focal Point
[CLOSED]How to imporve reports performance

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/2107015286

May 25, 2016, 11:31 AM
S.G
[CLOSED]How to imporve reports performance
Hi all,

I developed a couple of reports and they run fine on Dev/Testing env. However when they are migrated to Prod env, they run very slow because of the large amount of data (over 100 million rows in the main table). I went over all the DB tables/views with DBA and had all the tuning done from DB level, also network/webserver/appserver is checked. Still the reports run slowly.

Could you please share your suggestion (such as maintain hold files for past weeks/months/quarters or create summary DB tables instead of the main table) to enhance the performance? Thanks in advance!

This message has been edited. Last edited by: <Emily McAllister>,


WebFOCUS 8105
Windows, All Outputs
May 25, 2016, 11:48 AM
S.G
Besides, all of the reports will be triggered by users click action. So I don't think scheduler and ReportCaster will help...


WebFOCUS 8105
Windows, All Outputs
May 25, 2016, 11:50 AM
RSquared
Have you thought about giving them the Run Deferred option ?


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
May 25, 2016, 12:12 PM
S.G
Report customers accept the deferred mode for some reports. But for other reports, they hope to get the result quickly (very soon right after the trigger...)

quote:
Originally posted by RSquared:
Have you thought about giving them the Run Deferred option ?



WebFOCUS 8105
Windows, All Outputs
May 25, 2016, 12:14 PM
j.gross
Run with a SQL statement trace, and check the generated code (and any warning messages WF throws).
May 25, 2016, 12:16 PM
Francis Mariani
quote:
I went over all the DB tables/views with DBA and had all the tuning done from DB level, also network/webserver/appserver is checked.


It doesn't sound like you reviewed the report code with the DBA. Did you turn on WebFOCUS SQL tracing? The traces provide you with the generated SQL and also very helpful messages.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
May 25, 2016, 12:59 PM
j.gross
...and in many situations, the nature of the generated SQL will vary depending on user-selected options and parameters.
May 25, 2016, 02:22 PM
jcannavo
If you don't know how to trace, here is an example of a way...

 
 SET TRACEOFF = ALL
 SET TRACEON = STMTRACE//CLIENT
 SET TRACEON = SQLAGGR//CLIENT
 SET TRACESTAMP = OFF
 SET TRACEUSER = CLIENT
-* Don't actually fetch the data
 SET XRETRIEVAL = OFF

TABLE FILE CAR
PRINT 
     CAR.BODY.BODYTYPE
     CAR.BODY.SEATS
ON TABLE SET PAGE-NUM NOLEAD 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END

 SET TRACEOFF = ALL



JC
WebFOCUS Dev Studio / App Studio
8.2.01
Windows 7
May 25, 2016, 02:34 PM
S.G
Thanks all for the suggestion. Actually I have already traced the SQL generated by WebFOCUS reports. However the SQL query (for example, a chart report with the whole year summary) costs too much time (over 30 mins) in DB client app. And DBA team confirmed there is nothing they can do to enhance from DB level.

So I came for your suggestion. Smiler hope we can get a workaround to solve the issue. Thanks all!


WebFOCUS 8105
Windows, All Outputs
May 26, 2016, 02:44 AM
Tony A
quote:
And DBA team confirmed there is nothing they can do to enhance from DB level

That's not actually true - they could create a view or stored procedure for you in a more efficient query from which you could build your report.

You could also engage with your local Professional Services team to build upon your knowledge by using collaborative working (i.e. have a PS Consultant working in your team for knowledge transfer etc.)

There is always something that can be done, you just have to expand your view on how to achieve something Smiler

Good luck,

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
May 26, 2016, 09:49 AM
GavinL
I'm assuming your not displaying millions of records on a report that in reality it's an aggregation of data that spans millions of rows. Correct? Then why not aggregate it every night to meet the report requirements, then just report based on the aggregated data?



- FOCUS Man, just FOCUS!
-----------------------------
Product: WebFOCUS
Version: 8.1.04
Server: Windows 2008 Server
May 26, 2016, 11:39 AM
jcannavo
I was thinking along the same lines as Gavin. If your DBA team "cannot" do any more for you on that side of things you're going to have to limit or pare down your data somehow to speed up the process.


JC
WebFOCUS Dev Studio / App Studio
8.2.01
Windows 7
May 26, 2016, 12:29 PM
MattC
How many records are you expecting to return from database? Can you write SQL instead of letting WebFOCUS create the SQL? Maybe you fine tune your SQL.


WebFOCUS 8.1.05
May 26, 2016, 09:22 PM
StuBouyer
With reports that access really large tables you are always going to have some lag.

One thing we do is to create aggregate tables - I assume you aren't returning 100 million+ rows to your report - overnight with ReportCaster or ETL and report against them rather than the "raw" tables. We only go back to the parent tables when detail is needed and then we make sure that filters are against well indexed fields.

Hope this helps

Stu


WebFOCUS 8.2.03 (8.2.06 in testing)
May 31, 2016, 12:20 PM
stur0063
quote:
Originally posted by StuBouyer:
One thing we do is to create aggregate tables


We often do this for "big" tables. We define our App Hold location. Then we write a hold file

ON TABLE HOLD AS MyDailyTempTableName FORMAT FOCUS


We then query off that hold table created daily by Report Caster.

We will also create several aggregate tables. One by company, then another by company and division, and yet another by company - division and line of business etc...

By the time they get to a small subset of your original 100M rows - you, hopefully, are passing in enough keys to make the response time not suck....which should always be a goal Smiler

This works well for us.


webFOCUS 8207.15
WindowsServer 2019
May 31, 2016, 01:31 PM
Ricardo Augusto
I would suggest you follow what stur0063 said but use MODIFY to append new data from your main table into your new aggregated FOCUS indexed databases.


WebFOCUS 8.1.05 / APP Studio