<html><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; ">
<div>Using RT 3.6.3</div><div>on Mysql 5.0.38</div><div><br class="webkit-block-placeholder"></div><div>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.</div><div><br class="webkit-block-placeholder"></div><div>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.</div><div><br class="webkit-block-placeholder"></div><div>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:</div><div><div>rt@ceres:~/etc$ cat just.out | grep 'SELECT' | wc -l</div><div>491</div></div><div><br class="webkit-block-placeholder"></div><div>For example this query is run:</div><div><br class="webkit-block-placeholder"></div><div><div>Aug 3 11:16:02 ceres RT: SQL(0.00s):</div><div>SELECT </div><div>DISTINCT main.Id AS id, </div><div>main.Filename AS filename, </div><div>main.ContentType AS contenttype, </div><div>main.Headers AS headers, </div><div>main.Subject AS subject, </div><div>main.Parent AS parent, </div><div>main.ContentEncoding AS contentencoding, </div><div>main.ContentType AS contenttype, </div><div>main.TransactionId AS transactionid, </div><div>main.Created AS created </div><div>FROM </div><div>Attachments main JOIN Transactions Transactions_1 ON ( Transactions_1.id = main.TransactionId ) JOIN Tickets Tickets_2 ON ( Tickets_2.id = Transactions_1.ObjectId ) </div><div>WHERE (Tickets_2.EffectiveId = '22526') AND (Transactions_1.ObjectType = 'RT::Ticket') </div><div>ORDER BY main.id ASC ; (/opt/rt3/share/html/autohandler:320)</div><div><br class="webkit-block-placeholder"></div><div>Which returns 100 rows and then it runs one select for each Transaction:</div></div><div><div><br class="webkit-block-placeholder"></div><div>Aug 3 11:16:02 ceres RT: SQL(0.00s): </div><div>SELECT * FROM Transactions WHERE id = ?; [ bound values: '197857' ] (/opt/rt3/share/html/autohandler:320) </div><div><br class="webkit-block-placeholder"></div><div>So you get 100 of those:</div></div><div>rt@ceres:~/etc$ cat just.out | grep 'SELECT \* FROM Transactions' | wc -l</div><div>100</div><div><br></div><div>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?</div><div><br class="webkit-block-placeholder"></div><div>Also it seems to be doing a large number of CustomField related queries. </div><br><div>rt@ceres:~/etc$ cat just.out | grep 'CustomField' | wc -l</div><div>236</div><div><br></div><div>I've got 5 CustomFields at the ticket level. Why would it run 236 queries relating to CustomFields for 1 ticket?</div><div><br class="webkit-block-placeholder"></div><div>Maybe this is all normal, with perfectly good explanation. Or maybe my install is broken or maybe some of our customisations are causing it.</div><div><br class="webkit-block-placeholder"></div><div>Any thoughts/opinions welcome...</div><div><br class="webkit-block-placeholder"></div><div>Justin</div><div> <span class="Apple-style-span" style="border-collapse: separate; color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-align: auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0; "><div><div style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; "><div>------------------------------------------------------</div><div>Justin Hayes</div><div>Support Manager</div><div><a href="mailto:justin.hayes@orbisuk.com">justin.hayes@orbisuk.com</a></div><div><br class="khtml-block-placeholder"></div></div><br></div></span> </div><br></body></html>