How can I make my report run faster?
Is it just by using Indexes?
Thanks.

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,

I just edited this with details on how to set up WF SQL traces.

Sorry but I don't quite understand the point of the tracing.

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

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

ET

Oh ok now I understand why you're doing that.
However, where do you use that code?

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.

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?

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.

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.

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

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.

So I can't do much about it I guess.

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.

I fixed all that too but it still runs slow.

When you did the hold did you make it FORMAT FOCUS INDEX field1 field2 ?

No, I did FORMAT FOCUS INDEX field1 only.

Did you turn SQL tracing on and were there any messages?

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.

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.

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.

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.

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.

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.

Thanks.

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.

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

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.

All this discussion, and no mention of what database you are using?

did i miss it or something.