Query Acceleration by Sorting

Version

Application Scenario

When big data is filtered at the detail level, the current data storage and retrieval methods may aggravate system processing burdens and incur system performance issues such as slow queries. In this case, IT personnel (administrators) can enable Querying Acceleration by Sorting to reasonably set sorting keys for datasets. In this way, the storage structure is optimized so that data is read more efficiently and queried at a higher speed during filtering, optimizing the overall performance.

For example, the Store sales statistics table with large amounts of data often needs to be filtered by shop number. You can select shop number as Sorting Field for query acceleration, as shown in the following figure.

Function Description

The following lists the rules of query acceleration by sorting.

• Up to 100 datasets can be added.

• Sortable datasets must meet the following conditions simultaneously: less than 200 columns, datasets not configured with incremental update, and non-simplest tables. You are advised to control the number of extracted rows within 10 million rows and 1 billion rows.

• The sorting basis configured and saved on the page will take effect at the next table update.

• After you confirm the sorting basis adding, the table update time will be increased.

Why Sorting Can Improve the Query Speed

In databases (including FineBI databases), data is stored by block. Each block is numbered. During query by filtering, the computer has to search for the data that meets the conditions in all blocks if the data is not sorted.

However, the data sorted according to certain rules will be distributed in an orderly manner.

To make an analogy, databases, blocks, and data are like bookshelves, storage bins, and books respectively. To find a biology book published in the 1980s, you have to search through each storage bin if the books are not sorted, which is troublesome and time-consuming. However, you can directly find the required book in the corresponding bin if the books have been sorted by type and publication year, which is much more efficient.

How to Select a Sorting Field

The field by which data is filtered frequently is suitable as the sorting field. For example, if you frequently need to query data for male employees, the Gender field can be used as the sorting field. If you frequently need to query data for employees within a specific age range, the Age field can be used as the sorting field.

For example, business users feed back the slow query speed when they filter data in ENdemo_Product_Sales. After researches, IT personnel find that business users frequently need to view the sales by brand in different areas by filtering. Therefore, the Area and Brand fields can be used as sorting fields.

1. Log in to FineBI as the admin, choose System Management > Intelligent O&M > Acceleration Engine, and click Query Acceleration by Sorting, as shown in the following figure.

2. Click Add Sorting Basis and select ENdemo_Product_Sales, as shown in the following figure.

The Query Acceleration by Sorting function allows you to sort data in tables from User Analysis, Public Data, and My Analysis.

3. After adding the table, select Area and Brand in the table as the sorting fields, as shown in the following figure.

4. Return to the location of the data table and update the data table to make the query acceleration take effect.