--- In firebird-support@yahoogroups.com, bill_lam <bill_lam@...> wrote:

>
> Adam wrote:
>
> create procedure copythis
> as
> begin
> for
> select q, y
> from b
> into :q, :y
> do
> begin
> update a set p = :q where x = :y;
> end
> end
>
>
> if there are 100 rows selected by query inside for .... do,
> will the statement " update a set p = :q where x = :y; " be
> iterated for 100 times or executed only once?
>
> regards,
> bill
>

Hi Bill,

I will try and answer a few questions that are spread around different
branches of this thread in one go.

1) {Assuming B has 100 rows, will the update clause in the SP) be
iterated for 100 times or executed only once?

The update clause will be executed once for each record of B. The
pseudo code for the sample procedure I posted (which I haven't tested
btw but should work) is as follows:

For each record in B do
begin
update corresponding records in A
end

There are a couple of things to note here. First and foremost,
assuming a.X is indexed, the update clause will be extremely quick.
Not only that, but once the update is run for the first record of B,
there is an extremely high chance that the index on a.X is cached,
meaning for record 2 onwards, it will be even faster.

The SP is also run on the server by the engine, so there is no network
or IPC delays. I would expect the code in the stored procedure to
outperform the same logic from a client application running the
selects then sending out updates by a significant margin.

The SP can also be significantly improved by adding another condition
to the where.

update a set p = :q where x = :y and p <> :q

2) Which method, this and the SP suggested by Adam, is faster? I meant
execution speed for larger table.

See Set's response. Unless B is larger than A, the SP should be
quicker because the query must query B multiple times (see 3). It is
also unlikely in my experience to have B larger than A; having more
customers than orders could never happen because for someone to be
considered a customer they would need to place an order).

3) Is also possible to use this (non SP) syntax if more than 1 column
need changes.

It is 'possible', but generally not a good idea. The update syntax
requires for each record of A

* A read on B to make sure there is at least 1 record
* A read on B for every field to be changed.

So the number of (hopefully indexed) reads on B

= A * ([fields to change] + 1)

If you had 1000 records in A and wanted to change 3 fields, you would
be doing 4000 reads of B.

On the other hand, the stored procedure starts looking really
attractive, in this case. The initial select can load into variables
all the values you like, and your update can then be spoon-fed these
values.

It would require a read of the entire B table, then need [number of
records in B] updates (hopefully indexed) to A.

4) Moving back to your original admission that the cause of all this
question is that the structure is not normalised, have you considered
writing a view that looks like your non-normalised table, but behind
the scenes you have a proper relational structure?

Whether this is appropriate may depend on performance, certainly
updates to b become much less expensive, although selects will be joining.

5) If you absolutely must duplicate the data to A when it is changed
in B, rather than performing the logic in an update or sp, a trigger
on B can do this.

SET TERM ^ ;
CREATE OR ALTER TRIGGER B_AU FOR B
ACTIVE AFTER UPDATE POSITION 0
AS
BEGIN
update a set p = NEW.q where x = NEW.y and p <> NEW.q;
END
^
SET TERM ; ^
COMMIT WORK;

Problem:

With the approach you have taken to duplicate the information to A,
you get yourself into a bind where another transaction working in a
record in A prevents you from making a change to B. (This is true for
the update, the sp, and for the trigger).

In a real world scenario, assume I duplicated the customer name to an
orders table by one of the possible methods. The customer now phones
to order some items. During the conversation, the customer announces
that they have changed owners and have a new trading name. You try to
modify their address, but you get a lock conflict because updating the
customer name has to change the name against each order. Because in a
different transaction you are modifying one of the orders, the
operation of changing a customer name cannot succeed. Whilst short
transactions may reduce the frequency, it is not a problem one should
pretend never exists.

(In a normalised structure, that sort of problem can not happen.)

Adam