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,

    You can try the way as well, if it suits in your requirement:

    1-> create a table with the same columns as in your view has.
    2-> instead of fetching the data from view into Appian, fetch the data in the table with one extra column, i.e basically it will be a bool column.
    3->Now fetch the data from this table instead of view but we are not going to fetch data at once. Will fetch the data in batches .Just divided the whole data into small batches and then fetch. Once all data will be fetched then generate the excel file.

    It requires little bit changes but it surly help you.

    Regards
    Abhay
  • Hi Abhi,

    Do you want me to populate the temp table also whenever the sales table is getting populated similar to the data how view is getting formed right? what is the use of boolean column?

    Thanks
  • right, we use boolean variable to mark the record that this records has been fetched so in next time will retrieve next record not this one. Suppose your temp table will get 1000 records from view so in my logic, will fetch this 1000 records in 5 time 1000/5=200 each time. When first time data will fetch then first 200 record will come and this boolean variable marked as true so in second time will fetch records from 201-400 as so on.

    Abhay
Reply
  • right, we use boolean variable to mark the record that this records has been fetched so in next time will retrieve next record not this one. Suppose your temp table will get 1000 records from view so in my logic, will fetch this 1000 records in 5 time 1000/5=200 each time. When first time data will fetch then first 200 record will come and this boolean variable marked as true so in second time will fetch records from 201-400 as so on.

    Abhay
Children