Amit:
So, just to be clear, this query:
select count(*) from cs_subr_pcn_00 cb
WHERE EXISTS (SELECT cl.cust_numb , cl.subr_numb from c cs, cs_clse_cust cl
WHERE cs.cust_numb = cl.cust_numb
AND cl.cust_numb = cb.cust_numb
AND cl.subr_numb = cb.subr_numb
);
which joins a nearly 15 million row table to a 114 million row table and a nearly 150 million row table so you can count the number of rows in the result set is slow. Hmm. Understandable. My first thought was to propose to eliminate the subquery and use something like this:
SELECT count( * )
FROM cs_subr_pcn_00 AS cb
JOIN cs_clse_cust AS cl
ON cb.cust_numb = cl.cust_numb
AND cb.subr_numb = cl subr_numb
JOIN cs_subr_pcn_00 AS cs
ON cs.cust_numb = cl.cust_numb
;
But that will likely have the same query plan as the optimizer has already removed the sub-query and is doing key-only searches of the three indexes. So, I don't think it will be any faster. I'd like to see the statistical details at the end of the SET EXPLAIN output.
So, then I realized that this question hits my biggest baddest pet peeve. You have basically asked "I tried this solution to my problem and it doesn't work! Help me make it work!" What would be better is if you said "I have this problem that I cannot solve. I tried this one solution, and it runs WAY too long. Can you think of a better way to get what I want?" SO, because I don't think you actually need to know this count() but rather need it for something else, please post the problem and we'll see if we can't figure something out.
------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com------------------------------
Original Message:
Sent: Thu August 12, 2021 08:21 AM
From: AMIT PATEL
Subject: SQL Time
I'm running below query with set explain on.
QUERY: (OPTIMIZATION TIMESTAMP: 08-12-2021 18:44:05)
------
select count(*) from cs_subr_pcn_00 cb
WHERE EXISTS (SELECT cl.cust_numb , cl.subr_numb from cs_cust cs, cs_clse_cust cl
WHERE cs.cust_numb = cl.cust_numb
AND cl.cust_numb = cb.cust_numb
AND cl.subr_numb = cb.subr_numb
)
{
select count(*) from optim_cs_subr_pcn_00;
select count(*) from cs_subr_pcn_00;
select count(*) from optim_cs_cust_srch_09;
select count(*) from cs_cust_srch_09;
}
Estimated Cost: 14248124
Estimated # of Rows Returned: 1
1) informix.cb: INDEX PATH
(1) Index Name: informix.neo_csp_subr02_00_01
Index Keys: cust_numb subr_numb (Key-Only) (Serial, fragments: ALL)
2) informix.cl: INDEX PATH
(1) Index Name: informix.pk_cs_clse_cust
Index Keys: cust_numb subr_numb (Key-Only) (Serial, fragments: ALL)
Lower Index Filter: (informix.cl.cust_numb = informix.cb.cust_numb AND informix.cl.subr_numb = informix.cb.subr_numb )
NESTED LOOP JOIN
3) informix.cs: INDEX PATH
(1) Index Name: informix.csp_cust_01
Index Keys: cust_numb (Key-Only) (Serial, fragments: ALL)
Lower Index Filter: informix.cs.cust_numb = informix.cb.cust_numb
NESTED LOOP JOIN
select count(*) from cs_subr_pcn_00; -- 14965091
select count(*) from cs_cust; -- 149553382
select count(*) from cs_clse_cust; -- 114596229
But this SQL takes very long time mat be around 40 minutes to execute , sometimes I take cs_cust and cs_clse_cust table data in temp table also but then also it takes long time.
Please suggest to speed up the SQL.
UPDATE STATICS run on all tables.
Thanks
Amit
------------------------------
AMIT PATEL
------------------------------
#Informix