db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: Derby triggers cause massive slowdown
Date Thu, 23 Oct 2008 18:36:34 GMT
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,
>


Mime
View raw message