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?

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.

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,

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.

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'

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"

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?

@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

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.

This is nothing compared to the troubles you'll have when upgrading to WF 8.x, so, smile...

....we're gonna do that next week

( 7702 -> 8.x )

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).

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.