How to tune SQL performance of function based columns of a view

[M Sarwar <sarwarmd02@xxxxxxxxxxx>]

 

Hello,

 

I am working on SQL performance issue.

Here is my SQL.

 

SELECT STAGE                                                                    AS STAGE__C,

                NET_TEMP_YIELD                                                             AS NET_TEMP_YIELD__C,

                LATEST_SYSTEM_ID_PER_STAGE                AS LATEST_SYSTEM_ID__C,

                HIGHEST_TEMP_TESTED                                AS HIGHEST_TEMP_TESTED__C,

                PASSED_ALL_TEMPS                                       AS PASSED_ALL_TEMPS__C,

                NUM_TEMPS_TESTED                                    AS NUM_TEMPS_TESTED__C,

                NUM_REQUIRED_TEMPS                                              AS NUM_REQUIRED_TEMPS__C,

                MCM_ID                                                                                              AS MCM_ID__C

FROM BRONX.VW_TAB_MCM_NET_TEMP_YIELD_MID

where MCM_ID in

(

'B70725Z2','B7072Z76','B7072Z80','B7072Z81'

)

;

 

BRONX.VW_TAB_MCM_NET_TEMP_YIELD_MID is a view. I am attaching the code of BRONX.VW_TAB_MCM_NET_TEMP_YIELD_MID if anyone can take a look.

View, VW_TAB_MCM_NET_TEMP_YIELD_MID calls another view called VW_TAB_MCM_TEST_RESULTS_MID.SQL. I am attaching this code as well.

 

1. I can not create indexes on view columns which is a restriction on the views.
2. When I am using constant values for MCM_IDs as stated in the SQL, query performance is going down by 20 times which is unacceptable.
3. Column MCM_ID is a function column "substring"(p.SERIAL_NUMBER::TEXT, '[A-Z][0-9]+'::TEXT) AS MCM_ID from the view, VW_TAB_MCM_TEST_RESULTS_MID.SQL
4. I am attaching Explain Analyze results for this SQL in an excel file, Explain-Analyze-Net-Temps-Slow-Response-Bao.xlsx
5.  

 

After analyzing the results from the file, Explain-Analyze-Net-Temps-Slow-Response-Bao.xlsx, I tried to create several indexes one by one based on the possibility from Explain analyze results and none of them are helping in improving the performance of SQL.

I am just trying to know what are the other approaches which I can use to resolve this performance issue.

 

Thank you,

Sarwar

 

Attachment: vw_tab_mcm_net_temp_yield_mid.SQL
Description: vw_tab_mcm_net_temp_yield_mid.SQL

Attachment: vw_tab_mcm_test_results_mid.SQL
Description: vw_tab_mcm_test_results_mid.SQL

Attachment: Explain-Analyze-Net-Temps-Slow-Response-Bao.xlsx
Description: Explain-Analyze-Net-Temps-Slow-Response-Bao.xlsx