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

[Justin Hayes]

Many thanks for that Roy. I applied the fix and it appears to have  
reduced the number of SELECTS:

Before:
rt at ceres:~/etc$ tail -f /var/log/syslog | grep SELECT >> just.out
rt at ceres:~/etc$ cat just.out | grep 'SELECT' | wc -l
593

After:
rt at ceres:~/etc$ rm just.out
rt at ceres:~/etc$ tail -f /var/log/syslog | grep SELECT >> just.out
rt at ceres:~/etc$ cat just.out | grep 'SELECT' | wc -l
364

Doesn't seem to be displaying any faster though, but fewer queries  
must be a good thing.

Interestingly I've noticed some tickets with only 8 comments on that  
are also taking over 10 seconds. Then I'll look at the same ticket a  
bit later and it will only take 1 second to display.

More than likely there's something else going on besides the sheer  
number of queries, but RT does seem to run an awful lot of them at  
times.

Justin

------------------------------------------------------
Justin Hayes
Support Manager
justin.hayes at orbisuk.com



On 3 Aug 2007, at 12:04, Roy El-Hames wrote:

> 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
>
> _______________________________________________
> 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

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