Creation of Index on DFF attributes of egp_system_items_b for a particular project requirement

Ranganadh Neelakantham

Organization Name

Ferguson

Description

Hi All,

We have an VBCS application where we can create our sales orders.From VBCS application during the time of entering the sales order lines, we will query for an Item (which is a KEYWORD search), the expectation is when we query an item , web service should give complete details of item including DFFs and EFFs within less than a second. But this is taking around ~4seconds which is causing performance issue.

So we are trying to fetch complete item details using BIP report and moving all EFFs to DFF so that we can get results faster. But when we query for an item this is taking longer time than expected. Below is the timings for your reference

ItemNumber------RunReport SOAP Service(sec)
23515-----------------4.3
SMOKE - 4222014-------3.4
SMOKE - 531458--------4.6
7800155---------------5.1
5141836---------------4.8

Below is the sample code:
SELECT
INVENTORY_ITEM_ID,
ITEM_NUMBER,
DESCRIPTION,
PRIMARY_UOM_CODE,
ATTRIBUTE1 PRODUCT_ALT_CODE
FROM EGP_SYSTEM_ITEMS_VL
WHERE 1 = 1
AND ORGANIZATION_ID = :P_ORG
AND ( ITEM_NUMBER like :P_KEYWORD OR DESCRIPTION like :P_KEYWORD OR ATTRIBUTE1 like :P_KEYWORD )

NOTE: We have raised an SR 3-24389589841.We have spoken to Jon Chorley from Oracle and submitting this idea on his advice

Use Case and Business Need

Hi All,

We have an VBCS application where we can create our sales orders.From VBCS application during the time of entering the sales order lines, we will query for an Item (which is a KEYWORD search), the expectation is when we query an item , web service should give complete details of item including DFFs and EFFs within less than a second. But this is taking around ~4seconds which is causing performance issue.

So we are trying to fetch complete item details using BIP report and moving all EFFs to DFF so that we can get results faster. But when we query for an item this is taking longer time than expected. Below is the timings for your reference

ItemNumber------RunReport SOAP Service(sec)
23515-----------------4.3
SMOKE - 4222014-------3.4
SMOKE - 531458--------4.6
7800155---------------5.1
5141836---------------4.8

Below is the sample code:
SELECT
INVENTORY_ITEM_ID,
ITEM_NUMBER,
DESCRIPTION,
PRIMARY_UOM_CODE,
ATTRIBUTE1 PRODUCT_ALT_CODE
FROM EGP_SYSTEM_ITEMS_VL
WHERE 1 = 1
AND ORGANIZATION_ID = :P_ORG
AND ( ITEM_NUMBER like :P_KEYWORD OR DESCRIPTION like :P_KEYWORD OR ATTRIBUTE1 like :P_KEYWORD )

NOTE: We have raised an SR 3-24389589841.We have spoken to Jon Chorley from Oracle and submitting this idea on his advice

More details

Hi All,

We have an VBCS application where we can create our sales orders.From VBCS application during the time of entering the sales order lines, we will query for an Item (which is a KEYWORD search), the expectation is when we query an item , web service should give complete details of item including DFFs and EFFs within less than a second. But this is taking around ~4seconds which is causing performance issue.

So we are trying to fetch complete item details using BIP report and moving all EFFs to DFF so that we can get results faster. But when we query for an item this is taking longer time than expected. Below is the timings for your reference

ItemNumber------RunReport SOAP Service(sec)
23515-----------------4.3
SMOKE - 4222014-------3.4
SMOKE - 531458--------4.6
7800155---------------5.1
5141836---------------4.8

Below is the sample code:
SELECT
INVENTORY_ITEM_ID,
ITEM_NUMBER,
DESCRIPTION,
PRIMARY_UOM_CODE,
ATTRIBUTE1 PRODUCT_ALT_CODE
FROM EGP_SYSTEM_ITEMS_VL
WHERE 1 = 1
AND ORGANIZATION_ID = :P_ORG
AND ( ITEM_NUMBER like :P_KEYWORD OR DESCRIPTION like :P_KEYWORD OR ATTRIBUTE1 like :P_KEYWORD )

NOTE: We have raised an SR 3-24389589841.We have spoken to Jon Chorley from Oracle and submitting this idea on his advice

Original Idea Number: fa7202ca91