Focal Point Banner


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. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name.


Connect to myibi
Page 1 2 

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Run Faster
 Login/Join
 
<GGOFAnalyst>
posted
How can I make my report run faster?
Is it just by using Indexes?
Thanks.
 
Report This Post
Expert
posted Hide Post
I ALWAYS use SQL traces:

-*-- Set up SQL tracing ----------------------------------------------
-*-- Deactivate SQL tracing --------------------------------
SET TRACEOFF = ALL
-*-- Enable Trace for the SQL Translator -------------------
SET TRACEON = SQLTRANS
-*-- Show SQL statements -----------------------------------
SET TRACEON = STMTRACE//CLIENT
-*-- Show Optimization information -------------------------
SET TRACEON = SQLAGGR//CLIENT
-*-- Disable the trace stamp (Date/Time etc) ---------------
SET TRACESTAMP = OFF
-*-- Set trace line wrapping - # of characters -------------
SET TRACEWRAP = 78
-*-- Activate SQL tracing ----------------------------------
SET TRACEUSER = ON
-RUN

-*-- Turn off data retrieval (for testing purposes) ------------------
SET XRETRIEVAL=OFF
-RUN

It's very important to get your WebFOCUS request to generate efficient SQL. Yes, using indexes are important. Most DEFINE statements will not translate to SQL, so it's better to do them after the data retrieval step has created a HOLD file. If these type of tweaks do not help in the speed of the request, perhaps the report is going after too much data.

This message has been edited. Last edited by: Francis Mariani,


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, 2005Report This Post
Expert
posted Hide Post
I just edited this with details on how to set up WF SQL traces.


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, 2005Report This Post
<GGOFAnalyst>
posted
Sorry but I don't quite understand the point of the tracing.
 
Report This Post
Expert
posted Hide Post
If you write bad SQL, you could have a request that can take three days to retrieve the data.

If you write good SQL, you could summarize a million data rows into three report rows in 5 seconds.

The WebFOCUS SQL Trace commands help you to write WebFOCUS code that produces good, efficient SQL code.

When I write a new WebFOCUS report, I ALWAYS turn the SQL traces on (in fact I always have them turned on for my User ID).

Turning traces on provides RDBMS reporting messages - indexes not respected, DEFINE statements that cannot be translated to SQL, etc. which would generate inefficient SQL.

I have found that fine tuning the WebFOCUS code to generate efficient SQL is a black art, it can be a little difficult. Information Builders has courses on Reporting Efficiencies for RDBMS databases.

I also turn XRETRIEVAL OFF so that if I did write bad code, I don't have to wait three days for the response to come back - XRETRIEVAL controls whether data is retrieved or not. OFF means do not retrieve any data but run through the code to check for errors and, if tracing is turned on, show the SQL generated.


Have I given too much of the game away?


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, 2005Report This Post
Platinum Member
posted Hide Post
Francis

You are commended for all the patience you exhibit in your posts.

ET


FOCUS 7.6 MVS PDF,HTML,EXCEL
 
Posts: 115 | Location: Chicago, IL | Registered: May 28, 2004Report This Post
<GGOFAnalyst>
posted
Oh ok now I understand why you're doing that.
However, where do you use that code?
 
Report This Post
Expert
posted Hide Post
I stick it at the beginning of the main part of a report that accesses RDBMS tables. There may be some preliminary stuff at the top of a program that you probably don't want to trace, e.g. User ID validation, access rights checking, etc. Just before the main TABLE request, add an INCLUDE that contains the SQL TRACE commands. Then in the actual report, stick the XRETRIEVAL command that you can turn OFF and ON as you develop the report.


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, 2005Report This Post
<GGOFAnalyst>
posted
I used it and it's a good thing to know about.
However, does the message AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
AGGREGATION IS NOT APPLICABLE TO THE VERB USED is something that I need to fix or worry about? Is that the type of messages you're talking about?
 
Report This Post
Expert
posted Hide Post
If that's the only message you get, that's OK. It is a "comment" type message because you're using PRINT and not SUM, so there's no aggregation to be done.


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, 2005Report This Post
Master
posted Hide Post
Also be aware of your "WHERE" statements. Avoid wheres based on a defined field. You may be bringing back all of the rows in your tables before limiting them to the rows you need.


Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
 
Posts: 755 | Location: TX | Registered: September 25, 2007Report This Post
<GGOFAnalyst>
posted
Francis, would this be a problem?

