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