I have a fairly simple statement:

ALTER TABLE StockPurchaseItems
ADD COLUMN UnitWeight FLOAT GENERATED ALWAYS AS ProductWeight(ProductsID) AT 5!

The StockPurchaseItems table has about 100,000 rows.
The "ProductWeight" function is really fast, it runs in about 0.01 seconds within EDB Manager.

However when I run this update script it takes 2 or 3 hours ...

My assumption is that EDB is having to do something like re-create an index or something after every row alteration, and this is hugely boosting the time the update takes.

Can anyone give me advice on how I might make the ALTER statement run faster?

Adam


Unless you need the data to be physically stored would a computed column be faster - it would be slower in use since its much the same as a oncalcfields expression

Can you share the code for the function.

I do have an idea at the back of my mind but I'd like to check something out when I see what you're doing.

Roy Lambert

Roy

>>Unless you need the data to be physically stored
>>would a computed column be faster

Can't used "COMPUTED" with a function, you have to use GENERATED

When a ROW update happens it is really fast. What is hammering me is the fact that ALTERing the table is taking hours.

The ALTER statement MUST be triggering a bunch of other stuff.

I am hoping I can avoid or switch this off somehow ...

--

>>Can you share the code for the function.

It is very simple:

CREATE FUNCTION "ProductWeight" (IN "aProductsID" INTEGER)
RETURNS FLOAT
BEGIN
 DECLARE Crsr CURSOR FOR Stmt;
 DECLARE Result FLOAT;
PREPARE Stmt FROM
' SELECT
   UnitWeight
 FROM Products
 WHERE ID = ? ';
OPEN Crsr USING aProductsID;
FETCH FIRST FROM Crsr('UnitWeight') INTO RESULT;
RETURN Result;
END

>>I do have an idea at the back of my mind but I'd
>>like to check something out when I see what you're doing.

OH: And thanks for the feedback Roy! Great to know someone is listening.

I have realized that the table is PUBLISHED. It also has links to other tables, and triggers ... so I am guessing that I ought to probably switch off the triggers and publishing during the ALTER, then refresh triggered values afterwards with a single statement.

If I find that this helps I might write it up so it helps someone else not waste a week in the future!

Adam

>Can't used "COMPUTED" with a function, you have to use GENERATED

Rats, I'd forgotten that

>When a ROW update happens it is really fast. What is hammering me is the fact that ALTERing the table is taking hours.

Just taking your 0.01 and 100k rows that would work out to 16 minutes so we're talking 8 - 12 times as long

>The ALTER statement MUST be triggering a bunch of other stuff.

Do you have any triggers on the table? If so disable them for the table update. Code I use is below my sig. Is the column used in any indices (if so you're mad for putting a FLOAT into an index and deserve anything that happens to you <vbg>). Are other columns computed/generated using UnitWeight? If so that could well be the problem.

If the function had been in an external module it could have been linked to CacheModules.

I thought   DECLARE Crsr CURSOR FOR Stmt; might produce am insensitive result set and it could be a load of disk writes but according to the manual the default is ASENSITIVE which means it should produce a sensitive result set. It might be worth explicitly telling it   DECLARE Crsr SENSITIVE CURSOR FOR Stmt; and see if that makes a difference

The only thing I have left in my mind is that the statement is being prepared each time. As a test could you replace it with

CREATE FUNCTION "ProductWeight" (IN "aProductsID" INTEGER)
RETURNS FLOAT
DECLARE Result FLOAT;
SET Result = 1.2;
RETURN Result;
END

and just see what happens.

Sorry I can't give anything definitive

Roy

procedure UnsetTriggers(const DBase: TEDBDatabase; const TableName: string; Selector: string = '');
var
TriggerList: TEDBQuery;
begin
TriggerList := MakeEDBQuery(DBase);
TriggerList.SQL.Text := 'SELECT Name FROM Information.Triggers WHERE TableName = ' + QuotedStr(TableName);
if Selector <> '' then TriggerList.SQL.Add(' AND ' + Selector);
TriggerList.ExecSQL;
while not TriggerList.Eof do begin
 DBase.Execute('DISABLE TRIGGER "' + TriggerList.FieldByName(F_Name).AsString + '" ON "' + TableName + '"');
 TriggerList.Next;
end;
TriggerList.Free;
end;

procedure ResetTriggers(const DBase: TEDBDatabase; const TableName: string);
var
TriggerList: TEDBQuery;
begin
TriggerList := MakeEDBQuery(DBase);
TriggerList.SQL.Text := 'SELECT Name FROM Information.Triggers WHERE TableName = ' + QuotedStr(TableName);
TriggerList.ExecSQL;
while not TriggerList.Eof do begin
 DBase.Execute('ENABLE TRIGGER "' + TriggerList.FieldByName(F_Name).AsString + '" ON "' + TableName + '"');
 TriggerList.Next;
end;
TriggerList.Free;
end;

Roy

Thanks!

>Can't used "COMPUTED" with a function, you have to use GENERATED
>>Rats, I'd forgotten that



>>Do you have any triggers on the table? If so disable them for
>>the table update. Code I use is below my sig.

Thanks for this too, I will try it.

>>Is the column used in any indices (if so you're mad
>>for putting a FLOAT into an index and deserve anything
>>that happens to you <vbg>).

No!


>>Are other columns computed/generated using UnitWeight?
>>If so that could well be the problem.

There are other computations .... I'm sure this doesn't help, but I can't see how it has the severe impact I am observing.

>>If the function had been in an external module it could have been linked to CacheModules.

No, but v. good call.

>>The only thing I have left in my mind is that the statement is
>>being prepared each time. As a test could you replace it with

>>CREATE FUNCTION "ProductWeight" (IN "aProductsID" INTEGER)
>>RETURNS FLOAT
>>DECLARE Result FLOAT;
>>SET Result = 1.2;
>>RETURN Result;
>>END

I will try this and report back ...

One of the strangest things I am finding is how difficult it is to predict the time an ALTER will take.

Some apparently similar changes take seconds while others take hours.

It must relate to consequences, such as the presence of Triggers, Published Updates, Indices, or other computed / generated fields. Horribly difficult to figure out which are the worse offenders.

Adam


>One of the strangest things I am finding is how difficult it is to predict the time an ALTER will take.
>
>Some apparently similar changes take seconds while others take hours.
>
>It must relate to consequences, such as the presence of Triggers, Published Updates, Indices, or other computed / generated fields. Horribly difficult to figure out which are the worse offenders.

and don't forget buffering and caching, both ElevateDB's and Window's (and network's), and any other workload that's going on.

Roy

Adam

Missed this line - sorry

>There are other computations .... I'm sure this doesn't help, but I can't see how it has the severe impact I am observing.

This can well have an impact depending on what they are - the ultimate would be if the cnages resulted in 100k recalculations which in turn spawned other calculations.....

Roy