topic Re: SQL query takes too long to run in Data Engineering

SQL query takes too long to run

Anonymous — Fri, 18 Nov 2022 03:47:29 GMT

Hi all,

I have a set up SQL query run with 5 hours but the SQL endpoint take too long to start up with each run. Currently I don't know how to fix this 😞

Could you please help me how to improve this?

Re: SQL query takes too long to run

Anonymous — Fri, 18 Nov 2022 07:40:11 GMT

Actually, sometime it works ok, sometime it takes too long. BTW, I will get a tcpdump to check.

Tks

Re: SQL query takes too long to run

Unforgiven — Tue, 22 Nov 2022 04:52:21 GMT

It's possible the connectivity to hive metastore is causing the delay here. When there is a high degree of concurrency and contention for metastore access. Interactive clusters in DBR are configured to use up to 5 (spark.databricks.hive.metastore.client.pool.size) hive clients. So if there are more than 5 concurrently running queries that are accessing the hive for a longer time, then there could be slowness.

The easy solution to try is to increase "spark.databricks.hive.metastore.client.pool.size" . Try increasing to 32 and see if there is an improvement.

Re: SQL query takes too long to run

Unforgiven — Tue, 22 Nov 2022 04:54:40 GMT

or refer on this : Faster SQL Queries on Delta Lake with Dynamic File Pruning

https://www.databricks.com/blog/2020/04/30/faster-sql-queries-on-delta-lake-with-dynamic-file-pruning.html

Re: SQL query takes too long to run

Debayan — Fri, 18 Nov 2022 06:57:26 GMT

Hi @Jensen Ackles , Could you please do a tcpdump to the endpoint and check the hops. Also, checking network logs may help too. Also, is the query heavy? Was it working fine before (on-time)?