Using table functions in Query

[MohanV]

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

 

[fredericofonseca]

can you post the ddl for the function please

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[MohanV]

--Here is the DDL
CREATE OR REPLACE
FUNCTION OPT1VHZM_DRILLS.fn_parse_string_del(text VARCHAR(1000) ) RETURNS TABLE
( "ENTITY" VARCHAR(10) )
--LANGUAGE SQL

NO EXTERNAL ACTION
LANGUAGE SQL
READS SQL DATA NOT DETERMINISTIC
--DISALLOW PARALLEL
--CARDINALITY 10
--BEGIN ATOMIC
RETURN (
SELECT
MEMBER
FROM ( WITH parse (lvl,
MEMBER,
tail) AS (
SELECT
1,
CASE
WHEN LOCATE(',', members) > 0 THEN TRIM(LEFT(members, LOCATE(',', members)-1))
ELSE TRIM(members)
END,
CASE
WHEN LOCATE(',', members) > 0 THEN SUBSTR(members,
LOCATE(',', members)+ 1)
ELSE ''
END
FROM (
SELECT TEXT AS members
FROM SYSIBM.SYSDUMMY1 )
UNION ALL
SELECT
lvl + 1,
CASE
WHEN LOCATE(',', tail) > 0 THEN TRIM(LEFT(tail, LOCATE(',', tail)-1))
ELSE TRIM(tail)
END,
CASE
WHEN LOCATE(',', tail) > 0 THEN SUBSTR(tail,
LOCATE(',', tail)+ 1)
ELSE ''
END
FROM parse
WHERE lvl < 100
AND tail != '')
SELECT
LVL,
MEMBER
FROM parse
ORDER BY
1 ) );

 

[fredericofonseca]

hum. well known slow cte - should be deterministic though.

what DB2 version and which os - depending on version I may be able to get a new version of it that should perform better.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[MohanV]

Thanks a lot!! Frederico

The database is dash db (DB2 database hosted in IBM Cloud).
DB2 version is DB2 v11.5.4.0.

I am not sure which OS the DB2 is hosted.

 

[fredericofonseca]

Ok.. not as good as I was expecting - it seems that at least on the free version of DB2 cloud analytical functions are rather slow. and this associated with the fact that DB2 does not have a useful way of limiting the number of records from a sub select 2 of the options I had in mind proved a lot slower than expected.

attached are 5 functions - one is your own function with minor adjustment on the trim function.
[ul]
[li]fn_parse_string_del -- original version with trims moved - and return table changed to match remaining functions[/li]
[li]splitrcte - shorter version of the function fn_parse_string_del[/li]
[li]fn_parse_string_xml -- using XML to do the split[/li]
[li][/li]
[li]DelimitedSplit8K -- rewrite of a very famous function for SQL Server[/li]
[li]DelimitedSplit8K_LEAD -- rewrite of a very famous function for SQL Server (based on above)[/li]
[/ul]


to my big surprise the XML version did behave quite well even with bigger volumes than those below - did have times with 25, 35 and 49 strings per record and although 3-4 times slower than the CTE versions still significantly faster than the 2 delimitedsplit8k



timings for the functions below.
these are based on an input table with 10.000 records, with 10 comma delimited strings on format KK999 each.
upon split output was 100.000 rows so a significant enough volumne.

lost the code to create and populate the table unfortunately due to a crash of Data Studio.
I would appreciate if you could test these yourself on your server so we could compare performance of them with your settings/allocated resources.

delete testoutput;
/*
100,000 rows.
 
Query execution time => 705 ms
Query execution time => 570 ms
Query execution time => 558 ms
Query execution time => 618 ms
*/
insert into testoutput
select entity
from sampledata t
cross join table(fn_parse_string_del(strings, ','));
delete testoutput;
/*
100,000 rows.
 
Query execution time => 557 ms
Query execution time => 521 ms
Query execution time => 505 ms
Query execution time => 503 ms
*/
insert into testoutput
select item
from sampledata t
cross join table(splitrcte(strings, ',')); 

delete testoutput;
/*
100,000 rows.
 
Query execution time => 2 s: 777 ms
Query execution time => 2 s: 494 ms
Query execution time => 2 s: 514 ms
Query execution time => 2 s: 477 ms
*/
insert into testoutput
select item
from sampledata t
cross join table(fn_parse_string_xml(strings, ','));  

delete testoutput;
/*
100,000 rows.
 
Query execution time => 8 s: 355 ms
Query execution time => 8 s: 476 ms
Query execution time => 9 s: 318 ms
Query execution time => 8 s: 315 ms
*/
insert into testoutput
select item
from sampledata t
cross join table(DelimitedSplit8K(strings, ','));  

delete testoutput;
/*
100,000 rows.
 
Query execution time => 11 s: 740 ms
Query execution time => 11 s: 614 ms
Query execution time => 11 s: 670 ms
Query execution time => 11 s: 778 ms
*/
insert into testoutput
select item
from sampledata t
cross join table(DelimitedSplit8K_LEAD(strings, ','));

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886