[rt-users] Expensive Queries

[Kenneth Marshall]

Jeff,

It sounds like you may need to make some more indexes. Please
have your DBAs provide a plan for the query execution. Look
for sequential scans in particular. That may help you identify
possible index creation options. We use PostgreSQL here, but
I would suspect that many of the index creations needed for it
to be performant would be the same with Oracle. There are a
couple of posts about PostgreSQL tuning in the mailing list
that you can refer too. Good luck.

Ken

On Tue, May 15, 2007 at 09:37:11AM -0700, Jeff Stark wrote:
> 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
> 
>  

> _______________________________________________
> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
> 
> Community help: http://wiki.bestpractical.com
> Commercial support: sales at bestpractical.com
> 
> 
> Discover RT's hidden secrets with RT Essentials from O'Reilly Media. 
> Buy a copy at http://rtbook.bestpractical.com