A proper index dramatically helps locating information. Examine the
generated plan for your query, maybe it can be improved. Think how
you would order your info if you did the calculation by hand. Just
play, adding / droping indexes and shy away from indexes with too low
a selectivity. There is always the faster hardware and more memory
solution :)

Best Regards
Hans

====================================================================

Richard Saeger wrote:

>
> Hans,
>
> thanks for your reply. I'd say, your procedure must be faster, but:
>
> execution time your proc: 46,0274 sec
> execution time my proc : 32,0028 sec
>
> I can't believe it!
>
> BTW, I haven't got any reply to my previous question: do I need
> compound index, when I do
> SELECT WHERE Field1 = something AND Field2 = something?
>
> Who knows it, please?
>
> Regards
> Richard
>
> --- In ib-support@y..., hans@h... wrote:
> > Maybe this goes faster ?
> >
> > CREATE PROCEDURE MonthTable ( TheYear SMALLINT, Dept_No SMALLINT )
> > RETURNS ( Emp_No INTEGER, Name VARCHAR(50), M01 DECIMAL (9,2),
> > M02 DECIMAL (9,2), M03 DECIMAL (9,2),
> > M04 DECIMAL (9,2), M05 DECIMAL (9,2),
> > M06 DECIMAL (9,2), M07 DECIMAL (9,2),
> > M08 DECIMAL (9,2), M09 DECIMAL (9,2),
> > M10 DECIMAL (9,2), M11 DECIMAL (9,2),
> > M12 DECIMAL (9,2) )
> > AS
> > declare variable myEmp_No integer;
> > BEGIN
> > for select distinct Emp_No from Pers
> > where
> > order by Emp_no
> > into :myEmp_No
> > do
> > begin
> > mo1 = 0;
> > mo2 = 0;
> > ....
> > mo12 = 0;
> >
> > FOR SELECT Emp_No, NAME1 || ' ' || NAME2, Work_Hours
> > FROM Pers
> > WHERE Dept_No = :Dept_No
> >
> > order by Emp_No,Work_Day
> > where and Emp_No = myEmp_No
> > and extract(year of Work_Day) = :TheYear // prob want year
> limit
> >
> > INTO :Emp_No, :Name DO
> >
> > BEGIN
> > myMonth = extract(month of Work_Day);
> > if (MyMonth = 1) then
> > Mo1 = Mo1 + Work_Hours;
> > else
> > Mo2 = Mo2 + Work_Hours;
> > else
> > ....
> >
> > else
> > Mo12 = ....
> >
> > end
> > suspend;
> > end
> > end
> >
> > Hans
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/