I applied a little more pressure than usual to my brain this morning and discovered something that is now obvious but eluded me for a long time.

Consider this code, which I have used many times:

  
TABLE FILE BASEL_TIME_D
SUM MIN.TIME_DIM_KEY
BY HIGHEST 1 TIME_DIM_KEY
END

The SQL trace:

  
AGGREGATION DONE ...
SELECT T1.TIME_DIM_KEY, MIN(T1.TIME_DIM_KEY) FROM
BASEL.TIME_D T1 GROUP BY T1.TIME_DIM_KEY ORDER BY
T1.TIME_DIM_KEY DESC  FOR FETCH ONLY;

The WebFOCUS retrieval result:

  
NUMBER OF RECORDS IN TABLE=    39047  LINES=  39047
(BEFORE TOTAL TESTS)

39047 rows are retrieved by the DBMS and WebFCOUS outputs 1 row because of the BY HIGHEST 1 statement.

Now consider this code:

TABLE FILE BASEL_TIME_D
SUM MIN.TIME_DIM_KEY
BY HIGHEST TIME_DIM_KEY
WHERE READLIMIT EQ 1
END

The SQL trace:

  
AGGREGATION DONE ...
SELECT T1.TIME_DIM_KEY, MIN(T1.TIME_DIM_KEY) FROM
BASEL.TIME_D T1 GROUP BY T1.TIME_DIM_KEY ORDER BY
T1.TIME_DIM_KEY DESC  FETCH FIRST 1 ROW ONLY FOR FETCH ONLY;

The WebFOCUS retrieval result:

  
NUMBER OF RECORDS IN TABLE=        1  LINES=      1

1 row is retrieved by the DBMS and WebFCOUS outputs 1 row.

This behaviour seems so obvious now that I've compared the two techniques, I just had never considered using WHERE READLIMIT EQ 1 in a real request, I've used it for testing purposes only.

I will have to review all my programs to make them more efficient.

This would be OK when it's based on the fact that the record you want is, in fact, the first record that you read, right? So, the "MIN." is irrelevant with the use of "WHERE READLIMT EQ 1", right?

Doug, it will definitely be the first record that I want, that's why the HIGHEST is there. The interesting thing about WHERE READMLIMIT EQ 1 is that, for DBMS tables, it doesn't actually behave the way one would think it does. The DBMS will read all the data, but output only 1 row.

Francis,

I've had my morning coffee and my afternoon tea and I'm still 'just not getting it'. It seems like you want to get the highest TIME_DIM_KEY in your table? Why not just do

 
TABLE FILE BASEL_TIME_D
SUM MAX.TIME_DIM_KEY
END
 

That will generate

 
SELECT MAX(T1.TIME_DIM_KEY)
FROM BASEL.TIME_D T1 
 

EricH

EricH,

Yes, your code makes sense when you need to retrieve one row. If you need to retrieve the 5 highest rows, then might the technique I describe make more sense?

Hey Francis,

Very belated response here. I would avoid using the SUM or the MIN. Here's what I did on SQL*Server:

TABLE FILE MEASURES
   PRINT
      MEASURE_ID NOPRINT
   BY HIGHEST MEASURE_ID
   IF READLIMIT EQ 5
END
  

which generated this highly efficient SQL:

   SELECT  TOP 5 T1."MEASURE_ID" FROM MEASURES T1 ORDER BY  
   T1."MEASURE_ID" DESC ; 
  

A lot depends on the capabilities of the underlying RDBMS; the "TOP 5" syntax is relatively new - i.e., it was introduced sometime in the last decade.

EricH

EricH,

Thanks for the feedback.

TABLE FILE BASEL_TIME_D
PRINT PERIOD_END_DT
BY HIGHEST TIME_DIM_KEY
WHERE READLIMIT EQ 5

Generates

SELECT T1."TIME_DIM_KEY",T1."PERIOD_END_DT"
FROM BASEL.TIME_D T1 
ORDER BY T1."TIME_DIM_KEY" DESC  
FETCH FIRST 5 ROWS ONLY FOR FETCH ONLY;

This is WF v7.6.5, DB2 v8.1. I imagine this is as efficient as the "TOP 5" generated by the adpater for your DBMS.