Oracle 12c

[Ken Langendock]

Peter and Harold

 

For those out there that do not know all the ins and outs of PH and Oracle,
here are some more tips on speeding up your system.

 

 

After changing all SELECT statements to CHOOSE statements you can do the
following to QTP to speed up your system even further.

 

1)     Use OUTPUT PHASE reads for all reference files.

 

In this example the MemberFamilies file is a reference file for getting the
persons name only.

The ENTIRE MemberFamilies record is added to the end of the record complex
for nothing.

Sorting in this program creates sort work tables including every field from
all 3 files with hundreds of fields.

 

ACCESS ClaimPayments &

 LINK ClaimNo OF ClaimPayments &

   TO ClaimNo &

   OF Claims &

  LINK MemberId OF Claims, &

       GroupNo OF Claims, &

       SubgroupNo OF Claims, &

       FamilyId OF Claims &

    TO MemberId, &

       GroupNo, &

       SubgroupNo, &

       FamilyId &

    OF MemberFamilies

       OPTIONAL

 

This can be changed to not include the reference files in the access
statement:

This way the record complex does not included MemberFamilies.

Sorting in this program creates sort work tables including every field from
only 2 files with dozens/hundreds less fields.

 

ACCESS ClaimPayments &

 LINK ClaimNo OF ClaimPayments &

   TO ClaimNo &

   OF Claims

 

OUTPUT MemberFamilies &

       ADD &

       UPDATE &

       NOITEMS &

       IF 1 EQ 2 &

       VIA   MemberId, &

             GroupNo, &

             SubgroupNo, &

             FamilyId &

       USING MemberId OF Claims, &

             GroupNo OF Claims, &

             SubgroupNo OF Claims, &

             FamilyId OF Claims

 

This only reads the MemberFamilies record AFTER the record complex has been
completely extracted.

 

 

2)     Replace ACCESS statements with CURSOR statements

 

Here the database returns only the records you wanted and ONLY the fields
you need..thus reducing the traffic even more.

Sorting in this program creates sort work tables including every field from
only 1 file with 15 fields.

 

SQL &

    DECLARE Payments CURSOR FOR &

     SELECT ClaimPayments.BankId, &

            ClaimPayments.BankAccountId, &

            ClaimPayments.CheckNo, &

            ClaimPayments.ClaimCentury, &

            ClaimPayments.ClaimNo, &

            ClaimPayments.PaymentAmount, &

            Claims.MemberId, &

            Claims.GroupNo, &

            Claims.SubgroupNo, &

            Claims.FamilyId, &

            Claims.ClaimStatus, &

            MemberFamilies.LastName, &

            MemberFamilies.FirstName, &

            MemberFamilies.MiddleName, &

            MemberFamilies.NameSuffix &

       FROM ClaimPayments &

            LEFT OUTER JOIN Claims &

                         ON Claims.ClaimNo = ClaimPayments.ClaimNo &

            LEFT OUTER JOIN MemberFamilies &

                         ON MemberFamilies.MemberId = Claims.MemberId AND &

                            MemberFamilies.GroupNo = Claims.GroupNo AND &

                            MemberFamilies.SubgroupNo = Claims.SubgroupNo
AND &

                            MemberFamilies.FamilyId = Claims.FamilyId &

      WHERE ClaimPayments.CIPFlag = 'N' AND &

            ClaimPayments.BankId = :T-BankId AND &

            ClaimPayments.BankAccountId = :T-BankAccountId AND &

            ClaimPayments.BulkCheck = 'Y'

 

CURSOR Payments PRIMARY &

       ALIAS DisplayClaimPayments &

       OCCURS 13 &

       KEY CheckNo

  ACCESS VIA CheckNo &

       USING T-CheckNo

 

3)     Add VIEWS to the database.

 

Here the database returns only the records you wanted and ONLY the fields
you need keeping your syntax simple and clean

This view can be used by more than one program also, keeping all your source
code even more simple.

Sorting in this program creates sort work tables including every field from
only 1 file with 15 fields.

 

CREATE OR REPLACE VIEW vClaimPayments

  AS SELECT ClaimPayments.BankId, 

            ClaimPayments.BankAccountId, 

            ClaimPayments.CheckNo, 

            ClaimPayments.ClaimCentury, 

            ClaimPayments.ClaimNo, 

            ClaimPayments.PaymentAmount, 

            Claims.MemberId, 

            Claims.GroupNo, 

            Claims.SubgroupNo, 

            Claims.FamilyId, 

            Claims.ClaimStatus, 

            MemberFamilies.LastName, 

            MemberFamilies.FirstName, 

            MemberFamilies.MiddleName, 

            MemberFamilies.NameSuffix 

       FROM ClaimPayments

            LEFT OUTER JOIN Claims

                         ON Claims.ClaimCentury = ClaimPayments.ClaimCentury
AND

                            Claims.ClaimNo = ClaimPayments.ClaimNo

            LEFT OUTER JOIN MemberFamilies

                         ON MemberFamilies.MemberId = Claims.MemebrId AND

                            MemberFamilies.GroupNo = Claims.GroupNo AND

                            MemberFamilies.SubgroupNo = Claims.SubgroupNo
AND

                            MemberFamilies.FamilyId = Claims.FamilyId

/

 

ACCESS vClaimPayments

 

  CHOOSE CheckNo (T-CheckNo)

 

 

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.sowder.com/pipermail/powerh-l/attachments/20161116/f0757875/attachment-0001.htm>