The table function fn_parse_string_del parses a comma separated string.
Ex: The string 'TH663,TH550,TH445' is parsed as rows
TH663
TH550
TH445
I have used the table function fn_parse_string_del in my query below and it is taking more time say about 7 secs but the query with IN operator ('TH663','TH550','TH445') is taking less than a second to execute.
When I execute the table function fn_parse_string_del individually it is very quick (200 ms).
I want to use table function in my query for a different requirement.
Can you please help how to improve the performance of the query?
SELECT
KPI_DATE AS "Date",
ISNULL(ROUND(SUM(WR)),
0) AS "Actual",
ISNULL(ROUND(SUM(WR_PLANNED)),
0) AS "Planned"
FROM
OPT1VHZM.VIEW_DM_HZLMEASUREMENTS_SHIFT_ENTITY a,
OPT1VHZM.CALENDAR_NEW b,
OPT1VHZM.DATES_LOOKUP d
WHERE
--ENTITY IN ('TH663,TH550')
MODEL IN (SELECT ENTITY FROM TABLE(OPT1VHZM_DRILLS.fn_parse_string_del('TH663')))
AND b.CDR_DATE = a.KPI_DATE
AND b.HOUR_NO IN ( 99,
100 )
AND KPI_DATE BETWEEN D.MBD_DATE AND DATE(D.DDT_DATE)
AND a.SHIFT_ID = b.shift_id
GROUP BY
KPI_DATE
Ex: The string 'TH663,TH550,TH445' is parsed as rows
TH663
TH550
TH445
I have used the table function fn_parse_string_del in my query below and it is taking more time say about 7 secs but the query with IN operator ('TH663','TH550','TH445') is taking less than a second to execute.
When I execute the table function fn_parse_string_del individually it is very quick (200 ms).
I want to use table function in my query for a different requirement.
Can you please help how to improve the performance of the query?
SELECT
KPI_DATE AS "Date",
ISNULL(ROUND(SUM(WR)),
0) AS "Actual",
ISNULL(ROUND(SUM(WR_PLANNED)),
0) AS "Planned"
FROM
OPT1VHZM.VIEW_DM_HZLMEASUREMENTS_SHIFT_ENTITY a,
OPT1VHZM.CALENDAR_NEW b,
OPT1VHZM.DATES_LOOKUP d
WHERE
--ENTITY IN ('TH663,TH550')
MODEL IN (SELECT ENTITY FROM TABLE(OPT1VHZM_DRILLS.fn_parse_string_del('TH663')))
AND b.CDR_DATE = a.KPI_DATE
AND b.HOUR_NO IN ( 99,
100 )
AND KPI_DATE BETWEEN D.MBD_DATE AND DATE(D.DDT_DATE)
AND a.SHIFT_ID = b.shift_id
GROUP BY
KPI_DATE