Pivot table based on the data from SQL databases: how to achieve the best performance
This blog post will be especially interesting for those integrators who use SQL databases in their projects. Recently we have started getting more and more similar requests such as:
- “We use SQL DB and we want to reduce the time of data loading inside the component.”
- “We have a lot of rows in our SQL DB, but due to browsers restrictions we can’t load them all…”
- “We have several user roles in our system with different permissions and we want to show the data from our SQL DB according to the levels of access.”
At first sight, those three situations are different, but as you will see, they can be solved in the same way. Here we are going to give you some recommendations on how to handle such issues.
Flexmonster Pivot table is a web component which runs in a browser. As the result, it is limited with CPU and RAM resources that mostly affects the size of the data sources. Even though the component supports up to 100MB .csv files, sometimes it is impossible to work in browsers with the same amount of data as in a desktop application such as Excel, especially on client’s side computer. In such situations, the optimization matters a lot. From our developer experience, the best way to overcome these problems is to pre-filter the data on the server side and then pass it to the component. In 99.9% of cases, you do not need all the data you have to be loaded for a particular report, only some segment of it. Such approach improves performance and gives full control on the situation. The only thing you need is a script which will handle the optimization process for you. That is why we have created Flexmonster Data Compressor tool.
Our compressor is a server side script which allows you to pre-filter the data and also to make it more compact. Data compression reduces the size of the response which leads to reducing the time spent on the transporting the data through the internet. Such approach allows you to set the connection between the SQL database and the component in the most efficient way. As the result, the risk of loading more megabytes than a browser can handle is much lower and the transfer of data is much faster. Our Flexmonster Component is very easy to integrate and we did our best to keep this approach to our Compressor tool. You can find the examples of how to configure it in our documentation (http://www.flexmonster.com/doc/connecting-to-relational-database/). The only thing that you have to do is to write the right SQL query to your database and leave everything else to the Compressor tool.
Let’s imagine that we have some large SQL database. In this article, we will concentrate on just its small part (Picture 1). Here we have four tables: “customers”, “purchases”, “products” and “purchased_products”. We will apply filtering by modifying the simple SQL query that selects all the columns from “customers” table:
$result = mysql_query("SELECT * FROM customers");
Here we will select only some columns from the table. If you want to show just name, birthday and hometown for each customer, your SQL query should be the following:
$sql = "SELECT name as 'Customer Name', birthday as 'Birthday', home_town as 'Hometown' FROM customers";
The above query allows you to load only required columns from the data table.
To filter data rows please use WHERE condition in the query. For example, you want to show customers only from the specified city. You can pass this parameter in the query string to the script https://localhost:8080/demo-compress-mysql.php?home_town =Omaha and then use it in SQL query as follows:
$sql = "SELECT surname as 'Customer Surname' , name as 'Customer Name', birthday as 'Birthday', home_town as 'Hometown' FROM customers WHERE home_town = '". $_GET["home_town "] ."'";
We get home_town attribute from the server request and pass it to the SQL query. This way you can pass as many parameters as you want and make queries more and more specified https://localhost:8080/demo-compress-mysql.php?param1=value1¶m2=value2¶m3=value3.
Let’s write a more specific query. Here we will get the information about the client’s purchases filtered by area and purchasing date:
$sql = " SELECT surname as 'Customer Surname' , name as 'Customer Name', birthday as 'Birthday', purchase_date as 'Day of purchase' , purchase_price as 'Price', prod_name as 'Product', quantity as 'Product quantity' FROM customers INNER JOIN purchases ON id_customer = customer_id INNER JOIN purchased_products ON id_purchase = purchases_id INNER JOIN products ON id_product = products_id WHERE area = '". $_GET["area"] ."' AND purchase_date BETWEEN '". $_GET["purch_date_start"] ."' AND '". $_GET["purch_date_finish"] ."' ";
Such approach allows you to set a great number of different conditions and choose the ideal subset of data for your report.
Also, the Compressor tool can help you with defining the levels of access. Almost every project comes to a point when it is time to specify user privileges and give them certain permissions according to their role in the system. The consequences of these decisions influence the way you had shown the data before. This is the right moment to decide what is needed to be shown and what should be hidden. The solution is very simple, you can write different SQL queries and use the one which is fitting the user’s role. For example, if the person is not logged in then his/her privileges level will be low, but when the user is already authenticated there will be no problem to identify person’s role and show the amount of data according to the permission level.
As you can see here, our Compressor tool is made to help you to achieve the best performance when using SQL database. It is very easy to use, just write the suitable SQL query and it will do the rest.