 |
|||||
|
As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Join the TIBCO Community
Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community. | |||||
Focal Point 
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [CLOSED]How to imporve reports performance
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [CLOSED]How to imporve reports performance Read-Only TopicGo | Search | Notify | Tools |
| [CLOSED]How to imporve reports performance | Login/Join |
| Member |
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 | ||
|
| Member |
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 | |||
|
| Guru |
Have you thought about giving them the Run Deferred option ? WF 7.6.11 Oracle WebSphere Windows NT-5.2 x86 32bit | |||
|
| Member |
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...)
WebFOCUS 8105 Windows, All Outputs | |||
|
| Virtuoso |
Run with a SQL statement trace, and check the generated code (and any warning messages WF throws). | |||
|
| Expert |
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 | |||
|
| Virtuoso |
...and in many situations, the nature of the generated SQL will vary depending on user-selected options and parameters. | |||
|
| Platinum Member |
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 | |||
|
| Member |
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.  hope we can get a workaround to solve the issue. Thanks all!WebFOCUS 8105 Windows, All Outputs | |||
|
| Expert |
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 Good luck, T
| |||||||||
|
| Master |
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 | |||
|
| Platinum Member |
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 | |||
|
| Guru |
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 | |||
|
| Guru |
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) | |||
|
| Platinum Member |
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 This works well for us. webFOCUS 8207.15 WindowsServer 2019 | |||
|
| Guru |
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 | |||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only TopicFocal Point Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [CLOSED]How to imporve reports performance
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [CLOSED]How to imporve reports performanceCopyright © 1996-2020 Information Builders
| View $GS_USERNAME's Public Profile |
| Add $GS_USERNAME to my Ignore List |
| View Recent Posts by $GS_USERNAME |
| Notify me of New Posts by $GS_USERNAME |