Thanks for this information. It shows that the other columns used in your where clause (cbc_package and flag) are not very restrictive. So you need to work on getting that date filter working properly with an index.
Let's try updating statistics on the active_untill column, and running a query that doesn't use any functions on the column itself. Again, this may not return the exact results you need, but we are trying to see what performance is like with the index.
Original Message:
Sent: Mon November 06, 2023 07:54 AM
From: Indika Jinadasa
Subject: SQL query cost is very high
Hello Mike,
Thank you very much for your support.
Here are the answers to your questions.
select count(*) from cbc_mobile -> 10043638
"select count(*) from cbc_mobile where cbc_package IN ('SN' , 'YT' , 'IM' , '9IN1' , '3IN1' , 'TK7' , 'T30' , 'CMB7' , 'CMB30' , '3IN1-1' , '3IN1-5' , '9IN1-2' , '9IN1-7' , 'MX7' , 'MX30' , 'ULCBC' )- > 9900070
select count(*) from cbc_mobile where cbc_package IN ('SN' , 'YT' , 'IM' , '9IN1' , '3IN1' , 'TK7' , 'T30' , 'CMB7' , 'CMB30' , '3IN1-1' , '3IN1-5' , '9IN1-2' , '9IN1-7' , 'MX7' , 'MX30' , 'ULCBC' ) and flag = 'N'; -> 7352474
Set explain out - >
QUERY: (OPTIMIZATION TIMESTAMP: 11-06-2023 17:58:33)
------
select a.mobile_no, a.cbc_package, a.active_from, a.active_untill from cbc_mobile a, smgt004 b where a.mobile_no=b.mobile_no
and b.connection_type='PRE' and b.disconnected_on is null
-- and trunc(a.active_untill,'mi')<=trunc(current + interval(24) hour to hour, 'mi') and b.connected_date <= date(a.active_from)
and a.active_untill <= (current + 24 units hour) and b.connected_date <= date(a.active_from)
--and a.active_untill::DATETIME YEAR TO MINUTE <= (current + interval(24) hour to hour)::DATETIME YEAR TO MINUTE and b.connected_date <= date(a.active_from)
and a.cbc_package in ('SN','YT','IM','9IN1','3IN1','TK7','T30','CMB7','CMB30', '3IN1-1', '3IN1-5', '9IN1-2', '9IN1-7', 'MX7', 'MX30', 'ULCBC')
and a.flag='N' and date(a.active_untill)>date('2023-07-31')
Estimated Cost: 3828822
Estimated # of Rows Returned: 96437
1) informix.a: INDEX PATH
Filters: ((DATE (informix.a.active_untill ) > 2023/07/31 AND informix.a.cbc_package IN ('SN' , 'YT' , 'IM' , '9IN1' , '3IN1' , 'TK7' , 'T30' , 'CMB7' , 'CMB30' , '3IN1-1' , '3IN1-5' , '9IN1-2' , '9IN1-7' , 'MX7' , 'MX30' , 'ULCBC' )) AND informix.a.active_untill <= CURRENT year to fraction(3)+ interval( 24) hour(9) to hour )
(1) Index Name: informix.idx_cbc_mobile_5
Index Keys: flag (Serial, fragments: ALL)
Lower Index Filter: informix.a.flag = 'N'
2) informix.b: INDEX PATH
Filters: ((informix.b.connected_date <= DATE (informix.a.active_from ) AND informix.b.connection_type = 'PRE' ) AND informix.b.disconnected_on IS NULL )
(1) Index Name: informix.itst
Index Keys: mobile_no
Lower Index Filter: informix.a.mobile_no = informix.b.mobile_no
NESTED LOOP JOIN
Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 a
t2 b
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 77911 1366892 6252543 00:29.03 294368
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t2 55843 7020750 97080 00:27.70 3
type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 55843 96438 00:56.78 3828822
QUERY: (OPTIMIZATION TIMESTAMP: 11-06-2023 17:58:33)
------
select a.mobile_no, a.cbc_package, a.active_from, a.active_untill from cbc_mobile a, smgt004 b where a.mobile_no=b.mobile_no
and b.connection_type='PRE' and b.disconnected_on is null
-- and trunc(a.active_untill,'mi')<=trunc(current + interval(24) hour to hour, 'mi') and b.connected_date <= date(a.active_from)
and a.active_untill <= (current + 24 units hour) and b.connected_date <= date(a.active_from)
--and a.active_untill::DATETIME YEAR TO MINUTE <= (current + interval(24) hour to hour)::DATETIME YEAR TO MINUTE and b.connected_date <= date(a.active_from)
and a.cbc_package in ('SN','YT','IM','9IN1','3IN1','TK7','T30','CMB7','CMB30', '3IN1-1', '3IN1-5', '9IN1-2', '9IN1-7', 'MX7', 'MX30', 'ULCBC')
and a.flag='N' and date(a.active_untill)>date('2023-07-31')
Estimated Cost: 3828822
Estimated # of Rows Returned: 96437
1) informix.a: INDEX PATH
Filters: ((DATE (informix.a.active_untill ) > 2023/07/31 AND informix.a.cbc_package IN ('SN' , 'YT' , 'IM' , '9IN1' , '3IN1' , 'TK7' , 'T30' , 'CMB7' , 'CMB30' , '3IN1-1' , '3IN1-5' , '9IN1-2' , '9IN1-7' , 'MX7' , 'MX30' , 'ULCBC' )) AND informix.a.active_untill <= CURRENT year to fraction(3)+ interval( 24) hour(9) to hour )
(1) Index Name: informix.idx_cbc_mobile_5
Index Keys: flag (Serial, fragments: ALL)
Lower Index Filter: informix.a.flag = 'N'
2) informix.b: INDEX PATH
Filters: ((informix.b.connected_date <= DATE (informix.a.active_from ) AND informix.b.connection_type = 'PRE' ) AND informix.b.disconnected_on IS NULL )
(1) Index Name: informix.itst
Index Keys: mobile_no
Lower Index Filter: informix.a.mobile_no = informix.b.mobile_no
NESTED LOOP JOIN
NESTED LOOP JOIN
Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 a
t2 b
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 77911 1366892 6252543 00:29.03 294368
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t2 55843 7020750 97080 00:27.70 3
type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 55843 96438 00:56.78 3828822
Table schema
create table "informix".cbc_mobile
(
mobile_no varchar(10) not null ,
cbc_package varchar(10) not null ,
pack_seq_no varchar(100),
active_from datetime year to second not null ,
active_untill datetime year to second,
added_on datetime year to second not null ,
added_by varchar(15) not null ,
terminated_on datetime year to second,
terminated_by varchar(5),
flag varchar(1)
) in edrdata_06 extent size 1000 next size 1000 lock mode row;
revoke all on "informix".cbc_mobile from "public" as "informix";
create index "informix".idx_cbc_mobile_1_dup_01 on "informix".cbc_mobile
(mobile_no,cbc_package,pack_seq_no,active_from,active_untill,
added_on,added_by,terminated_on,terminated_by,flag) using
btree in idxdb_8k_4;
create index "informix".idx_cbc_mobile_2 on "informix".cbc_mobile
(active_untill) using btree in idxdb_8k_5;
create index "informix".idx_cbc_mobile_5 on "informix".cbc_mobile
(flag) using btree in idxdb_8k_5;
------------------------------
Indika Jinadasa
Original Message:
Sent: Fri November 03, 2023 08:45 AM
From: Mike Walker
Subject: SQL query cost is very high
Thank you for this, it is useful.
Can you please run the following and post the results:
select count(*) from cbc_mobile;
select count(*) from cbc_mobile where cbc_package IN ('SN' , 'YT' , 'IM' , '9IN1' , '3IN1' , 'TK7' , 'T30' , 'CMB7' , 'CMB30' , '3IN1-1' , '3IN1-5' , '9IN1-2' , '9IN1-7' , 'MX7' , 'MX30' , 'ULCBC' );
select count(*) from cbc_mobile where cbc_package IN ('SN' , 'YT' , 'IM' , '9IN1' , '3IN1' , 'TK7' , 'T30' , 'CMB7' , 'CMB30' , '3IN1-1' , '3IN1-5' , '9IN1-2' , '9IN1-7' , 'MX7' , 'MX30' , 'ULCBC' ) and flag = "N";
Also, try the query, replacing:
trunc(a.active_untill,'mi')<=trunc(current + interval(24) hour to hour, 'mi')
with
a.active_untill <= (current + 24 units hour)
This may not give you the exact same results because of the minute precision but let's see how the query performs. In fact, what is the definition of the active_untill column?
------------------------------
Mike Walker
xDB Systems, Inc
www.xdbsystems.com
Original Message:
Sent: Fri November 03, 2023 04:14 AM
From: Indika Jinadasa
Subject: SQL query cost is very high
HI Mike,
Thank you very much for the tips. Here are the table indexes details.
create index "informix".idx_cbc_mobile_1_dup_01 on "informix".cbc_mobile (mobile_no,cbc_package,pack_seq_no,active_from,active_untill,added_on,added_by,terminated_on,terminated_by,flag) using btree in idxdb_8k_4;
create index "informix".idx_cbc_mobile_2 on "informix".cbc_mobile(active_untill) using btree in idxdb_8k_5;
create index "informix".idx_cbc_mobile_5 on "informix".cbc_mobile(flag) using btree in idxdb_8k_5;
Best Regards,
Indika
------------------------------
Indika Jinadasa
Original Message:
Sent: Thu November 02, 2023 08:32 AM
From: Mike Walker
Subject: SQL query cost is very high
Hi Indika,
From the statistics at the end of the query plan, it's apparent that you are reading a lot of records, to retrieve a few. In the read of cbc_mobile, you are reading almost 6.5 million records to return 271,000...about 4%. That's a lot of records that are read unnecessarily. Those other 96% of records are being discarded because they don't match the filter conditions. You should try and find out which of these filter conditions, or combinations of them, are the most restrictive and look for an index on that. It could be that cbc_package would be a better column to be indexed than the "flag" column, or maybe both, e.g. cbc_package, flag, active_untill.
As Art said, the use of TRUNC is likely hurting you. Informix 14 now does some things that makes the use of date or datetime casting unnecessary. You may get lucky with just removing the date function from date(a.active_untill)>'2023-07-31' or changing it to a.active_untill >= '2023-07-31 00:00:00'. It may take some variations to get this right, and if the active_untill is not a great filter or is not indexed then it may be of limited benefit.
------------------------------
Mike Walker
xDB Systems, Inc
www.xdbsystems.com
Original Message:
Sent: Thu November 02, 2023 05:49 AM
From: Indika Jinadasa
Subject: SQL query cost is very high
Thanks Watson, here is the query plan.
QUERY: (OPTIMIZATION TIMESTAMP: 11-02-2023 10:22:00)
------
select a.mobile_no, a.cbc_package, a.active_from, a.active_untill from cbc_mobile a, smgt004 b where a.mobile_no=b.mobile_no
and b.connection_type='PRE' and b.disconnected_on is null
and trunc(a.active_untill,'mi')<=trunc(current + interval(24) hour to hour, 'mi') and b.connected_date <= date(a.active_from)
and a.cbc_package in ('SN','YT','IM','9IN1','3IN1','TK7','T30','CMB7','CMB30', '3IN1-1', '3IN1-5', '9IN1-2', '9IN1-7', 'MX7', 'MX30', 'ULCBC')
and a.flag='N' and date(a.active_untill)>'2023-07-31'
Estimated Cost: 1485208
Estimated # of Rows Returned: 32364
1) informix.a: INDEX PATH
Filters: ((trunc(informix.a.active_untill , 'mi' ) <= trunc(CURRENT year to fraction(3)+ interval( 24) hour to hour , 'mi' ) AND DATE (informix.a.active_untill ) > 2023/07/31 ) AND informix.a.cbc_package IN ('SN' , 'YT' , 'IM' , '9IN1' , '3IN1' , 'TK7' , 'T30' , 'CMB7' , 'CMB30' , '3IN1-1' , '3IN1-5' , '9IN1-2' , '9IN1-7' , 'MX7' , 'MX30' , 'ULCBC' ))
(1) Index Name: informix.idx_cbc_mobile_4
Index Keys: flag (Serial, fragments: ALL)
Lower Index Filter: informix.a.flag = 'N'
2) informix.b: INDEX PATH
Filters: ((informix.b.connected_date <= DATE (informix.a.active_from ) AND informix.b.connection_type = 'PRE' ) AND informix.b.disconnected_on IS NULL )
(1) Index Name: informix.itst
Index Keys: mobile_no
Lower Index Filter: informix.a.mobile_no = informix.b.mobile_no
NESTED LOOP JOIN
Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 a
t2 b
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 271329 460904 6456016 00:49.88 294370
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t2 248436 6997341 309213 01:02.00 3
type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 248436 32364 01:52.02 1485208
------------------------------
Indika Jinadasa
Original Message:
Sent: Thu November 02, 2023 05:08 AM
From: Paul Watson
Subject: SQL query cost is very high
Query plan would be useful On 11/2/2023 4:06 AM, Indika Jinadasa via IBM TechXchange Community wrote:
0100018b8f4925bb-37054389-e021-4f73-91b4-f0b956ba7f90-000000@email.amazonses.com"> Dear Friends, We are using IDS 14X and the below SQL query is taking high query cost even if it perform INDEX path. Could you pl help us to...
Original Message:
Sent: 11/2/2023 5:07:00 AM
From: Indika Jinadasa
Subject: SQL query cost is very high
Dear Friends,
We are using IDS 14X and the below SQL query is taking high query cost even if it perform INDEX path. Could you pl help us to optimize the SQL query below.
select a.mobile_no, a.cbc_package, a.active_from, a.active_untill from cbc_mobile a, smgt004 b where a.mobile_no=b.mobile_no
and b.connection_type='PRE' and b.disconnected_on is null
and trunc(a.active_untill,'mi')<=trunc(current + interval(24) hour to hour, 'mi') and b.connected_date <= date(a.active_from)
and a.cbc_package in ('SN','YT','IM','9IN1','3IN1','TK7','T30','CMB7','CMB30', '3IN1-1', '3IN1-5', '9IN1-2', '9IN1-7', 'MX7', 'MX30', 'ULCBC')
and a.flag='N' and date(a.active_untill)>'2023-07-31'
Thanks !
Best Regards,
Indika
------------------------------
Indika Jinadasa
------------------------------