Optimization is challenging, no matter what you are doing, and unfortunately, reporting in Kayako Classic is no exception. How to make the process of writing, rendering, and processing reports most effective? Today, these questions concern a majority of the users working with reports in Kayako.
Optimizing KQL (Kayako Query Language) reports is a set of actions aimed at improving the speed of report rendering and reducing memory usage. To optimize your report performance, you need to know the right tools, and how to focus on the target result set which you want to see.
Understanding How Reports Work
With the KQL reports, it is essential to know that it fetches data directly from the database; thus, it uses MySQL and server resources to fetch any results. So, the only way to make a query run faster is to reduce the number of calculations that the software (and, therefore, resources) must perform. The optimization here lies in two directions: increasing the speed of report rendering and reducing memory usage.
Optimizing KQL Reports
To increase the speed of rendering reports, it is necessary to request only needed data from large databases. That is, if for the report, you need results only from a given time range, then there is no need to fetch all the data available in the database. Otherwise, a huge amount of resources will be spent on it to get all the data, not to mention the time spent. So, you will need some understanding of how MySQL makes calculations. The following section describes the basic statements that must be checked before moving on to query plans:
SELECT * statement is only recommended when you want to use all of the data returned by this statement. For example, if you are using a KQL query like:
SELECT * FROM 'Tickets'
This will not only fetch all the fields from the
Tickets table but also put additional load on the MySQL server and add to the time required to complete this query. Instead, you should only call the required fields, as shown in the following example.
SELECT 'Tickets.Ticket ID', 'Tickets.Subject', 'Tickets.Department' FROM 'Tickets'
It is always logical to use a
WHERE construction in KQL. In case you do not need all the fields and records from a table, etc., but only the ones corresponding to specific conditions. This applies a positive effect on performance. So, it is always suggested to add your conditions under the WHERE clause of the report like this:
SELECT 'Tickets.Ticket ID' FROM 'Tickets' WHERE 'Tickets.Department' = 'General'
In this sample query, you are telling Kayako to fetch results only from a specific department. Thus, the system will only look at the results under that department instead of the entire database.
To find more useful conditions and operators, see KQL Condition & Operator Reference.
When filtering the report's data and minimizing the data set that needs to be queried, the time ranges play an important role. For example, if you are running reports for data older than a month, you can use the
MKTIME function to limit the scope of your report. The following report will help you limit the time range for your report and reduce its load time:
SELECT 'Tickets.Ticket ID' FROM 'Tickets' WHERE 'Tickets.Department' = 'General' AND 'Tickets.Creation Date'<= MKTIME('0','0','0','20','9','2017') AND 'Tickets.Creation Date'<= MKTIME('0','0','0','3','31','2017')
This function becomes highly useful when you are running reports for large data sets. For example, you want to query all the data since 2010; you can use the
MKTIME function to break into 6 reports and reduce the time taken to run this report. The
MKTIME function accepts a date in the following syntax:
NOTE: The parameters of the MKTIME() function should be added in single quotes in Kayako 4.93.01 and above. For further information, refer to MKTIME.
Again, this is one more useful function to set limits to the scope of your report and avoid processing too many records. For example, you are fetching results from a very high-volume system where thousands of records are created every day. And even after using all the conditions and filters, the report is taking too long to run; you can use the
LIMIT function to break the results into parts, as shown in the example below.
SELECT 'Tickets.Ticket ID' FROM 'Tickets' WHERE 'Tickets.Department' = 'General' AND 'Tickets.Creation Date'= Today() LIMIT 100 OFFSET 0
This report will give you only the first 100 rows from the total results. To continue fetching the remaining records, you can increment the offset like this:
SELECT 'Tickets.Ticket ID' FROM 'Tickets' WHERE 'Tickets.Department' = 'General' AND 'Tickets.Creation Date'= Today() LIMIT 100 OFFSET 100
This last function concludes our article, and we hope that it will help you write, manage, and maintain your Kayako reports better.