Derby triggers cause massive slowdown

[Alan Burlison <Al...@sun.com>]

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?

Thanks,

-- 
Alan Burlison
--

Re: Derby triggers cause massive slowdown

[Alan Burlison <Al...@sun.com>]

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
--

Re: Derby triggers cause massive slowdown

[Knut Anders Hatlen <Kn...@Sun.COM>]

Alan Burlison <Al...@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

Re: Derby triggers cause massive slowdown

[Rick Hillegas <Ri...@Sun.COM>]

Hi Alan,

This will not help you today, but the next feature release, 10.5, should 
give you Generated Columns (DERBY-481). This will give you the 
performance which you see when you don't use triggers but normalize your 
strings in Java code. You would declare and index your table like this:

create table ut__ut
(
   user_name varchar( 100 ),
   lc_user_name generated always( lower( user_name ) )
);
create index lc_ut on ut__ut( lc_user_name );

You may want to take this functionality for a test-drive in a month--by 
then I am hoping that we will have checked-in enough code that you can 
run some experiments.

Regards,
-Rick

Alan Burlison wrote:
> 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?
>
> Thanks,
>