Focal Point
Run Faster

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

October 11, 2007, 12:36 PM
<GGOFAnalyst>
Run Faster
How can I make my report run faster?
Is it just by using Indexes?
Thanks.
October 11, 2007, 12:46 PM
Francis Mariani
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
October 11, 2007, 01:54 PM
Francis Mariani
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
October 11, 2007, 02:12 PM
<GGOFAnalyst>
Sorry but I don't quite understand the point of the tracing.
October 11, 2007, 02:55 PM
Francis Mariani
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
October 11, 2007, 03:26 PM
ET
Francis

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

ET


FOCUS 7.6 MVS PDF,HTML,EXCEL
October 11, 2007, 03:37 PM
<GGOFAnalyst>
Oh ok now I understand why you're doing that.
However, where do you use that code?
October 11, 2007, 03:47 PM
Francis Mariani
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
October 11, 2007, 04:06 PM
<GGOFAnalyst>
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?
October 11, 2007, 04:45 PM
Francis Mariani
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
October 12, 2007, 08:58 AM
PBrightwell
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
October 12, 2007, 09:12 AM
<GGOFAnalyst>
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
October 12, 2007, 09:31 AM
Francis Mariani
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
October 12, 2007, 09:43 AM
<GGOFAnalyst>
So I can't do much about it I guess.
October 12, 2007, 03:03 PM
Leah
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
October 15, 2007, 08:49 AM
<GGOFAnalyst>
I fixed all that too but it still runs slow.
October 15, 2007, 09:06 AM
PBrightwell
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
October 15, 2007, 09:11 AM
<GGOFAnalyst>
No, I did FORMAT FOCUS INDEX field1 only.
October 15, 2007, 10:38 AM
Francis Mariani
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
October 15, 2007, 10:46 AM
jmdoyl1
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
October 15, 2007, 10:51 AM
<GGOFAnalyst>
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.
October 15, 2007, 11:11 AM
Francis Mariani
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
October 15, 2007, 11:35 AM
<GGOFAnalyst>
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.
October 15, 2007, 01:55 PM
<GGOFAnalyst>
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.
October 15, 2007, 02:00 PM
Francis Mariani
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
October 15, 2007, 02:31 PM
<GGOFAnalyst>
Thanks.
October 15, 2007, 03:59 PM
Francis Mariani
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
October 15, 2007, 04:40 PM
Francis Mariani
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
October 18, 2007, 12:11 PM
<GGOFAnalyst>
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.
October 18, 2007, 12:38 PM
Jason K.
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.