|
Problem IT30892
|
Status: Closed |
INT(DATE) IS NOT PUSH DOWN IN CDE, CAUSING QUERY SLOWNESS.
|
| product: |
DB2 FOR LUW / DB2FORLUW / B10 - DB2 |
| Problem description: |
Test case shows that INT (date) takes more execution time ,
than others for the same SQL output & no. of records.
The reason for slowness is due to because the SORT/GRPBY is done
above CTQ and processing more records than neccessary.
RUN 1 ************* INT(SK_DTINTEG)/100 ===>bad plan
select int(sk_dtinteg)/100, count_big(*) from ps_sk_cre_me group
by int(sk_dtinteg)/100 order by 1
----------- ---------------------------------
201708 63202216.
.../...
201908 186175174.
25 record(s) selected.
real 15m31.045s
RUN 2 ************* INT(HEX(SK_DTINTEG))/100 ==>medium good
plan
select int(hex(sk_dtinteg))/100, count_big(*) from ps_sk_cre_me
group by int(hex(sk_dtinteg))/100 order by 1
----------- ---------------------------------
201708 63202216.
.../...
201908 186175174.
25 record(s) selected.
real 5m0.350s
RUN 3 ************* SUBSTR(HEX(SK_DTINTEG)) ==>Good plan
select substr(hex(sk_dtinteg),1,6), count_big(*) from
ps_sk_cre_me group by substr(hex(sk_dtinteg),1,6) order by 1
------ ---------------------------------
201708 63202216.
.../...
201908 186175174.
25 record(s) selected.
real 1m38.767s
============================ |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* Platform independent, its a performance issue *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Fix is available in 'db2_v111m4fp5' *
**************************************************************** |
| Local Fix: |
|
| Solution |
|
| Workaround |
****************************************************************
* USERS AFFECTED: *
* Platform independent, its a performance issue *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Fix is available in 'db2_v111m4fp5' *
**************************************************************** |
| Comment |
The SB fix will resolve the slow performance issue by doing
group by/sort below the CTQ operation. |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 07.11.2019
14.01.2020
14.01.2020 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
