db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <Knut.Hat...@Sun.COM>
Subject Re: Derby triggers cause massive slowdown
Date Fri, 24 Oct 2008 09:20:59 GMT
Alan Burlison <Alan.Burlison@Sun.COM> writes:

> We have a table we are inserting rows into from inside a tight loop,
> committing every 100 rows.  We need to do case-insensitive string
> searches on some columns, so we have duplicated the relevant columns
> and are populating them via a trigger which does a lower() on the
> source column and inserts the value into the corresponding lowercase
> column:
>
> create trigger ut__it after insert on usersTrigger
>     for each row update usersTrigger
>     set lc_user_name = lower(user_name);
> create trigger ut__ut after update of user_name on usersTrigger
>     for each row update usersTrigger
>     set lc_user_name = lower(user_name);
>
> Without these triggers and doing the lowercase in Java instead, we can
> insert approx 100,000 rows a minute into the table.  If we enable the
> triggers the same process isn't completed even after several hours.
>
> Is this massive slowdown a known bug?

Hi Alan,

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.

-- 
Knut Anders

Mime
View raw message