Many thanks...still more questions:

The original query had this condition

AND OC.MSISDN LIKE '6________'

and you have replaced it with

AND OC.MSISDN STARTING WITH '6'
AND substring(OC.MSISDN,9,1)<>''
AND substring(OC.MSISDN,10,1)=''

The query performs better with the conditions the way you have coded
them?

I think the way you have coded the query, it doesn't return the same
results, because max INTERACT_ID for a particular MSISDN could not be
LEVEL 1. I want to count rows for a MSISDN if they are the max
interaction, but only if the last is LEVEL 1. For example

INTERACT_ID MSISDN LEVEL
1 600000000 1
2 600000000 0

Your query will count 1, because 1 is the max INTERACT_ID for MSISDN
600000000 with LEVEL 1, but there is an greater INTERACT_ID for that
MSISDN, altough is LEVEL 0, and in this case the count should be 0.
Sorry if I didn't explain it crearly before.

The last question is about indexes. You say it will perform better if
there are indexes on LEVEL, DATE and MSISDN. I have an index on
MSISDN (composite index with primary key to avoid duplicates). I have
not created an index on LEVEL because it is a boolean field. DATE can
only have 90 values for a table of 12 millions, very low selectivity,
so I have not created an index. Should I reconsider creating index in
these fields?


Many thanks again

Diego

--- In firebird-support@yahoogroups.com, Ali Gökçen <alig@e...> wrote:
> 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]