Dear Tim,

I think the main problem was in my newbie Function writing.

I wrote a version:

CREATE FUNCTION "COCOBodWeek" ()
RETURNS INTEGER
BEGIN
DECLARE RESULT INTEGER DEFAULT 0;
DECLARE tmp CURSOR FOR stmt;

PREPARE stmt FROM

  ' SELECT ID, ROUND(CAST((Current_date - YearStartDate()) as INTEGER) / 7) as COCOBodWeek '
 +' FROM PrimaryEvacuations WHERE ID = 10';

OPEN tmp;
    
FETCH FIRST FROM tmp ('COCOBodWeek') INTO RESULT;

RETURN RESULT;
END

I chose this version purely because I assumed a SQL Function had to call some sort of SQL Statement to generate a result ... very beginner-ish behaviour sorry.

Roy suggested the version:

ALTER FUNCTION "CocoBODWeek" ()
RETURNS INTEGER
BEGIN
 DECLARE RESULT INTEGER DEFAULT 0;
 SET RESULT = ROUND((CAST(Current_date - YearStartDate() as INTEGER)+1) / 7 TO 0);
 RETURN RESULT;
END

... substituting this internal function for my database-calling function speeded the query up from 20+ seconds to <1 second. I guess that calling even a single row query for everyone of the 100,000 rows of my primary table would be bound to have that sort of impact.

Adam,

<< .. substituting this internal function for my database-calling function
speeded the query up from 20+ seconds to <1 second. I guess that calling
even a single row query for everyone of the 100,000 rows of my primary table
would be bound to have that sort of impact. >>

Nicely done.

--
Tim Young
Elevate Software
www.elevatesoft.com