Tips To Enhance Speed And Performance

Speed and performance is very important when come to reporting. Running a report which summarizes million of data rows takes time. The issue is multiple if you have many users accessing the same heavy reports. That will put a heavy weight to server processing resources. Below are some tips to help you improve the speed and responsiveness of your report.

Utilize database processing power

Although KoolReport provide us a set of powerful data processing such as Filtering, Sorting and Grouping, it is better if you could do it with database. It is because of two reasons:

1. Database is designed to support data query. Data filter, sort and group are basic tasks of any database and all of them are designed to work on them in the most efficient manner. So we would utilize this power
2. Reduce the amount of data transferred between data server and web server.

Example

Example 1: Instead of doing this:

$this->src("mydata")
->query("Select id,name,emails from customers")
->pipe(new Filter(array(
    array("name","startWith","Peter")
)))
->pipe(...)
...

we do

$this->src("mydata")
->query("Select id,name,emails from customers where name like :name")
->params(array(
    ":name"=>"Peter%"
))
->pipe(...)

Example 2: Instead of doing this

$this->src("mydata")
->query("Select customerName,amount from customers")
->pipe(new Group(array(
    "by"=>"customerName",
    "sum"=>amount,
)))
->pipe(...)
...

we do

$this->src("mydata")
->query("Select customerName,sum(amount) as total GROUP BY customerName")
->pipe(...)

You may question if we can do all thing efficiently with database, why do we need the KoolReport data processes. The answers are:

1. Not all the time we work with database. Data loaded from CSV, Excel source does not have power of data server.
2. To avoid repeating queries to data server. In many cases, with one query we can drawn all the data we need and other data can be calculated from that data. It is more efficient to process in PHP.

Avoid "Select * .."

Many of us has tendency to use Select *. This is not recommended since it causes too much waste. The returned rows contain data column that we may not use and that cause extra bandwidth to transfer those unnessary data and memory to store them. The rule of thump is only query the column that we need.

Avoid repeating data query

Limit the number of queries to your data server as many as possible.

For example, instead of doing this:

//Query 1
$this->src("mydata")
->query("select customerName,amount from orders")
->pipe($this->dataStore("all_sales"))

//Query 2
$this->src("mydata")
->query("select customerName,sum(amount) as total from orders group by customerName")
->pipe($this->dataStore('sale_amount_by_customer'))

we do:

$this->src("mydata")
->query("select customerName,amount from orders")
->saveTo($root) // Save the source
->pipe($this->dataStore("all_sales"));

//Do grouping
$root->pipe(new Group(array(
    "by"=>"customerName"
    "sum"=>"amount"
)))
->pipe($this->dataStore("sale_amount_by_customer"));

In above example, instead of making two queries, we only did one query and using all sales data to summarize sale by customers.

Use Cache

Let imagine your report need to go through million of rows to produce a report but you have number of people accessing it. Each person view a report, the report need to run all processes again to produce, most of the time, the same result. Each time the report runs, it consumes certain resources in term of CPU, memory and network bandwidth. Such a waste. Furthermore, it degrade the user experience since report normally takes certain amount of time to produce result.

Using Caching method will be the best solution. The result of report will be stored temporarily in certain time-frame. During that time-frame, report, intead of re-processing data, will return temporary result right away. The benefits will be:

1. Save resource of CPU, Memory and Bandwidth to re-processing data
2. Increase the responsiveness and user experience of report since result is returned instantly.

KoolReport has Cache package which can help you to address this issue. Cache provide 3 types of caching system FileCache, ApcCache and MemCache.

The FileCache use the file system to store recent report result. It is cheap and easy and still produce great result. To use the FileCache, you only need to specify this on your report:

<?php

class MyReport extends \koolreport\KoolReport
{
    use \koolreport\cache\FileCache;

    function cacheSettings()
    {
        return array(
            "ttl"=>60,
        );
    }
    ...
}

"ttl" stands for Time To Alive means that how long the cached result will be stored and report does not need to re-process.

The advanced caching system is ApcCache and MemCache, if you have Apc or MemCache installed in your system then you can use them as your caching methods. More information of using are found at Cache package page.

Summary

In this tutorial, we have addressed the speed and responsiveness of reports. They are very important when come to reporting. Tips to improve are: Utilizing database processing power, avoid using select *, avoiding repeated queries and using Cache system.

If you have any question, you may reply to us of this email.

See you in the next tutorial.

Resources

1. Cache Package

<3 koolreport team