Sorry, I forgot to mention I have an index on MSISDN. It is a
composite index with primary key (MSISDN, INTERACT_ID), as there are
a lot of duplicates

Diego

--- In firebird-support@yahoogroups.com, "Adam" <s3057043@y...> wrote:
> --- In firebird-support@yahoogroups.com, "Diego Rodriguez"
> <drodriguez@a...> 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
> >
>
> Do you have any index for this query to use apart from the primary
> key? Apart from in the subselect, the primary key is not even
> referenced so it would have to do a table scan if that is all you
give it.
>
> Adam