Dear All
When i get the data from CST_CG_COST_HISTORY_V view its performance too much slow . From this view i need to get the last price of Item at specific inventory org. for one item it is working fine , but when i am running for all active item it take too much time .
Regards
Zulqarnain
Hello Zulqarnain,
The slowness you are facing is because of the vast amount of data inside the mtl_material_transactions table. In such cases, there is one check you can do before getting into the solution to avoid scanning all the data inside the table above.
1- Check the statistics whether it’s up to date or not. To check this, run the query below:
compare the total number of rows on the table with the number returned from this query and check the last analyzed date.
Suppose you found the number of rows so little than the current one or last analyzed date is old; ask the DBA to submit a concurrent program called “Gather Table Statistics” to update the statistics. Then you can try your query again.
2- The second action is a workaround to avoid accessing all the data on the table MTL_MATERIAL_TRANSACTIONS by converting the column of the last price to be a subquery within your main query or to creating a function to return the last price for each inventory item within an organization. As you said, you are selecting only the price for each item.
I hope this answer helped you or gave you an insight.
Hassan