db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Alan Burlison <Alan.Burli...@sun.com>
Subject Re: Derby triggers cause massive slowdown
Date Sun, 26 Oct 2008 20:08:04 GMT
Knut Anders Hatlen wrote:

> The update statements in the triggers don't have a WHERE clause, so each
> of them will update all rows in the table. Something like this should be
> much faster, provided that you have a user_id column with an index:
> 
> create trigger ut__it after insert on usersTrigger
>     referencing new as new
>     for each row update usersTrigger
>     set lc_user_name = lower(user_name) where user_id = new.user_id;
> 
> It'll still not be as fast as doing the lowercasing in Java, since the
> update statement in the trigger will navigate through the index instead
> of using the cursor directly, but it should perform reasonably well, I
> think.

I thought row triggers were restricted to operating on just the rows 
that were modified 
(http://db.apache.org/derby/docs/10.1/ref/rrefsqlj43125.html#rrefsqlj43125__sqlj54276), 
I take it from what you've said that is not the case.

I found the documentation rather unclear on the difference between row 
and statement triggers, and also on which rows would be affected by a 
trigger, thanks for the clarification.

Doing the lowercasing in Java is both trivial and fast, so that seems 
the best option.

-- 
Alan Burlison
--

Mime
View raw message