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,
