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 Derby triggers cause massive slowdown
Date Thu, 23 Oct 2008 18:16:38 GMT
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
--

Mime
View raw message