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.
- I can not create indexes on view columns which is a restriction on the views.
- 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.
- 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
- I am attaching Explain Analyze results for this SQL in an excel file, Explain-Analyze-Net-Temps-Slow-Response-Bao.xlsx
-
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