Better Performance On Work with Payroll Transaction Ledger (P050999) With An Index On F0618

Read Time:1 Min, 30 Sec

We have been getting complaints about the Work with Payroll Transaction Ledger (P050999) grid loading performance. So, I decided to take a look. What I found was that upon entering the application it does an automatic find on the grid using the following SQL statement with the current G/L date in the WHERE clause.

SELECT
YTDGL,YTMCU,YTSBLT,YTPAYM,YTPB,YTSBL,YTUAMT,YTPCUN,YTPRTR,YTDWK,YTSHRT,YTAN8,YTSHD,YTPPRT,YTPALF,YTBHRT,YTOBJ,YTGPA,YTSUB,YTCMTH,YTAUBP,
YTPHRW,YTPDBA,YTCO,YTAID,YTCRCD,YTCRDC
FROM PRODDTA.F0618
WHERE (YTDGL <= 115334 )
ORDER BY YTDWK ASC

As you can see, this SQL statement is returning every record in the F0618 that has a G/L Date less than or equal to the system’s current G/L Date. I don’t know about your installation but the one that I was working on had 17+ million records. Why the heck would it need to return all that?

That screen was taking 8+ minutes to present information to the user.

With the help of my coworker Geordy and Technet, I came up with an index on the F0618 that didn’t make the SQL statement finish any quicker, but it does present the user the first page of data extremely fast (LESS THAN 3 SECONDS!).

CREATE INDEX [F0618_CUSTOM]
ON [JDE_PRODUCTION].[PRODDTA].[F0618] ([YTDWK] ASC)
INCLUDE ([YTDGL],[YTAN8], [YTPALF], [YTPRTR], [YTCO], [YTMCU], [YTOBJ], [YTSUB], [YTSBL], [YTSBLT], [YTPHRW], [YTPCUN], [YTPPRT], [YTBHRT],
[YTSHRT], [YTSHD], [YTPAYM], [YTGPA], [YTPDBA], [YTPB], [YTUAMT], [YTAID], [YTCMTH], [YTCRCD], [YTCRDC], [YTAUBP])

You can see from the index that all I did was create an index over the field that the SQL was sorting on (YTDWK or Date of Work) and included the rest of the fields that were in the query.

There must be other little tricks with indexes in EnterpriseOne. Have you found any?

Author

Stewart Schatz

Career: Principal CNC Consultant for Syntax Systems Limited specializing Oracle JD Edwards EnterpriseOne and the technology that supports it. Side Hustle: Owner/Operator of E1Tips.com Location: Lancaster, PA USA What I like to do: Invest in Family, Explore Technology, Lead Teams, Share Knowledge/Experience, Hunt, Hike, etc.

stewart@9rains.com

http://stewartschatz.com