Hi all!

I have a query performance problem. The query only uses one table. I have to make a count on MSISDN that called during a period and have a value of field LEVEL of 1. One MSISDN could call more than once during that period (and could have another value of LEVEL), and I have to count only the last one (surrogate key field INTERACT_ID the greater of all MSISDN for that period) if the last one is LEVEL 1. The table has now 5 million rows and will have at most 12 millions.

My first approach was to check if EXISTS another MSISDN for that period with an INTERACT_ID greater. I have read in the list and in Helen book that is better to join the table to itself than use the EXIST clause, but I don't know how to do it. This works, but as the table grows performace is getting worse and with 5 millions is almost non acceptable, so when it reaches 12 millions in some weeks it will hang my app.


Is there a way to do it better?

Using Firebird classic server 1.5.2 in a Windows XP plattform with 1Gb RAM

This is the query

SELECT COUNT (OC.MSISDN)
FROM OPPORTUNITY_CALLS OC
WHERE OC.OPP_DATE >= '2005-10-01' AND OC.OPP_DATE <= '2005-10-10'
AND OC.MSISDN LIKE '6________'
AND OC.LEVEL = 1
AND NOT EXISTS(
SELECT 1
FROM OPPORTUNITY_CALLS OC2
WHERE OC.MSISDN=OC2.MSISDN
AND OC2.OPP_DATE >= '2005-10-01' AND OC2.OPP_DATE <= '2005-10-10'
AND OC.INTERACT_ID < OC2.INTERACT_ID
)

And this is the table

CREATE TABLE CALLS (
INTERACT_ID INTEGER NOT NULL,
MSISDN VARCHAR(9),
OPP_DATE DATE,
OPP_TIME TIME,
LEVEL SMALLINT);

ALTER TABLE OPPORTUNITY_CALLS ADD CONSTRAINT PK_OPPORTUNITY_CALLS PRIMARY KEY (INTERACT_ID);


Many thanks

Diego



[Non-text portions of this message have been removed]