View Optimization

Question

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

Abhay Giri · Answer

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

Abhay Giri · Answer

indexing, where ? in view? 
 if you can't implement the above way then we can optimize the view itself. May be the chances that we can rearrange the view for better performance. 
 you can also refer the below link : it has some very good suggestion to optimize the query.Cross check with your query may be you will find some way to improve your query. 
 https://www.safaribooksonline.com/library/view/high-performance-mysql/9780596101718/ch04.html 
 https://dev.mysql.com/doc/refman/8.0/en/view-restrictions.html 
 If you face the problem then we need query to examine. 
 
 Regards 
 Abhay Giri

nickh647 · Answer

You will need to add indexes as others have suggested on the Join Columns. In general, if queries or views are being performed on a table where filtering or joining needs to occur you should take the time to research the proper syntax and which indexes are appropriate to use (ie fulltext vs index) 
 
If you are using MySQL, queries don't always automatically use the best indexes available, but I have had success in utilizing the 'USE INDEX' syntax 
 
If you are going to be filtering on the view (ie. select * from view where condition=true) I would suggest instead to use a stored procedure to construct a sql query for this data. When querying against a view the indexes on the underlying tables aren't used and you will run into performance issues. 
 
You should also utilize the EXPLAIN SELECT on your query to find out if any full table scans are being performed and try to limit those

View Optimization

Top Replies