Hi Diego,

Yup, you can use a SP instead of view, to pass of dates.

main tricks here are to escape from subselect cost and
to use of MSISND index at maximum level, minimize table scanning.

Regards.

Ali

--- In firebird-support@yahoogroups.com, "zimrilin2000"
<drodriguez@a...> wrote:

> 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]