Gen EDGE

Main

Can anyone please provide your view or any suggestion on this query? 

The short answer is No. You need the 3 attributes in the view for the qualifier so they will also be in the results set.

The longer answer is Yes it is technically possible.  The question is whether it is worthwhile.

CA Gen uses the SOME and THAT feature to remove columns from the SELECT list of a READ/READ EACH as indicated in the example below based on the CA Gen SAMPLE model.  Here the requirement is to read the id and name of all full time employees with no telephone number.

The CA Gen code

      *   +->   NOT_ALL_COLUMNS_IN_SELECT         02/14/2017  07:19
      *   !       ENTITY ACTIONS:
      *   !         Entity View sat employee
      *   !           number
      *   !           name
      *   !           home_phone
      *   !           work_phone
      *   !           full_time_status
      *   !         Entity View employee
      *   !           number
      *   !           name
      *   !
      *   !     PROCEDURE STATEMENTS
     1*   !  
     2*   !  +=>READ EACH (No Distinct) employee
     2*   !  !        WHERE DESIRED employee number = SOME sat
     2*   !  !              employee number 
     2*   !  !              AND  THAT sat employee home_phone = SPACES
     2*   !  !              AND  THAT sat employee work_phone = SPACES
     2*   !  !              AND  THAT sat employee full_time_status =
     2*   !  !              "F"
     2*   !  +--
      *   +---

The resulting SQL

           SELECT
                   EMPLOYEE01."NUMBER",
                   EMPLOYEE01."NAME0"
           FROM
               "EMPLOYEE"                      EMPLOYEE01,
               "EMPLOYEE"                      EMPLOYEE02
           WHERE
           (
                EMPLOYEE01."NUMBER" = EMPLOYEE02."NUMBER" AND
                    EMPLOYEE02."HOME_PHONE" = ' ' AND
                    EMPLOYEE02."WORK_PHONE" = ' ' AND
                    EMPLOYEE02."FULL_TIME_STATUS" = 'F'
           )

Thanks for info...Geoff...

If the requirement is to improve performance I doubt the join created by having the additional table in the select will make it faster compared with including the 3 additional attributes in the READ.

When we compared CA Gen created SQL query and Modified SQL query after removing columns which are already used in Where clause in SPUFI, we could see improvement in CPU utilization (approx 0.0003). Our application is 80% based on CA Gen so was thinking could this small improvement leads us to significant improvement in CPU.

The comparison on CPU saving would be between removing the columns but introducing an additional join vs. the original READ since you will not be able to just remove the columns in Gen. I am not sure SPUFI is that reliable as a guide to the performance of the application which will use a bound plan rather than dynamic SQL.

If you are looking for quick wins for improving application performance there are almost certainly other areas that could provide significant savings, for example use of multi-row fetch and optimised view matching for repeated USE statements. Take a look at the postings on the IET Blog for more ideas.