Thursday, June 24, 2010

LIPS / LIKP table join efficency


Had a bit of a poser with a report that was taking 9.43 minutes to run. The problem was at the LIPS and LIKP table join. The only search criteria available was the EBELN and EBELP fields from the EKKO / EKPO table. What was needed was to get the value of VBELN using VGBEL in LIPS so a link to LIKP could be established and a couple of fields could be retrieved from LIKP that way for the report based on an entire ITAB full of EBELN entries gathered from the PO tables. The secondary index of VGBEL is not active for the LIPS table so the SQL optimisers were ineffective. Tried many ways to get the efficiency up on the report but with over a million records to search though there was just no easy way for the program to get the information needed using this configuration.

There is good news though. There is a table that holds the delivery document numbers that are associated to the Purchase order documents numbers that is other than LIPS.
The EKES table hold the cross reference and is a table that has just Purchase Order related documents stored in it. So I joined the EKES table with my LIPS / LIKP join at LIPS-VBELN = EKES-VBELN and in the "for all entries in ITAB where" used Where EKES~EBELN = ITAB-EBELN. This linked my group of PO EBELN's to the VBELN numbers supplying the link I needed to join the LIPS and LIKP tables using their primary keys. The run time of the report is now .45 minutes.
Here is a runtime charting of the two versions of the program as it ran in the ECD test environment: Figure 1 show with just LIPS / LIKP joined using VGBEL as the search and Figure 2 shows the runtime after adding in the EKES table as the cross reference.