topic Re: Databricks SQL script slow execution in workflows using serverless in Data Engineering

Databricks SQL script slow execution in workflows using serverless

iamgoda — Thu, 11 Jul 2024 12:41:33 GMT

I am running a very simple SQL script within a notebook, using an X-Small SQL Serverless warehouse (that is already running). The execution time is different depending on how it's run:

4s if run interactively (and through SQL editor)
26s if run within a workflow as a notebook task

If you look at the query history screenshot below you see the individual queries are taking the same amount of time to execute, but for some reason there's a few seconds delay between end of one query and start of the next query (line) when running through workflow vs interactively (both using SQL serverless warehouse). I tried both current and preview channel and they both behave the same way

I even tried using an all purpose compute cluster (Single node: Standard_DS3_v2 · DBR: 15.3) and the times were consistent when running in a workflow vs interactively.

Script

declare or replace start_time timestamp = current_timestamp(); declare or replace end_time timestamp = current_timestamp(); declare or replace v STRUCT<SOURCE_TBL_MAX_TIMESTAMP STRING, TARGET_TBL_MAX_TIMESTAMP STRING, SOURCE_MAX_TIMESTAMP TIMESTAMP, TARGET_MAX_TIMESTAMP TIMESTAMP, MIN_TIME TIMESTAMP, MAX_TIME TIMESTAMP, MAX_INTERVAL_TIME TIMESTAMP, TABLE_SOURCE STRING, TABLE_TARGET STRING, MERGE_STATEMENT STRING, INTERVAL_COLUMN_NAME STRING, STAGING_TABLE_NAME STRING, STAGING_TABLE_CREATE_STATEMENT STRING, FULL_STAGING_TABLE_NAME STRING>; select current_timestamp(); select v; select current_timestamp(); select timestampdiff(MILLISECOND , start_time, current_timestamp()) as total_time_elapsed_ms;

Screenshots

Query History

Further tests

Using a SQL file workflow task, running on X-Small SQL serverless, the same script executes as expected (faster than notebook task):

Not sure why the type of workflow task would impact the execution of the SQL script on a serverless warehouse.

Re: Databricks SQL script slow execution in workflows using serverless

iamgoda — Thu, 11 Jul 2024 12:17:42 GMT

@SergeRielau - is this another bug or?

Re: Databricks SQL script slow execution in workflows using serverless

SergeRielau — Thu, 11 Jul 2024 18:08:53 GMT

@iamgoda No idea. In DBSQL session variables and their values must be preserved outside the runtime engine (since the session may be migrated), but that doesn't seem to be at play here (and it should not have such an impact).

Re: Databricks SQL script slow execution in workflows using serverless

iamgoda — Thu, 11 Jul 2024 18:36:30 GMT

Yes and the fact that running it as a SQL script task works. Running it as a notebook task on a SQL serverless instance seems to be the issue.

Re: Databricks SQL script slow execution in workflows using serverless

BilalAslamDbrx — Fri, 12 Jul 2024 08:41:43 GMT

@iamgoda we are going to look into how to make this faster. There's a poll loop in Databricks Workflows for SQL notebooks (but not for SQL scripts) which causes things to slow down.

Re: Databricks SQL script slow execution in workflows using serverless

iamgoda — Fri, 12 Jul 2024 11:43:51 GMT

@BilalAslamDbrx thanks for looking into this and confirming the issue. This doesn't happen if the SQL notebook task is run using all purpose compute in a workflow, so wondering if the poll loop only applies to SQL serverless compute?

Re: Databricks SQL script slow execution in workflows using serverless

BilalAslamDbrx — Fri, 12 Jul 2024 14:45:28 GMT

This adds up. This particular polling inefficiency only applies to the flow of notebooks running on warehouses (not on clusters).

Re: Databricks SQL script slow execution in workflows using serverless

iamgoda — Fri, 19 Jul 2024 11:35:46 GMT

Any idea of whether this will be addressed and when?

Re: Databricks SQL script slow execution in workflows using serverless

BilalAslamDbrx — Sat, 20 Jul 2024 15:16:47 GMT

No not yet

Re: Databricks SQL script slow execution in workflows using serverless

Ememel — Sun, 13 Oct 2024 20:56:45 GMT

Hello. Are there updates for this issue?

Re: Databricks SQL script slow execution in workflows using serverless

iamgoce — Wed, 16 Oct 2024 13:39:00 GMT

I was told that it would be fixed in Q4 this year. I am checking for an update again and will let you know if they give me a more exact date.

Re: Databricks SQL script slow execution in workflows using serverless

iamgoce — Mon, 28 Oct 2024 18:50:23 GMT

So I was told that the Q4 date was incorrect - in fact there is currently no ETA for when this issue will be fixed. It's considered lower priority by Databricks as not enough customers are impacted or have raised this type of an issue. I would recommend @Ememel that you try and raise this issue through your Databricks support team or Microsoft so it increases the priority?

Re: Databricks SQL script slow execution in workflows using serverless

LordAsp — Tue, 14 Jan 2025 12:25:26 GMT

I'm facing the exact same issue - when ran as an SQL Notebook it runs in 40 seconds, the same Notebook as a job on a serverless sql arehouse

There just seems to be gaps of a good number of seconds between each statement when ran as a job.

Severless SQL warehouse takes ~2 minutes.

(I also tried running this on a serverless cluster and it was ~2 minutes, but I can't see the breakdown of statements in the job when I did that)

Re: Databricks SQL script slow execution in workflows using serverless

iamgoce — Tue, 14 Jan 2025 12:41:47 GMT

Yeah I don't think it's been fixed yet or is even on the roadmap to be fixed. Databricks can't prioritize fixing this bug until enough users report it. So if you can, report the issue directly with your Databricks account team.

Re: Databricks SQL script slow execution in workflows using serverless

LordAsp — Tue, 14 Jan 2025 12:43:11 GMT

Now remember to attached the file 😛
Made a job with the same task 3 times, first 2 times as dbquery.ipynb (first to ensure Serverless Starter Warehouse awake) and then last time as a .sql file (exact same code)

Re: Databricks SQL script slow execution in workflows using serverless

LordAsp — Tue, 14 Jan 2025 12:50:24 GMT

It'll certainly be more of a bother for us to stick to .sql files (as databricks seems to push you to dbquery.ipynb stuff) so I'll find out who our account team is, and ask them to raise it as well 🙂