Disabling query caching for SQL Warehouse

[Marvin_T]

Hello everybody,

I am currently trying to run some performance tests on queries in Databricks on Azure. For my tests, I am using a Classic SQL Warehouse in the SQL Editor.

I have created two views that contain the same data but have different structure -> I am trying to see if those different structures affect query performance.

My issue:

To test the performance I am running similiar queries that result in the same output for the individual views. Caching of course screws with the results as the output is fully cached from the previous query.

I have tried to do the following things already:

• SET use_cached_result = false; -> Does not impact my query caching unfortunately
• CLEAR CACHE; -> Does not work on SQL Warehouses
• spark.conf.set("spark.databricks.io.cache.enabled", "false") -> Also doesnt impact the query caching

Looking at the caching structure of databricks I guess I need to disable the UI Cache, but I can't find any information on how to do so. Does anybody know how to solve my problem?

[-werners-]

besides caching, are you sure the queries execute a different query plan?

I'd think (not sure though) that if you stop the warehouse, the cache gets cleared. Perhaps that is useable?

[Marvin_T · Accepted solution]

They are probably executing the same query plan now that you say it. And yes, restarting the warehouse does theoretically works but it isnt a nice solution.

I guess I will do some restarting and build averages to have a good comparison for now