Help with Databricks SQL Queries

[alexacas]

Hi everyone,

I’m relatively new to Databricks and trying to optimize some SQL queries for better performance. I’ve noticed that certain queries take longer to run than expected. Does anyone have tips or best practices for writing efficient SQL in Databricks? Specifically, I’m interested in how to handle large datasets and any strategies for indexing or partitioning data effectively.

surcharge

[mhiltner · Accepted solution]

You can find some tips here: https://community.databricks.com/t5/technical-blog/top-10-query-performance-tuning-tips-for-databric... 

And here: https://www.databricks.com/discover/pages/optimize-data-workloads-guide 

My overall recommendation would be to check the query performance window and find which processes are taking the longest. Than you can understand whether a broadcast would help, or repartitioning or any other strategy. 

[filipniziol]

Hi @alexacas ,

The best thing is to share the queries and table structures 🙂

But my general approach is:
1. Use partitioning/zordering, or if you can upgrade runtime to 15.4, use liquid clustering, that is the new optimization technique.

2. Make sure you do not have many small files. Run DESCRIBE DETAIL on your tables to check if the files are of around 128 MB. If they are not, make sure to have maintenance job to run OPTIMIZE on your tables on regular basis.