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

[Justin Hayes]

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



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