Optimizing KQL reports
Optimization is challenging, no matter what you are doing, and unfortunately, reporting in GFI HelpDesk 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 GFI HelpDesk.
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 is spent on it to get all the data, not to mention the time spent. So, you 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 *
Using the 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:
This not only fetches 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.
WHERE
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:
In this sample query, you are telling GFI HelpDesk to fetch results only from a specific department. Thus, the system only looks at the results under that department instead of the entire database.
To find more useful conditions and operators, see KQL Condition & Operator Reference.
MKTIME
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 helps you limit the time range for your report and reduce its load time:
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:
The parameters of the MKTIME() function should be added in single quotes in GFI HelpDesk 4.93.01 and above. For further information, refer to MKTIME.
LIMIT
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.
This report gives you only the first 100 rows from the total results. To continue fetching the remaining records, you can increment the offset like this: