A Simple Request or a Performance Nightmare – Painted into a Corner by the ERP System

13042010

April 13, 2010

Recently, a simple request came in from an ERP mailing list. The author wanted to modify a report used by the purchasing module of the ERP program to show additional information on the report. The purchasing module retrieves the data from the database, manipulates the data, and then pushes the manipulated data to a reporting package. Wisely, the ERP developer allowed end users to add “extended queries” to bring in additional information from the database to be displayed on the report – all that the end user needs to do is to select from a list of variables provided by the ERP system to link to the database tables, and the ERP system will automatically pass the request to the database using bind variables. Simple, right?

The table definitions look like this (primary key columns denoted by *):

The PURCHASE_ORDER table contains the header information for the order, the PURC_ORDER_LINE table contains the line level detail for the order, and the PURC_LINE_DEL table contains the dates on which the line level detail should be provided to the company and the requested quantities for each of the dates. While the delivery schedule’s date and order quantities already appear on the purchase order report, the previously received quantity and the delivery schedule line number need to be included on the report, along with a bit of other information. The problem? The ERP system does not provide a variable to target a specific delivery schedule line, but we do have the PURC_ORDER_ID (PO_ID variable), PURC_ORDER_LINE_NO (LN_LINE_NO), DESIRED_RECV_DATE (LN_DEL_SCHED_DATE), and USER_ORDER_QTY (LN_DEL_SCHED_QTY), so the first thought is to construct an extended query like this (line breaks are not permitted in the extended query, so this might be hard to read):

Line 1 of this purchase order has 8 delivery schedule lines, and in this silly situation the extended query is executed only at the PURC_ORDER_LINE level, and not at the delivery schedule level – all of the delivery schedule information is passed into the report with the multiple lines in a single field, with CRLF characters separating the values in each of the delivery schedule lines. OK, that was unexpected. Now what, do we give up? No, we use a little creativity to format the data in the required format, with all of the delivery schedule rows rolled in a single row:

The above is fairly efficient, using the primary key index on the table. We will just slide the above into an inline view in the report’s extended query (replacing the hardcoded literals with variables). The following appears when trying to display the report:

Missing expression… 😦 how about what happened to the rest of the SQL statement? Painted into a corner, again. Now what, do we give up?

OK, no problem, just define a static view rather than wrapping our SQL statement into an inline view. So, in testing it would look like this (the WHERE clause will be removed when it is created as a static view since the WHERE clause will be provided by the ERP package’s extended query):

The predicted time for execution is 2 seconds, and if there are 10 order lines ~~~ 2*10 = 20 seconds for the report to display. The end user will complain, but let’s test just in case the predicted time is incorrect. Executing the query… Returning in an hour… Why is this SQL statement still running? Notice that the predicate information in the plan, unlike for the plan of the first SQL statement, does not include access(“PLD”.”PURC_ORDER_ID”=’144038′ AND “PLD”.”PURC_ORDER_LINE_NO”=1) on any of the lines – that predicate information was not pushed into the inline view, and providing a hint to push the predicates into the inline view does not change the execution plan. The end user definitely will complain if it takes 10+ hours to print one report with the previously received quantities. Painted into a corner, again. Now what, do we give up?

If only we could somehow push at least the PURC_ORDER_ID further into the view. Any ideas?

One possible solution is to create another table in the database, something like this:

But how do we populate the CURRENT_PO_USER table? The ERP module supports the execution of VBScript macros when a purchase order is opened and when it is saved. So, we just create a VBScript macro that populates that table as needed. Painted into a corner, but fortunately there is a door in that corner of the room.

Maybe there is a better way that does not require the custom table and the custom VBScript macros?

Actions

Information

2 responses

14042010

Narendra(06:04:46) :

that predicate information was not pushed into the inline view, and providing a hint to push the predicates into the inline view does not change the execution plan.
Is that because oracle thinks pushing the predicate will change the query semantically (and hence does not do it) or is it just an optimizer limitation (or bug)?

I believe that the reason for the predicate information not being pushed in is a result of the CONNECT BY syntax. It appears that when a CONNECT BY clause is used, the START WITH clause is considered a WHERE clause, according to a portion of a 10053 trace:

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:
<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: