--- In ib-support@y..., Svein Erling Tysvær
<svein.erling.tysvaer@k...> wrote:

> >>Do you have an index on (Work_Day, Emp_No)?
> >Yes, there are this indexes.
>
> These indexes?? I was thinking of one combined index, i.e.
>
> CREATE INDEX DayEmployee ON Pers(Work_Day, Emp_No)

What??? In this case I misunderstand the role of indexes in the
optimisation. If I have 2 Fields: F1 and F2, and I need a query:
SELECT * FROM MyTable WHERE F1 = 'X' and F2 = 'Y', do I need a
compound index on (F1, F2)? Till now, I thought I need an index on F1
_and_ an index on F2. Consider F1 is the primary key, then I have to
duplicate the key in the compound index?

PLEASE, can anybody confirm this? Am I on the wrong way?

Regardless of this, in my case the primary key of WorkTime is
(Work_Day, Emp_No). So I have the index you suggest.

> Don't know how much better it will be, but my guess is that it

could help

> quite a bit.

Sure.

> >Yes, it is right. EOM returns the End Of Month date. I've found,

that

> >WHERE DateField BETWEEN Date1 AND Date2 uses indexes, but EXTRACT
> >(MONTH FROM DateField) doesn't.
>
> Absolutely right.
>
> >IMHO the problem isn't the call of EOM procedure, the problem is
> >twelvetimes SELECT SUM(... for every employee in the main-query,

100

> >employees 1200 x SELECT SUM(... :-(
>
> Sounds right, but every little bit could help :.) So try changing

your

> procedure to something like the procedure below and tell us your

results (I

> think the combined index will make the greatest difference, but

don't know

> until you report back).
>
> HTH,
> Set

I've tried it, but sorry, it's the same. The procedure EOM does only
this:

CREATE PROCEDURE EOM(AJahr INTEGER, AMonat INTEGER)
RETURNS (MaxDatum DATE)
AS
DECLARE VARIABLE MaxTage INTEGER;
BEGIN
MaxTage = 31;
MaxDatum = NULL;
WHILE (MaxTage >= 28 AND MaxDatum IS NULL) DO
BEGIN
EXECUTE PROCEDURE EncodeDate(AJahr, AMonat, MaxTage)
RETURNING_VALUES(MaxDatum);
WHEN ANY DO MaxTage = MaxTage - 1;
END
END #

Regards
Richard