Skip to content
Advertisement

optimizing database record fetching time

i have 2 kind of big database tables : [product] and [product_position], a product may change position so i use a function product_pos(product_id) to get the current (meaning last) position of a said product. in my Apex report i need to show each product with its current position with this query:

the function product_pos :

the problem i have now is the fetching time, in the apex report page it takes almost 30 seconds for every loading/refresh. i thought about adding a column [position] in the product table and updating it by using a trigger for every insert/update/delete on [product_position] but that leaves a possibility for errors when someone changes the position in the [product] table manually. now is there a way to reduce the processing time or maybe a way to proceed with the trigger solution with no errors by making the new [position] column changeable by trigger only?

Advertisement

Answer

A function can impede the optimizer. You might find that a simple query works better. For instance:

Or a lateral join:

Regardless, you want an index on product_position(product_id, id, new_pos) for performance.

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement