[rt-users] Large number of SELECT queries run when displaying ticket

[Roy El-Hames]

Justin;
Not sure if it'll help you ;
I had the same issue, where tickets with 100 or so updates/comments were 
taking over 1 minute to display ..
In our RT we do not use Transaction custom fields , so I commented out 
the following lines in ShowTransaction :

%# if ($Transaction->CustomFieldValues->Count) {
%#      <& /Elements/ShowCustomFields, Object => $Transaction &>
%#}
And now these same tickets open in less that 10 seconds ..
I think there is a bug in pulling transaction cf's, which until we start 
using them I wont need to worry about them.

Roy



Justin Hayes wrote:
> Using RT 3.6.3
> on Mysql 5.0.38
>
> Some of our larger tickets are taking a long time to display (>10 
> seconds) so I thought I'd have a look to see where the time is spent.
>
> I've added Set($StatementLog, 'crit'); to my config file to see what 
> queries are run and it seems like it runs a lot of queries just to 
> display 1 ticket and it's history.
>
> I dumped all the queries run to display a ticket with 100 
> comments/replies into a file 'just.out' and it's running approx 500 
> SELECT statements:
> rt at ceres:~/etc$ cat just.out | grep 'SELECT' | wc -l
> 491
>
> For example this query is run:
>
> Aug  3 11:16:02 ceres RT: SQL(0.00s):
> SELECT
> DISTINCT main.Id AS id,
> main.Filename AS filename,
> main.ContentType AS contenttype,
> main.Headers AS headers,
> main.Subject AS subject,
> main.Parent AS parent,
> main.ContentEncoding AS contentencoding,
> main.ContentType AS contenttype,
> main.TransactionId AS transactionid,
> main.Created AS created
> FROM
> Attachments main JOIN Transactions Transactions_1  ON ( 
> Transactions_1.id = main.TransactionId ) JOIN Tickets Tickets_2  ON ( 
> Tickets_2.id = Transactions_1.ObjectId )
> WHERE (Tickets_2.EffectiveId = '22526') AND (Transactions_1.ObjectType 
> = 'RT::Ticket')
> ORDER BY main.id ASC ; (/opt/rt3/share/html/autohandler:320)
>
> Which returns 100 rows and then it runs one select for each Transaction:
>
> Aug  3 11:16:02 ceres RT: SQL(0.00s):
> SELECT  * FROM Transactions WHERE id = ?;  [ bound values: '197857' ] 
> (/opt/rt3/share/html/autohandler:320)
>
> So you get 100 of those:
> rt at ceres:~/etc$ cat just.out | grep 'SELECT  \* FROM Transactions' | 
> wc -l
> 100
>
> Just wondering why it does all those 'Select *'s? Couldn't the fields 
> you want from Transactions be retrieved in the first query and iterate 
> over that in code?
>
> Also it seems to be doing a large number of CustomField related queries.
>
> rt at ceres:~/etc$ cat just.out | grep 'CustomField' | wc -l
> 236
>
> I've got 5 CustomFields at the ticket level. Why would it run 236 
> queries relating to CustomFields for 1 ticket?
>
> Maybe this is all normal, with perfectly good explanation. Or maybe my 
> install is broken or maybe some of our customisations are causing it.
>
> Any thoughts/opinions welcome...
>
> Justin
> ------------------------------------------------------
> Justin Hayes
> Support Manager
> justin.hayes at orbisuk.com
>
>
>
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> 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