[rt-devel] SQL query performance

[Matthew D. Stock]

Now that our RT implementation is up into the thousands of tickets and
there are about 100,000 transactions in the database, I've started to run
into performance problems.  The biggest one was related to generating the
history list when displaying a ticket.  It was taking about 5 seconds per
transaction lookup, and so for long tickets, it was taking over a minute to
load the page.

It looks like the problem is that when the query is built by SearchBuilder,
it uses lower() on the indexed field (in this case, TransactionId), unless
CASESENSITIVE is enabled.  The lower(TransactionId) throws away the index
when doing the search.  By adding CASESENSITIVE, things have improved quite
a bit.

Here's a simple patch that shows specifically what I'm talking about:


diff -u -r1.1.2.100 Transaction.pm
--- Transaction.pm      2001/10/06 06:46:49     1.1.2.100
+++ Transaction.pm      2001/11/07 13:17:03
@@ -1,4 +1,4 @@
-# $Header: /raid/cvsroot/rt/lib/RT/Transaction.pm,v 1.1.2.100 2001/10/06 06:46:
49 jesse Exp $
+# $Header: /raid/cvsroot/rt/lib/RT/Attic/Transaction.pm,v 1.1.2.100 2001/10/06 
06:46:49 jesse Exp $
 # Copyright 1999-2001 Jesse Vincent <jesse at fsck.com>
 # Released under the terms of the GNU Public License
 
@@ -395,6 +395,7 @@
     }
     
     $Attachments->Limit(FIELD => 'TransactionId',
+                       CASESENSITIVE => 1,
                        VALUE => $self->Id);
 
     # Get the attachments in the order they're put into