[rt-users] Expensive Queries

[Jeff Stark]

Hello,
 
We are trying to identify very poor performance levels of the RT
Application 3.6.3 on REHL/Apache with Oracle.  In doing so, we had our
DBAs take a look at the Oracle box and the identified some very
expensive queries that were taking nearly 10 seconds to return, such as:

SELECT * FROM 
 ( SELECT limitquery.*, rownum limitrownum FROM 
            ( SELECT main.* FROM   
                        ( SELECT DISTINCT main.id 
                                    FROM CustomFields main 
                                    JOIN ObjectCustomFields
ObjectCustomFields_1  ON ( ObjectCustomFields_1.CustomField = main.id ) 
                                    WHERE (main.Name = 'Customer') 
                                    AND (ObjectCustomFields_1.ObjectId =
'0') 
                                    AND (main.LookupType =
'RT::Queue-RT::Ticket')  ) distinctquery, 
                        CustomFields main 
            WHERE (main.id = distinctquery.id)  
            ORDER BY main.SortOrder ASC, main.id ASC  ) 
 limitquery WHERE rownum <= 1 ) 
 WHERE limitrownum >= 1

Their concern is mainly around the joins being inequality joins, as even
indexes won't help with these types of queries.  Has anyone seen this
same behavior and/or has anyone done anything to modify the system to
generate better join clauses in the queries?  
 
Also I must note, we only have 450 tickets in the system at this
time...this was in just over a week with less than half of the users
active, so we expect the number to grow significantly as we onboard
additional users.
 
Thanks,
 
Jeff Stark

 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20070515/9f894e05/attachment.htm>