Thanks for your suggestion...I still have one doubt: the dates of the
query can change, so I will have to recreate the view everytime I
have to run the query. Is this a good politic?

If I create a selectable stored procedure, with the dates as
parameteres, instead a view, will it give the same performance?

Diego

--- In firebird-support@yahoogroups.com, Ali Gökçen <alig@e...> wrote:
> ok, i did'n see the MSISDN condition in subquery..
>
> I hope i interpreted your query
> please replace my previous mail with this line
> ...
>
> SELECT MSISDN,max(OC.INTERACT_ID)
> ...
> group by MSISDN
> ...
>
> Ali
>
> --- 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
> >
> >
> >
> > [Non-text portions of this message have been removed]