I am looking for a mysql performance ninja.  

Situation: we have a service desk queue with a dropdown for quick close, which runs hours.  When you select quick close a custom service desk ticket rule will run and close out a ticket for you and basically market it is irrelevant; we use this for server status tickets.  

Problem: the query takes a long time to run.  For example, ran on 36 hours and it took 10 minutes.  

Can someone make suggestions to improve the cost of these queries:

Select Query
_________
select
                HD_TICKET.*,
                owner.EMAIL as OWNER_EMAIL,
                if ((LENGTH(owner.FULL_NAME) = 0), owner.USER_NAME,
owner.FULL_NAME) as OWNER_NAME,
                submitter.EMAIL as SUBMITTER_EMAIL
from
                HD_TICKET
                left outer join USER owner on owner.ID = HD_TICKET.OWNER_ID
                left outer join USER submitter on submitter.ID =
HD_TICKET.SUBMITTER_ID
where
LENGTH(HD_TICKET.CUSTOM_FIELD_VALUE9) > 0
_________

Update Query:
_______________
update HD_TICKET, HD_CATEGORY as T5, HD_STATUS as T6, USER as T8
    set HD_TICKET.HD_CATEGORY_ID = T5.ID, 
HD_TICKET.HD_STATUS_ID = '55', 
HD_TICKET.HD_CATEGORY_ID = '116', 
HD_TICKET.TIME_OPENED  = IF(T6.STATE = 'opened', NOW(), HD_TICKET.TIME_OPENED), 
HD_TICKET.TIME_CLOSED  = IF(T6.STATE = 'closed', NOW(), HD_TICKET.TIME_CLOSED), 
HD_TICKET.TIME_STALLED = IF(T6.STATE = 'stalled', NOW(), HD_TICKET.TIME_STALLED), 
HD_TICKET.CUSTOM_FIELD_VALUE3 = 'Information Only', 
HD_TICKET.CUSTOM_FIELD_VALUE0 = 'IT DEPT', 
HD_TICKET.CUSTOM_FIELD_VALUE1 = 'Main Building', 
HD_TICKET.SUBMITTER_ID='10',
HD_TICKET.OWNER_ID = 'TC.USER_ID',
HD_TICKET.CUSTOM_FIELD_VALUE9 = ''
  where 
HD_TICKET.HD_QUEUE_ID = T5.HD_QUEUE_ID and 
   (HD_TICKET.ID in (<TICKET_IDS>))

________________


3 Comments   [ + ] Show Comments

Comments

  • Correction to the above, ran on 36 records and took about 10 minutes
  • Do you have any idea which part is taking more time to run, the select or the update?
    • I ran the select query via MySQL WorkBench and it executed in less than one second, so I suspect it is the update query. In addition to the update query, I also add a comment for owners only to indicate the rule ran on the ticket. Does that make a difference?
  • I'm assuming the server monitoring app is sending an email to the queue based on specific events. Solarwinds allows us to, based on the event, send an email message to the queue with the specific @commands needed to populate fields. Solarwinds has been setup up as a user account in the kbox.

    Selecting * along with OWNER and SUBMITTER is redundant unless you're sending an email to each of those participants.

    OUTER JOINS are slower than all others.

    LENGTH, in relation to the line you have in the select query isn't needed.

    The select query should look for tickets in the specific queue the tickets are in, as in WHERE HD_TICKET.HD_QUEUE_ID=5. This select/update is forcing the kbox to look at every ticket in the system.

    I'm not sure I understand why you're setting the submitter and the owner of the ticket via the update statement.

    Your UPDATE statement is locking four tables when it runs. If there are other queues in action it must wait for any other ticket rules to complete prior to running.

    A better approach would be to find a way to populate the ticket fields prior to selecting the Quick Close so that the only field being updated is the STATUS.
Please log in to comment

There are no answers at this time

Answers

Answer this question or Comment on this question for clarity

Share