Improve Dashboard Performance by Optimizing Data Access
Business dashboards provide visualizations of business data to communicate metrics and other key points to users at a glance. Such a dashboard should respond quickly to any dynamic user interaction. The loading performance of a dashboard not only affects the dashboard objects’ rendering but also the connected database’s querying performance. In this article, let’s explore how SQL Server database columnar storage and indexing help to load dashboards much faster.
Setting up SQL Server database with sample data set and recordings
Take a sample data set of over 25 million records. For this example, we downloaded the UK HM Land Registry Price paid data set from this government site. Then move the data from the CSV file to a SQL Server database running in a local machine with 16 GB RAM.
Executing a simple count statement, it takes one minute and seven seconds for the first run to complete query execution. The second run takes only two seconds since it utilizes the SQL Server default caching operations of previous query results.
Note: I have used SQL Server Management Studio to run these queries and get these metrics.
Here are two more sample queries with recordings for testing purposes during both runs.
Actions for improved performance
As a business user, we expect our business dashboards to load in seconds. So, to speed up data access and aggregation, perform the following two actions:
- Convert the row store to columnar store. This will give high performance gains for analytic queries that require scanning large amounts of data. Execute the following command to created clustered columnar store.
To learn more about column store in SQL Server, refer to this Microsoft documentation.
2. Starting with SQL Server 2016, you can combine row store index and column store index on the same table. Create a non clustered row index on the same table using the following command. This row index will improve table seek performance, so queries that look for matching values or return a range of values will perform better in row index than columnar index.
To verify whether the index has been created, try running the following command. This will describe the table details fully. At the bottom of the results, you can see that two indexes are present
Now let us rerun the sample queries to test the indexing performance.
You can see that there is monumental improvement in SQL Query performance once columnar indexing and row indexing is done. To learn more about combining column and row indexing for data warehousing needs, refer to this documentation.
Creating a dashboard
Now let’s build a dashboard connecting to this SQL Server database using Bold BI. To learn about how to connect with SQL Server from Bold BI, read this blog post. Consider the following widgets and data configuration to prepare a dashboard on the HM Land Registry Price paid data set.
Now the dashboard is ready.
Performance metrics
The average performance of this dashboard is given in the following.
To capture the query generated by Bold BI, use the SQL profiler trace option by connecting with the database server. Using the traced SQL queries for the previous dashboard, we have listed out the performance metrics for each of the queries before and after indexing separately for understanding.
Conclusion
With this blog, we can conclude that for data warehousing, data analytics, and dashboarding use cases, we should leverage the indexing support provided by SQL Server in order to gain high performance on analytics queries running on large data. If you have any questions on this blog, please feel free to post them in the following comment section. To get started with Bold BI, please request a free 30-minute demo with our experts to discuss creating dashboards and any other features you would like to learn more about. You can also contact us by submitting your questions through the Bold BI website or, if you already have an account, you can log in to submit your support question.
Originally published at www.boldbi.com on October 8, 2019
