[rt-users] extremely slow rt response, query hanging

[Joop van de Wege]

On Thu,  8 Dec 2005 12:00:05 -0500 (EST)
rt-users-request at lists.bestpractical.com wrote:


> At Wednesday 12/7/2005 03:54 AM, Harald Wagener wrote:
> >Hello,
> >One of our users constructed a search on our RT 3.2.2 instance which
> >is slowing the system down quite a lot:
> >
> >SELECT COUNT(DISTINCT main.id) FROM Tickets main , Transactions
> >Transactions_1, Attachments Attachments_2  WHERE ((main.EffectiveId =
> >main.id)) AND ((main.Status != 'deleted')) AND ((main.Type =
> >'ticket')) AND ((main.Subject LIKE '%Performance%')OR
> >( (Attachments_2.Content LIKE '%Performance%')AND 
> >(Attachments_2.TransactionId = Transactions_1.id)AND(main.id =
> >Transactions_1.Ticket) ) )
> 
> For what it's worth, we've experienced this when searching on Subject & 
> Content together (RT 3.4.2, Oracle 9). Either a subject search or a content 
> search is OK, but not both together. I haven't had time to profile the 
> query, so I can't shed any light on a solution.

I just had a look at our Oracle9 instance with our production RT on it
and tried to run that query but it contains atleast one mistake. Could
be the fact that we're running RT-3.4.3 and not 3.2.2
So this is the query that is run on our instance when asking for subject
like 'structuur' and content matching 'betrekking'.

SELECT COUNT (DISTINCT main.ID)
  FROM tickets main, transactions transactions_1, attachments attachments_2
 WHERE ((transactions_1.objecttype = 'RT::Ticket'))
   AND ((main.effectiveid = main.ID))
   AND ((main.status != 'deleted'))
   AND ((main.TYPE = 'ticket'))
   AND (    (LOWER (main.subject) LIKE '%structuur%')
        AND (    (contains (attachments_2.content, 'betrekking') > 0)
             AND (attachments_2.transactionid = transactions_1.ID)
             AND (main.ID = transactions_1.objectid)
            )
       )


There are a couple of things to be noted:
- we modify the query if it searches for content (contains clause)
- there is an appropriate index for this.
- we must explicitly use wildcards if we want to use them (SB does the
reverse)

Execution time of this query is 350ms ;-)
What would kill its performance is using wildcards at the front of the
search string ('%betrekking%'), killing its index. This will be slow for
any database relying on indices.
See the following example from our dbiprof output:
#####[ 1 ]###########################################################
  Count         : 15
  Total Time    : 991.093903 seconds
  Longest Time  : 859.530297 seconds
  Shortest Time : 0.000017 seconds
  Average Time  : 66.072927 seconds
  Key 1         :

/* Formatted on 2005/12/09 10:57 (Formatter Plus v4.8.7) */
SELECT COUNT (DISTINCT main.ID)
  FROM tickets main, transactions transactions_1, attachments attachments_2
 WHERE ((transactions_1.objecttype = 'RT::Ticket'))
   AND ((main.effectiveid = main.ID))
   AND ((main.status != 'deleted'))
   AND ((main.TYPE = 'ticket'))
   AND (    (main.queue = '27')
        AND (    (contains (attachments_2.content, '%fixed%') > 0)
             AND (attachments_2.transactionid = transactions_1.ID)
             AND (main.ID = transactions_1.objectid)
            )
       )
 
And the corresponding entry from the first query:
#####[ 37 ]###########################################################
  Count         : 15
  Total Time    : 2.809959 seconds
  Longest Time  : 2.155377 seconds
  Shortest Time : 0.000011 seconds
  Average Time  : 0.187331 seconds
  Key 1         :
 
Hope this will help you Steve and maybe other users using Oracle with
problems searching on Content.

Joop

---
Joop van de Wege <JoopvandeWege at mococo.nl>