• Suggestions and Feedback

  • Fix for v1.9.2 where a ticket with large number of threads, which was taking several minutes to load

I had a performance issue on osTicket v1.9.2, with a ticket having  292 threads and quite a lot of attachments, it was taking several minutes to loadPlatform with osTicket v1.9.2 installedis  on a  VMWARE Server 2 VMO/S>  Slackware Linux 14.1> Linux mon214 3.10.17 #2 SMP Wed Oct 23 16 CDT 2013 x86_64 Intel(R) Xeon(R) CPU           E5606  @ 2.13GHz GenuineIntel GNU/LinuxPHP> root@mon214:/var/www/htdocs/osTicket/include/sv0# php -v> PHP 5.4.20 (cli) (built: Oct 11 2013 17)> Copyright (c) 1997-2013 The PHP Group> Zend Engine v2.4.0, Copyright (c) 1998-2013 Zend TechnologiesApache Web Server> root@mon214:/var/www/htdocs/osTicket/include/sv0# httpd -v> Server version: Apache/2.4.6 (Unix)> Server built:   Aug  5 2013 16This below change to include/class.thread.phpallows the big ticket to load et within a couple of seconds...root@mon214:/var/www/htdocs/osTicket/include/sv0 diff class.thread.php ..39a40,61> /*> **        $sql='SELECT ticket.ticket_id as id '> **            .' ,count(DISTINCT attach.attach_id) as attachments '> **            .' ,count(DISTINCT message.id) as messages '> **            .' ,count(DISTINCT response.id) as responses '> **            .' ,count(DISTINCT note.id) as notes '> **            .' FROM '.TICKET_TABLE.' ticket '> **            .' LEFT JOIN '.TICKET_ATTACHMENT_TABLE.' attach ON ('> **                .'ticket.ticket_id=attach.ticket_id) '> **            .' LEFT JOIN '.TICKET_THREAD_TABLE.' message ON ('> **                ."ticket.ticket_id=message.ticket_id AND message.thread_type =>  'M') "> **            .' LEFT JOIN '.TICKET_THREAD_TABLE.' response ON ('> **                ."ticket.ticket_id=response.ticket_id AND response.thread_type>  = 'R') "> **            .' LEFT JOIN '.TICKET_THREAD_TABLE.' note ON ( '> **                ."ticket.ticket_id=note.ticket_id AND note.thread_type = 'N')> "> **            .' WHERE ticket.ticket_id='.db_input($this->getTicketId())> **            .' GROUP BY ticket.ticket_id';> */>41,44c63,66<             .' ,count(DISTINCT attach.attach_id) as attachments '<             .' ,count(DISTINCT message.id) as messages '<             .' ,count(DISTINCT response.id) as responses '<             .' ,count(DISTINCT note.id) as notes '--->             .' ,(select count(*) from '.TICKET_ATTACHMENT_TABLE.' attach where attach.ticket_id='.db_input($this->getTicketId()).') as attachments '>             .' ,(select count(*) from '.TICKET_THREAD_TABLE.' thread where thread.thread_type = "M" and thread.ticket_id='.db_input($this->getTicketId()).') as messages '>             .' ,(select count(*) from '.TICKET_THREAD_TABLE.' thread where thread.thread_type = "R" and thread.ticket_id='.db_input($this->getTicketId()).') as responses '>             .' ,(select count(*) from '.TICKET_THREAD_TABLE.' thread where thread.thread_type = "N" and thread.ticket_id='.db_input($this->getTicketId()).') as notes '46,55c68<             .' LEFT JOIN '.TICKET_ATTACHMENT_TABLE.' attach ON ('<                 .'ticket.ticket_id=attach.ticket_id) '<             .' LEFT JOIN '.TICKET_THREAD_TABLE.' message ON ('<                 ."ticket.ticket_id=message.ticket_id AND message.thread_type = 'M') "<             .' LEFT JOIN '.TICKET_THREAD_TABLE.' response ON ('<                 ."ticket.ticket_id=response.ticket_id AND response.thread_type = 'R') "<             .' LEFT JOIN '.TICKET_THREAD_TABLE.' note ON ( '<                 ."ticket.ticket_id=note.ticket_id AND note.thread_type = 'N') "<             .' WHERE ticket.ticket_id='.db_input($this->getTicketId())<             .' GROUP BY ticket.ticket_id';--->             .' WHERE ticket.ticket_id='.db_input($this->getTicketId());

Additional Infomysqlroot@mon214:/var/www/htdocs/osTicket/include# mysql -Vmysql  Ver 15.1 Distrib 5.5.32-MariaDB, for Linux (x86_64) using readline 5.1

@[deleted]

Maybe that's interesting for the devs?

this is feed back to osTicket, about performasce issues, and provides  a possible way to fix it, it is up to osTicket to handle internally how to handle it.What do you mean by your comment?

Is this not appropriate  for the feedback forum?

@[deleted] what @[deleted] is saying is he thinks that I should make sure that the Devs see this thread.I've included it in my daily report to them.

Exactly what I meant. Such improvements are interesting for the developers to make osTicket faster, more reliable and secure.

And yes the forum here or an issue/pull request is an appropriate place for such feedback ;)

Thanks you for that, I,m not a php programmer but I know a bit about SQL, and  after I was having trouble waiting minutes for a ticket to load, I went looking for the culptit, and this SQL looked as it it might be the cause.

Write a Reply...