Release Date: 11/29/2017

Q - I used the following SQL SELECT statement to retrieve product structure from the BOMP module. Generally speaking, it works. But the performance is not what I would like it to be. Is there anyway to make it run faster?

SELECT PRD_STR_PAR_ITEM_NO, PAR.ITEM_DESC1, PAR.ITEM_DESC2, PRD_STR_SEQ_NO,
PRD_COMP_ITEM_NO, COMP.ITEM_DESC1 AS COMP_ITEM_DESC1,
COMP.ITEM_DESC2 AS COMP_ITEM_DESC2, PRD_STR_QTY_PER_PAR,
COMP.ITEM_UOM AS COMP_ITEM_UOM, COMP.ITEM_STD_COST AS 'Comp Cost',
PAR.ITEM_USER_DEF_CD, COMP.ITEM_STD_COST AS 'Assembly Cost', PAR.ITEM_DRAWING_REL_NO,
PAR.ITEM_ACTIVITY_CD, PRD_EFF_DATE, PRD_STR_OBSOLETE_DT
FROM (BMPRDSTR INNER JOIN IMITMFIL AS PAR 
ON PRD_STR_PAR_ITEM_NO = PAR.ITEM_NO)
INNER JOIN IMITMFIL AS COMP ON PRD_COMP_ITEM_NO = COMP.ITEM_NO
WHERE PAR.ITEM_ACTIVITY_CD <> 'O'
ORDER BY PRD_COMP_ITEM_NO;
 
A - I tested the above SQL statement with a database in which the BMPRDSTR.BTR file size is 63MB and the IMITMFIL.BTR file size is 130MB. The first time I ran this statement, it took 40 second to finish. The second time, because data is already cached in the server memory, it took only 5 second to finish. Even though 5 second is not a lot, I feel there's room for improvement. So I revised the SQL SELECT statement as follows:

SELECT PRD_STR_PAR_ITEM_NO, PAR.ITEM_DESC1, PAR.ITEM_DESC2, PRD_STR_SEQ_NO,
PRD_COMP_ITEM_NO, COMP.ITEM_DESC1 AS COMP_ITEM_DESC1,
COMP.ITEM_DESC2 AS COMP_ITEM_DESC2, PRD_STR_QTY_PER_PAR,
COMP.ITEM_UOM AS COMP_ITEM_UOM, COMP.ITEM_STD_COST AS 'Comp Cost',
PAR.ITEM_USER_DEF_CD, COMP.ITEM_STD_COST AS 'Assembly Cost', PAR.ITEM_DRAWING_REL_NO,
PAR.ITEM_ACTIVITY_CD, PRD_EFF_DATE, PRD_STR_OBSOLETE_DT
FROM  IMITMFIL AS PAR, BMPRDSTR, IMITMFIL AS COMP 
WHERE PAR.ITEM_NO IN (SELECT DISTINCT PRD_STR_PAR_ITEM_NO FROM BMPRDSTR) 
AND PAR.ITEM_ACTIVITY_CD <> 'O'
AND PAR.ITEM_NO = BMPRDSTR.PRD_STR_PAR_ITEM_NO
AND PRD_COMP_ITEM_NO = COMP.ITEM_NO
ORDER BY PRD_COMP_ITEM_NO;

This SQL SELECT statement took about 1 second to finish. Why is it faster? As you can see we moved the INNER JOIN out of the FROM clause to the WHERE clause as the last two conditions. In the WHERE clause, we began with the following condition:

      WHERE PAR.ITEM_NO IN (SELECT DISTINCT PRD_STR_PAR_ITEM_NO FROM BMPRDSTR)  

This narrowed down the first item set to the parent item only. Then it further excluded the obsolete parent items by using   PAR.ITEM_ACTIVITY_CD <> 'O'. Now this is a smaller subset. We then used it to join with the BMPRDSTR table, then joined with IMITMFIL to the retrieve the component information. This resulted in faster performance.

The key for faster join performance is to narrow down the data to a subset first before performing the inner join.

EMK