Hi Diego!

Your description lacks a few important things: The query PLAN and a
description of the selectivity of the indexes.

Myself, I often do something similar to

SELECT COUNT(*)
FROM OPPORTUNITY_CALLS OC
WHERE OC.OPP_DATE BETWEEN '2005-10-01' AND '2005-10-10'
AND OC.MSISDN STARTING '6'
AND OC.LEVEL = 1
AND NOT EXISTS(
SELECT 1
FROM OPPORTUNITY_CALLS OC2
WHERE OC.MSISDN=OC2.MSISDN
AND (2=0
OR (OC2.OPP_DATE BETWEEN '2005-10-01' AND '2005-10-10'
AND OC.INTERACT_ID < OC2.INTERACT_ID)))

But that may be because I'm used to MSISDN being very selective (well,
I don't work with anything remotely similar to ISDN), whereas the
other requirements rarely are (I'm used to there being 800000
different MSISDNs in a table with 1000000 records). If you have the
opposite (e.g. 50 different MSISDNs in your 5000000 records table),
then you would definitely not want to do this. The 2=0 part is simply
to prevent the optimizer from using less selective indexes than what
would be optimal.

HTH,
Set

--- In firebird-support@yahoogroups.com, "Diego Rodriguez" wrote:
>
> 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