Have you ever waited, minutes or even hours, on end for your queries to compute? Perhaps only to discover an error?
In this article, we’ll walk you through some of the most frequently encountered reasons for slow Redshift queries and show you how to speed them up.
Not enough space in your Redshift cluster
As time goes on, it’s not unusual for your company’s data volume to grow and your team to write more and more queries.
Unfortunately, that might lead to you running out of space in your cluster.
To figure out whether a lack of space in your cluster is the reason behind the slow queries, check to see if you’ve hit the maximum. The rule of thumb is to not exceed 80% of your cluster storage capacity.
If you discover you’ve exceeded 80% of your cluster storage, simply resize your cluster.
Elastic resize is the fastest method to resize a cluster, it adds or removes nodes on an existing cluster, then automatically redistributes the data to the new nodes.
To learn how to resize your clusters in Amazon Redshift, check out this step-by-step guide.
Running inefficient Redshift queries
Another common issue that causes slow Redshift queries is running inefficient queries.
For instance, a query that requires the scanning of all your data would be very inefficient and not the best use of your time.
In order to mitigate this risk of running inefficient queries in Redshift, consider the following tips when writing queries suggested by Redshift experts:
- Avoid using the UNION set operator where possible.
- Be as specific as possible with multiple levels of conditionals.
- Use INNER joins over LEFT joins because LEFT and RIGHT outer joins retain values from one of the joined tables when no match is found in the other table.
Running multiple Redshift queries
As much as Redshift is a powerful database, it still has computing limitations.
The obvious is running multiple queries simultaneously to your data warehouse. Amazon Redshift ETL processes can also extend query time.
To avoid slowing down your Redshift queries, you can stagger your query schedule throughout the day or at times you know your cluster will be least active.
If you’re a Kloudio user, you can schedule your data sync times for your Redshift connection.
Alternatively, all Redshift users can use Redshift’s Workload Management to speed up their queries.
Additional factors affecting query time
There are certainly more than three reasons as to why your Redshift queries may be running slower than expected. Here are some additional factors that might be slowing your queries down:
- Data sort order – Amazon Redshift stores table data on disk in sorted order according to a table’s sort keys.
The query optimizer and the query processor use the information about where the data is located to reduce the number of blocks that need to be scanned and thereby improve query speed.
- The number of nodes, processors, or slices – More nodes mean more processors and more slices, which enables your queries to process faster by running portions of the query concurrently across the slices.
However, more nodes also mean greater expense, so you need to find the balance of cost and performance that is appropriate for your business.
We hope these quick tips will help you speed up your Redshift queries. Kickstart your data analysis by connecting to your Redshift with Kloudio.