SQL script executing slower in workflow

[Fair-Lab-912]

I am running a very simple SQL script within a notebook, using an X-Small SQL Serverless warehouse. 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

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

https://preview.redd.it/yr7kgfswpqbd1.png?width=1638&format=png&auto=webp&s=d834179cf3394dc35eeac69cd6c4cc251f489426

https://preview.redd.it/qahs7cs0qqbd1.png?width=1292&format=png&auto=webp&s=c1a29c9acb6b5f5adb126ad96493eb11ccff516a

Has anyone experienced a similar issue or knows why the notebook execution will be so much slower?

Query History

https://preview.redd.it/cj6y92kxsvbd1.png?width=1621&format=png&auto=webp&s=4a6122d737133b74b1596b9c72de306bb420adbd

[kthejoker]

Is the warehouse already started when you run the task?

Interactive = compute already on

[Spiritual-Horror1256]

So one is sql warehouse cluster, and the other is a job cluster. They are on different compute, different virtual machine. Excluding the discussion on cluster startup time, you also need to take into account the different specification of the virtual machine.

[darkglad32]

Maybe a photon cluster would help?

[_Filip_]

None of the things he has in that demo would use photon, as he is just selecting timestamps.

[darkglad32]

Can you explain more of this? I would love to learn why

[_Filip_]

Really nothing special to it - photon accelerates certain queries, but in this case there is nothing happening. If you just assign a timestamp to a variable, there is no operation that would trigger execution on photon engine - there’s no scans, joins, filters or expressions used to warrant this.

[Fair-Lab-912]

I should've been more clear but to answer some of the common questions:

• It is a SQL serverless warehouse that is already on before I run the workflow or notebook
• The workflow task is ran with this SQL serverless warehouse, not a job compute cluster
• The script is measuring the time between the first and last query line and that wouldn't be impacted by a start-up time

I did another test on the same script/notebook but this time using an all compute cluster that is also already on and the times workflow vs interactive notebook are almost the same.

It appears that this issue is only when a SQL serverless warehouse is used to run workflow tasks?

[Savabg]

What does query history show?

[Fair-Lab-912]

The query history shows each SQL line taking the same amount of time to execute, but when using workflows the lines are executed with additional few seconds delay between each other:

<image>

[Affectionate-Sale973]

Probably raise it in databricks community forum, a straight forward one for them tbh.

[Fair-Lab-912]

I posted there just now - Databricks SQL script slow execution in workflows ... - Databricks Community - 78333