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.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

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  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Performance of FROM .. TO in 7706

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Performance of FROM .. TO in 7706
 Login/Join
 
Virtuoso
posted
Here's another of those WTF moments after upgrading from 7704M to 7706.

We had a COMPUTE like this in one of our reports, and the report would get completely stuck on that TABLE request. Stuck as in both CPU's on the reporting server pegged at 100% for minutes until we killed the agent.
COMPUTE IS_INDEXED/A1 = IF LST.COLNO FROM 1 TO LST.LKEYCOLUMNS THEN '+' ELSE '';


This report works fine on our production WebFOCUS server which is still at 7704M. No hangs whatsoever. It used to work fine on our Dev server before we upgraded that too.

Changing the above COMPUTE to the below, which to our understanding should be completely equivalent, fixes the issue and returns the report in several seconds:
COMPUTE IS_INDEXED/A1 = IF LST.COLNO GE 1 AND LST.COLNO LE LST.LKEYCOLUMNS THEN '+' ELSE '';


Is there some rational explanation for the (huge) difference in performance?


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Virtuoso
posted Hide Post
Is this code tightening? As documented, record selection (IF/WHERE) can use FROM...TO, but logical expressions within DEFINE/COMPUTE/RECAP do not have this operator.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Virtuoso
posted Hide Post
It would be code tightening if the report had thrown an error, but it didn't.

But... expressions are treated differently between IF/WHERE and DEFINE/COMPUTE/RECAP? That makes no sense!
Not in the least because that would mean there are at least two separate implementations, with twice the opportunity to introduce bugs, twice the work needed to maintain them, twice the time to compile, etc. In short, a maintenance nightmare.

I hope you're just pulling my leg?

This message has been edited. Last edited by: Wep5622,


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Virtuoso
posted Hide Post
In as much as an error was not produced, but the report did not work, then I agree that this is not really acceptable.

However, there is no leg pull. The operators for DEFINE/COMPUTE/RECAP are different from WHERE/IF, and there are also subtle differences between WHERE and IF.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Expert
posted Hide Post
ah. interesting. thanks for the heads up, wep. i' think i'll do a textpad search on FROM before i move my code over from 768 to 7706.
As alan says, there's a whole world of difference b/w IF and WHERE. IF is real simple, and WHERE brings in a boatload of sql.
eg whenever you have a comparison between a field and a value, use IF.
WHERE CAR EQ 'JAGUAR' is gonna take a lot more time than
IF CAR EQ 'JAGUAR'




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
Virtuoso
posted Hide Post
I only find FROM ... TO syntax listed under Selection (IF/WHERE).

Code tightening or not, if there's a bug in the implementation of undocumented syntax, you're on your own.
I suggest you tighten *your* code, by replacing
"IF a FROM b TO c"
with the equivalent
"IF a GE b AND a LE c"


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Virtuoso
posted Hide Post
Hey Susannah -

The subject on the floor is "IF ..." within a COMPUTE, which has all the "complexity" of "WHERE ...".

It may be instructive to compare the SQL generated under the two releases -- but, since it's a COMPUTE rather than a DEFINE, and involves LST., the column is unlikely to be reflected directly in the generated SQL. No?
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Virtuoso
posted Hide Post
@Susannah: I am aware of the differences between IF and WHERE clauses. That's not where the problem is.

The problem is between WHERE and COMPUTE. Our problematic COMPUTE contains a conditional IF expression with FROM ... TO ... to compare a field's value to a range of (field-)values. See my initial post above for an example.

According to Alan that may not be supported, but it worked fine in previous versions (most notably 7704M).

Regardless, the fact that it hangs the agent is not acceptable behaviour. I don't think there's any disagreement there Wink

I assume that FROM ... TO ... in a WHERE clause still works as expected, but I haven't tested that situation.

Considering the amount of fairly serious issues we're having since upgrading to 7706, I wouldn't advise anyone to upgrade to that particular version.
Issues we're seeing include: Agent hangs (the above), agent crashes with expressions on long fields, fuzziness about the meaning of '' versus MISSING (apparently IBI decided that those are now equivalent) and NOPRINT causing errors with SUMMARIZE fields.

See my other topic for details: http://forums.informationbuild...1057331/m/1147058376

If you upgrade, my advise is to aim for 7704M (which does contain some bugs, but not quite as many in our experience) or go straight to 8 (which admittedly we didn't test our code against yet, so there may be a few similar surprises there).

Unfortunately, IBI Tech support kept telling us that cases we had with them(*1) were resolved in 7705 and then 7706, so we figured upgrading was a good idea... Bad call!

*1: We're still quite adamant in opening cases when we encounter issues, as it serves to improve the product, which in turn is in our (and hopefully yours too) benefit on the longer term.


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Expert
posted Hide Post
This is nothing compared to the troubles you'll have when upgrading to WF 8.x, so, smile...


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
....we're gonna do that next week Big Grin

( 7702 -> 8.x )


_____________________
WF: 8.0.0.9 > going 8.2.0.5
 
Posts: 668 | Location: Veghel, The Netherlands | Registered: February 16, 2010Report This Post
Virtuoso
posted Hide Post
quote:
Originally posted by j.gross:
It may be instructive to compare the SQL generated under the two releases -- but, since it's a COMPUTE rather than a DEFINE, and involves LST., the column is unlikely to be reflected directly in the generated SQL. No?


These are FOCUS files, so no SQL will be generated, unfortunately (that would have been insightful indeed).


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Gold member
posted Hide Post
Wep, your example should work in 7706 if it worked in previous releases, and you should definitely call it into IBI. I would suggest also that you try to test 7706M (the latest cumulative release) as there are many bug fixes since the introduction of 7706.


IBI Development
 
Posts: 61 | Registered: November 15, 2005Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Performance of FROM .. TO in 7706

Copyright © 1996-2020 Information Builders