Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

In this Discussion

osTicket v1.10 (stable) and Maintenance Release v1.9.15 are now available! Go get it now

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 load

Platform with osTicket v1.9.2 installed
is  on a  VMWARE Server 2 VM

O/S
>  Slackware Linux 14.1
> Linux mon214 3.10.17 #2 SMP Wed Oct 23 16:34:38 CDT 2013 x86_64 Intel(R) Xeon(R) CPU           E5606  @ 2.13GHz GenuineIntel GNU/Linux

PHP
> root@mon214:/var/www/htdocs/osTicket/include/sv0# php -v
> PHP 5.4.20 (cli) (built: Oct 11 2013 17:50:38)
> Copyright (c) 1997-2013 The PHP Group
> Zend Engine v2.4.0, Copyright (c) 1998-2013 Zend Technologies

Apache Web Server
> root@mon214:/var/www/htdocs/osTicket/include/sv0# httpd -v
> Server version: Apache/2.4.6 (Unix)
> Server built:   Aug  5 2013 16:32:54


This below change to include/class.thread.php
allows 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());




Comments

  • Additional Info

    mysql
    root@mon214:/var/www/htdocs/osTicket/include# mysql -V
    mysql  Ver 15.1 Distrib 5.5.32-MariaDB, for Linux (x86_64) using readline 5.1
  • @ntozier
    Maybe that's interesting for the devs?
  • edited August 2014
    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?
  • @rshep2 what @chefkeks 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 ;)
  • edited August 2014
    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.
Sign In or Register to comment.