Hi there,

I need some guidance on the sql statement that i have.
Its running very slow and i think the Distinct and Group By play their part in it
but not sure how to enhance this code.

SELECT DISTINCT A, B, C, COUNT(*) D
FROM TABLE_A, TABLE_B
WHERE E = '01'
AND TABLE_A.F = TABLE_B.H
AND TABLE_A.J = TABLE_B.K
AND TABLE_A.L <> TABLE_B.M
GROUP BY A,B,C

Hope someone can point me a right direction.
Thanks in advance

Hi,

you are right, the distinct/group by thing is one of the responsible actors, but maybe not the only one, therefore a number of further questions:
0) set expectations: what do you call "very slow"? Can you please tell how long does this query take to complete, and what duration do you expect?
1) Are your statistics up to date (i-e when has 'update statistics' been run for the last time)
2) Is this the first time you run this query, or was it fast before and slow now ?
3) If (1) has not been executed recently, please run it with dbaccess as informix or a dba role, then, before rerunning your query, add the set explain on statement, like this:
send me the sqexplain.out file that you will find i, the current directory.
4) How many rows do table_a and table_b have?
5) can you also post the schema of both tables, including indexes?

We'll talk again after I have all this.
Regards,
Eric