Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED]How to imporve reports performance
Go
New
Search
Notify
Tools
Reply
  
[CLOSED]How to imporve reports performance
 Login/Join
 
Member
posted
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
 
Posts: 26 | Location: Hefei,China | Registered: March 27, 2016Reply With QuoteReport This Post
Member
posted Hide Post
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
 
Posts: 26 | Location: Hefei,China | Registered: March 27, 2016Reply With QuoteReport This Post
Guru
posted Hide Post
Have you thought about giving them the Run Deferred option ?


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
 
Posts: 398 | Registered: February 04, 2008Reply With QuoteReport This Post
Member
posted Hide Post
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
 
Posts: 26 | Location: Hefei,China | Registered: March 27, 2016Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Run with a SQL statement trace, and check the generated code (and any warning messages WF throws).
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Virtuoso
posted Hide Post
...and in many situations, the nature of the generated SQL will vary depending on user-selected options and parameters.
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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
 
Posts: 144 | Registered: November 09, 2015Reply With QuoteReport This Post
Member
posted Hide Post
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
 
Posts: 26 | Location: Hefei,China | Registered: March 27, 2016Reply With QuoteReport This Post
Expert
posted Hide Post
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.06 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5616 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport This Post
Master
posted Hide Post
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
 
Posts: 578 | Registered: October 01, 2014Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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
 
Posts: 144 | Registered: November 09, 2015Reply With QuoteReport This Post
Guru
posted Hide Post
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
 
Posts: 495 | Registered: January 04, 2008Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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)
 
Posts: 241 | Location: Melbourne, Australia | Registered: February 07, 2007Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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 8203
WindowsServer 2012 R2,
Excel, PDF, HTML, ActiveReports
 
Posts: 124 | Location: Minnesota | Registered: August 26, 2013Reply With QuoteReport This Post
Guru
posted Hide Post
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
 
Posts: 272 | Location: Brazil | Registered: October 31, 2006Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED]How to imporve reports performance

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.