Ok, i did a lot of mistake today!

here is my clean code:

create view myview as
SELECT OC.MSISDN,max(OC.INTERACT_ID)
FROM OPPORTUNITY_CALLS OC
WHERE OC.OPP_DATE BETWEEN '2005-10-01' AND '2005-10-10'
AND OC.MSISDN STARTING WITH '6'
AND substring(OC.MSISDN,9,1)<>''
AND substring(OC.MSISDN,10,1)=''
AND OC.LEVEL = 1
GROUP BY MSISDN

select count(*) from myview

FB should be fly if there are indexes on MSISDN,LEVEL and OPP_DATE.

I should to go seaside to cooling my brain now.. ;)

Ali

P.S. Let's hope about i'm correct this time.

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