FOCUS-MANAGED JOIN SELECTED FOR FOLLOWING REASON(S):
(FOC2519) THE JOINED SEGMENTS RESIDE IN MORE THAN ONE NODE OR SUBSYSTEM
(FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
(FOC2592) RDBMS-MANAGED JOIN HAS BEEN DISABLED
 
Report This Post
Expert
posted Hide Post
That's probably the biggest no-no!

I know your environment - you're joining from the EI db to the CRM db or vice-versa - not a good idea. We solved that problem by copying some of the data from CRM to EI, but perhaps not the data you're after.


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, 2005Report This Post
<GGOFAnalyst>
posted
So I can't do much about it I guess.
 
Report This Post
Virtuoso
posted Hide Post
quote:
So I can't do much about it I guess.


Well, do you do selection against each of the files in your current joins? In our student system world, I find it sometimes faster to pull only the data fields I need from each DB2 table create hold files and then join the hold files. As noted if part of your primary selection is on a key field in the file that will also speed selection and if you can avoid it, don't do selection on a defined field especially as your major selection field.


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
<GGOFAnalyst>
posted
I fixed all that too but it still runs slow.
 
Report This Post
Master
posted Hide Post
When you did the hold did you make it FORMAT FOCUS INDEX field1 field2 ?


Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
 
Posts: 755 | Location: TX | Registered: September 25, 2007Report This Post
<GGOFAnalyst>
posted
No, I did FORMAT FOCUS INDEX field1 only.
 
Report This Post
Expert
posted Hide Post
Did you turn SQL tracing on and were there any 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, 2005Report This Post
Silver Member
posted Hide Post
I had a situation where the access files had keys=0 and that created the FOCUS-MANAGED JOIN selection. I changed the setting to keys=1 to allow the translator to pass the join along.


WebFOCUS 7.1.3 Windows Client AIX 5.2 Report Servers
 
Posts: 39 | Registered: January 26, 2004Report This Post
<GGOFAnalyst>
posted
Yea, I get a couple that says:
(FOC2510) FOCUS-MANAGED JOIN SELECTED FOR FOLLOWING REASON(S):
(FOC2519) THE JOINED SEGMENTS RESIDE IN MORE THAN ONE NODE OR SUBSYSTEM
(FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
(FOC2592) RDBMS-MANAGED JOIN HAS BEEN DISABLED

And I get one that says:
(FOC2510) FOCUS-MANAGED JOIN SELECTED FOR FOLLOWING REASON(S):
(FOC2514) NON-CONTIGUOUS SQL SUBTREE
(FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
(FOC2592) RDBMS-MANAGED JOIN HAS BEEN DISABLED

But I'm not sure how to fix it.
 
Report This Post
Expert
posted Hide Post
That's where training and experience come in.

I wouldn't go any further before solving these.

"RDBMS-MANAGED JOIN HAS BEEN DISABLED" means WebFOCUS will tell MS SQL Server to not join the tables, but retrieve ALL the data from both tables before joining in WebFOCUS - extremely inefficient.

"NON-CONTIGUOUS SQL SUBTREE" - even worse. The JOIN is incorrect.


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, 2005Report This Post
<GGOFAnalyst>
posted
quote:
"RDBMS-MANAGED JOIN HAS BEEN DISABLED" means WebFOCUS will tell MS SQL Server to not join the tables, but retrieve ALL the data from both tables before joining in WebFOCUS - extremely inefficient.


So how would i fix this problem?

I'll check on the NON-CONTIGUOUS SQL SUBTREE. See what's wrong with the join.
 
Report This Post
<GGOFAnalyst>
posted
I checked my joins and they're all correct. I can't do anything about them. I gotta join them the way they are joined.
 
Report This Post
Expert
posted Hide Post
This is a good document to read: "FOCUS for S/390 Relational Data Adapter Users Manual Version 7.2".

Even though it's not for the platform or version you're working with, it has a lot of information on optimization and also on how things work.

I searched for "Relational Data Adapter" from the Advanced Search on Technical Support link in the FocalPoint banner.


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, 2005Report This Post
<GGOFAnalyst>
posted
Thanks.
 
Report This Post
Expert
posted Hide Post
Log in to Tech Support and look for this document:

sj9707_002_bricker

It's a pretty good article on SQL Query Optimization. It's not about WebFOCUS code but does tell you about SQL efficiencies.


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, 2005Report This Post
Expert
posted Hide Post
More good stuff to read (you have to login to Tech Support first):

Information Builders Systems Journal, March-April 1996 - Relational Reporting Efficiencies: Part I

Information Builders Systems Journal, July-August 1996 - Relational Reporting Efficiencies: Part II


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, 2005Report This Post
<GGOFAnalyst>
posted
I ready these articles and I applied a few things to my report. I played around with it but it still runs slow.
Any other suggestions?
I've put my report back to the way it was because it was starting to not work the way it's suppose to with all the changes.
 
Report This Post
Master
posted Hide Post
All this discussion, and no mention of what database you are using?

did i miss it or something.


Prod: Single Windows 2008 Server running Webfocus 7.7.03 Reporting server Web server IIS6/Tomcat, AS400 DB2 database.
 
Posts: 611 | Registered: January 04, 2007Report This Post
  Powered by Social Strata Page 1 2  

Read-Only Read-Only Topic


Copyright © 1996-2020 Information Builders