View Optimization

Hi,

I have requirement to optimize a view. The structure of view is available as below with multiple select commands based on code. 

SELECT
`S`.`ID` AS `ID`,
(select `TEST_MASTER_DATA`.`LABEL` from `TEST_MASTER_DATA` where ((`TEST_MASTER_DATA`.`CODE` = `S`.`CODE`) and (`TEST_MASTER_DATA`.`FIELD` = 5)))
AS `STATUS_CODE`,

`S`.`INFO` AS `INFO`

FROM `Test_Sales` `S`
LEFT JOIN `Test_FollowUp` `F` ON `S`.`ID`= `F`.`ID` ;

I have multiple select commands with in the view to fetch the label from master data based on the code since we are saving code in the sales table.

Since we are triggering multiple calls with reference data the view is taking too much time in the user interface level.

Is there any way to optimize the view to improve the performance.

Thanks in Advance

  Discussion posts and replies are publicly visible

Parents
  • Hi Krishna,

    It all depends on the actual requirement what needs to be included and what can be excluded from the tables that your are joining. We have also had so many tables joined through the view some times we get pink error while retrieving the data.

    What is the delay that you are currently experiencing on UI? Of course the view is having double select one is from Appian and other is actual select of the view.

    If you are okay I would recommend to user the SP which can return wither xml or json if it oracle 12g Enterprise edition. This would be the last option.

    I would also suggest that, DBA can only help in this case.
  • Hi shanmukh,

    Since the application is already implemented we just want to make changes to optimize the code without much changes. Since we have too much data in DB even query entity is not able to fetch that data. we are using this view to export the data to excel using Export Sql to Excel/CSV smart service where it is taking too much time.
    Is there any way to optimize the view query if it is possible?

    Thanks
Reply
  • Hi shanmukh,

    Since the application is already implemented we just want to make changes to optimize the code without much changes. Since we have too much data in DB even query entity is not able to fetch that data. we are using this view to export the data to excel using Export Sql to Excel/CSV smart service where it is taking too much time.
    Is there any way to optimize the view query if it is possible?

    Thanks
Children
No Data