DB2 v7.2 Table Function Performance Issue
Posted on 2009-02-14
Good day Experts,
We are currently working in DB2 environment and we wrote two DB2 User Defined Table Functions (UDF). Let's called TableFunction_A, and TableFunction_B. The TableFunction does the following:
select col1, col2, col3 from table_B_1
select col1, col2, col3 from table_B_2
select col1, col2, col3 from table_B_3;
select col1, col2, col3 from table_A_1
select col1, col2, col3 from table_A_2
select col1, col2, col3 from table_A_3
select col1, col2, col3 from table(tablefunction_b()) as tabfuncb;
When we did the following test, we got some interesting performance:
(1) When we execute the TAbleFunction_B by itself, results come back within 30 seconds.
(2) We take the select statement from the TableFunction_A and run it, results come back within 35 seconds.
(3) But when we execute TableFunction_A by itself, it took over 25 minutes for the results to return.
As from the above, the select statement is the same in scenario (2) and (3), we expected the results should be somewhat the same, and it certainly surprises us.
Is there anyone experience this and if someone knows what the cause to it so that we can get this fix?
Thank you very much for your time and your input is highly appreciated.