https://community.databricks.com/t5/technical-blog/why-dbsql-is-best-for-bi-workloads-part-5-query-optimization/ba-p/78967 <P><SPAN><STRONG data-stringify-type="bold">Authors</STRONG>: Andrey Mirskiy (<A class="c-link" href="https://community.databricks.com/t5/user/viewprofilepage/user-id/40437" target="_blank" rel="noopener" data-stringify-link="https://community.databricks.com/t5/user/viewprofilepage/user-id/14326" data-sk="tooltip_parent">@AndreyMirskiy</A>) and Marco Scagliola (<A class="c-link" href="https://community.databricks.com/t5/user/viewprofilepage/user-id/63093" target="_blank" rel="noopener" data-stringify-link="https://community.databricks.com/t5/user/viewprofilepage/user-id/82808" data-sk="tooltip_parent">@MarcoScagliola</A>)</SPAN></P> <P><LI-TOC indent="30" liststyle="disc" maxheadinglevel="2"></LI-TOC></P> <P>&nbsp;</P> <H1><SPAN>Introduction</SPAN></H1> <P>Welcome to the fifth part of our blog series on 'Why Databricks SQL Serverless is the best fit for BI workloads'.</P> <P><SPAN>In the previous blog posts we have covered the following topics:</SPAN></P> <UL> <LI style="font-weight: 400;" aria-level="1"><A href="https://community.databricks.com/t5/technical-blog/why-databricks-sql-serverless-is-the-best-for-bi-workloads-part/ba-p/54925" target="_blank" rel="noopener"><SPAN>Part #1 - Disk Cache</SPAN></A></LI> <LI style="font-weight: 400;" aria-level="1"><A href="https://community.databricks.com/t5/technical-blog/why-databricks-sql-serverless-is-the-best-for-bi-workloads-part/ba-p/58074" target="_blank" rel="noopener"><SPAN>Part #2 - Apache jMeter for Databricks SQL performance testing</SPAN></A></LI> <LI style="font-weight: 400;" aria-level="1"><A href="https://community.databricks.com/t5/technical-blog/why-serverless-databricks-sql-is-the-best-for-bi-workloads-part/ba-p/60018" target="_blank" rel="noopener"><SPAN>Part #3 - Query Result Cache</SPAN></A></LI> <LI style="font-weight: 400;" aria-level="1"><A href="https://community.databricks.com/t5/technical-blog/why-serverless-databricks-sql-is-the-best-for-bi-workloads-part/ba-p/65507" target="_blank" rel="noopener"><SPAN>Part #4 - Remote Query Result Cache</SPAN></A></LI> </UL> <P><SPAN>In this blog post, we will discuss query optimization capability within Databricks SQL which leverages primary key constraints to improve query performance by eliminating unnecessary operations, such as DISTINCT aggregations and unnecessary joins.</SPAN></P> <P><SPAN>This blog explores how to implement and benefit from this optimisation technique, providing practical examples demonstrating how this capability improves query efficiency in Databricks SQL.</SPAN></P> <P>&nbsp;</P> <H1><SPAN>Query optimization using primary key constraints - Overview</SPAN></H1> <P><SPAN>Databricks SQL Warehouse allows users to specify informational PK and FK constraints. With the introduction of this new query optimization technique, users are now able to specify Primary Key (PK) constraints with the </SPAN><STRONG>RELY</STRONG><SPAN> option, allowing the Databricks query optimizer to utilize these constraints to optimize query execution plans and eliminate unnecessary operations. This technique can improve query performance, particularly in BI workloads..</SPAN></P> <P><SPAN>The specification of primary keys with the </SPAN><STRONG>RELY</STRONG><SPAN> option in table alteration statements is shown below in Code 1.&nbsp;</SPAN></P> <UL> <LI><SPAN><SPAN>Code 1: Creating primary key (PK) constraint with RELY option.<BR /></SPAN></SPAN><LI-CODE lang="python">USE CATALOG catalo_name; ALTER TABLE schema_name.table_name ADD PRIMARY KEY (column_name) RELY;</LI-CODE></LI> </UL> <P><SPAN>Currently Databricks SQL leverages this information to optimize query execution in the following scenarios:</SPAN></P> <OL> <LI style="font-weight: 400;" aria-level="1"><SPAN>A DISTINCT operation on a primary key column can be skipped. Because of the RELY option the engine knows that all values in that column are unique, therefore no need to perform additional operation.</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><SPAN>When using a star-schema like model a fact table can be joined with multiple dimension tables. When a query uses LEFT JOIN on the primary key column with the RELY option the engine may exclude that join operation from actual execution because this operation neither increases nor decreases the resultset.</SPAN></LI> </OL> <P><SPAN>These optimizations lead to more efficient query execution, making Databricks SQL faster and more effective.</SPAN></P> <P>&nbsp;</P> <H1><SPAN>Demo Scenario</SPAN></H1> <P><SPAN>In this demo scenario we are creating two synthetic environments, in order to create an A/B test on tables without any Primary Key (PK) defined and the same tables with Primary Key (PK)&nbsp; defined with the </SPAN><STRONG>RELY</STRONG><SPAN> option.</SPAN></P> <H2><SPAN>Preparation</SPAN></H2> <P><SPAN>First, we created test tables by replicating some of the tables from the </SPAN><STRONG>tpch</STRONG><SPAN> schema contained within </SPAN><STRONG>samples</STRONG><SPAN> catalog.</SPAN></P> <UL> <LI><SPAN><SPAN>Code 2: Create a Catalog and Schema in order to run the test.<BR /></SPAN></SPAN><LI-CODE lang="python">CREATE CATALOG IF NOT EXISTS join_optimization; CREATE SCHEMA IF NOT EXISTS join_optimization.tpch;</LI-CODE></LI> <LI><SPAN><SPAN>Code 3: Create the test tables as CTAS<BR /></SPAN></SPAN><LI-CODE lang="python">CREATE OR REPLACE TABLE lineitem AS SELECT * FROM samples.tpch.lineitem; CREATE OR REPLACE TABLE orders AS SELECT * FROM samples.tpch.orders; CREATE OR REPLACE TABLE part AS SELECT * FROM samples.tpch.part; CREATE OR REPLACE TABLE supplier AS SELECT * FROM samples.tpch.supplier; CREATE OR REPLACE TABLE customer AS SELECT * FROM samples.tpch.customer; CREATE OR REPLACE TABLE nation AS SELECT * FROM samples.tpch.nation; CREATE OR REPLACE TABLE region AS SELECT * FROM samples.tpch.region;</LI-CODE></LI> </UL> <H2><SPAN>Executing Sample Query without PKs</SPAN></H2> <P><SPAN>Before executing sample queries we may need to drop any primary keys which may exist after previous tests. We used Code 4 for that purpose.</SPAN></P> <UL> <LI><SPAN><SPAN>Code 4: Drop any primary keys within the test tables<BR /></SPAN></SPAN><LI-CODE lang="python">ALTER TABLE orders DROP PRIMARY KEY IF EXISTS; ALTER TABLE part DROP PRIMARY KEY IF EXISTS; ALTER TABLE supplier DROP PRIMARY KEY IF EXISTS; ALTER TABLE customer DROP PRIMARY KEY IF EXISTS; ALTER TABLE nation DROP PRIMARY KEY IF EXISTS; ALTER TABLE region DROP PRIMARY KEY IF EXISTS;</LI-CODE></LI> </UL> <P><SPAN>On Code 5, you can see the sample query which we used for testing.</SPAN></P> <UL> <LI><SPAN><SPAN>Code 5: Test query<BR /></SPAN></SPAN><LI-CODE lang="python">set use_cached_result=false; select sum(l_quantity), min(l_tax) from lineitem left join orders on l_orderkey=o_orderkey left join part on l_partkey=p_partkey left join supplier on l_suppkey=s_suppkey left join customer on o_custkey=c_custkey left join nation on c_nationkey=n_nationkey left join region on n_regionkey=r_regionkey;</LI-CODE></LI> </UL> <P><SPAN>When executed for the very first time (cold execution) we observed the following query execution metrics - see Image 1 below.</SPAN></P> <UL> <LI style="font-weight: 400;" aria-level="1"><SPAN>Total wall-clock duration = 5 s 723 ms</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><SPAN>Tasks total time = 54.84 s</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><SPAN>Bytes read = 358.80 MB</SPAN></LI> </UL> <P><span class="lia-inline-image-display-wrapper lia-image-align-center" image-alt="AndreyMirskiy_0-1721135999369.png" style="width: 400px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/9599iF74ED272CD9F3B03/image-size/medium?v=v2&amp;px=400" role="button" title="AndreyMirskiy_0-1721135999369.png" alt="AndreyMirskiy_0-1721135999369.png" /></span></P> <P class="lia-align-center"><SPAN>Image 1: Cold execution without PK constraints</SPAN></P> <P class="lia-align-left"><SPAN>When executed immediately for the second time (warm execution) we observed the following query execution metrics - see Image 2 below.</SPAN></P> <UL> <LI style="font-weight: 400;" aria-level="1"><SPAN>Total wall-clock duration = 4 s 749 ms</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><SPAN>Tasks total time = 18.96 s</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><SPAN>Bytes read = 358.80 MB</SPAN></LI> </UL> <P><span class="lia-inline-image-display-wrapper lia-image-align-center" image-alt="AndreyMirskiy_2-1721136151105.png" style="width: 400px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/9600iE6545390DF43E674/image-size/medium?v=v2&amp;px=400" role="button" title="AndreyMirskiy_2-1721136151105.png" alt="AndreyMirskiy_2-1721136151105.png" /></span></P> <P class="lia-align-center"><SPAN>Image 2: Warm execution without PK constraints</SPAN></P> <P><SPAN>Next, we tested the same query using a view that encapsulates the logic of joining tables. In code 6, you can see the DDL we created to encapsulate the test logic&nbsp; within a view.</SPAN></P> <UL> <LI><SPAN>Code 6: Test query wrapped within a view<BR /></SPAN><LI-CODE lang="python">create or replace view v_join_optimization as select * from lineitem left join orders on l_orderkey=o_orderkey left join part on l_partkey=p_partkey left join supplier on l_suppkey=s_suppkey left join customer on o_custkey=c_custkey left join nation on c_nationkey=n_nationkey left join region on n_regionkey=r_regionkey;</LI-CODE></LI> </UL> <P><SPAN>After restarting the SQL Warehouse to reset the Disk Cache, we executed the test query using a view twice. See Code 7 below.</SPAN></P> <UL> <LI><SPAN><SPAN>Code 7: Test query wrapped within a view<BR /></SPAN></SPAN><LI-CODE lang="python">set use_cached_result=false; select sum(l_quantity), min(l_tax) from v_join_optimization;</LI-CODE></LI> </UL> <P><SPAN>When executed for the first time (cold execution) we observed the following query execution metrics - see Image 3 below.</SPAN></P> <UL> <LI style="font-weight: 400;" aria-level="1"><SPAN>Total wall-clock duration = 7 s 850 ms</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><SPAN>Tasks total time = 55.61 s</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><SPAN>Bytes read = 358.80 MB</SPAN></LI> </UL> <P><span class="lia-inline-image-display-wrapper lia-image-align-center" image-alt="AndreyMirskiy_3-1721136205275.png" style="width: 400px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/9601iECDFB518F030EE39/image-size/medium?v=v2&amp;px=400" role="button" title="AndreyMirskiy_3-1721136205275.png" alt="AndreyMirskiy_3-1721136205275.png" /></span></P> <P class="lia-align-center"><SPAN>Image 3: Cold execution without PK constraints using a view</SPAN></P> <P><SPAN>When executed for the second time (warm execution) we observed the following query execution metrics - see Image 4 below.</SPAN></P> <UL> <LI style="font-weight: 400;" aria-level="1"><SPAN>Total wall-clock duration = 2 s 987 ms</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><SPAN>Tasks total time = 18.54 s</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><SPAN>Bytes read = 358.80 MB</SPAN></LI> </UL> <P><span class="lia-inline-image-display-wrapper lia-image-align-center" image-alt="AndreyMirskiy_4-1721136258275.png" style="width: 400px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/9602i083AFCE88F436C34/image-size/medium?v=v2&amp;px=400" role="button" title="AndreyMirskiy_4-1721136258275.png" alt="AndreyMirskiy_4-1721136258275.png" /></span></P> <P class="lia-align-center"><SPAN>Image 4: Warm execution without PK constraints using a view</SPAN></P> <H2><SPAN>Creating PK Constraints</SPAN></H2> <P><SPAN>Next, in order to test the benefits of query optimizations we created primary keys with the </SPAN><STRONG>RELY</STRONG><SPAN> option - see Code 7 below.</SPAN></P> <UL> <LI><SPAN><SPAN>Code 7: Alter the tables to define the primary key using the RELY option<BR /></SPAN></SPAN><LI-CODE lang="python">ALTER TABLE orders ALTER COLUMN o_orderkey SET NOT NULL; ALTER TABLE orders ADD PRIMARY KEY (o_orderkey) RELY; ALTER TABLE part ALTER COLUMN p_partkey SET NOT NULL; ALTER TABLE part ADD PRIMARY KEY (p_partkey) RELY; ALTER TABLE supplier ALTER COLUMN s_suppkey SET NOT NULL; ALTER TABLE supplier ADD PRIMARY KEY (s_suppkey) RELY; ALTER TABLE customer ALTER COLUMN c_custkey SET NOT NULL; ALTER TABLE customer ADD PRIMARY KEY (c_custkey) RELY; ALTER TABLE nation ALTER COLUMN n_nationkey SET NOT NULL; ALTER TABLE nation ADD PRIMARY KEY (n_nationkey) RELY; ALTER TABLE region ALTER COLUMN r_regionkey SET NOT NULL; ALTER TABLE region ADD PRIMARY KEY (r_regionkey) RELY;</LI-CODE></LI> </UL> <H2><SPAN>Executing Sample Query with PK Constraints</SPAN></H2> <P><SPAN>After restarting SQL Warehouse in order to reset Disk Cache, we executed sample queries again.</SPAN></P> <P><SPAN>When executed for the first time (cold execution) we observed the following query execution metrics - see Image 5.</SPAN></P> <UL> <LI style="font-weight: 400;" aria-level="1"><SPAN>Total wall-clock duration = 5 s 309 ms</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><SPAN>Tasks total time = 16.03 s</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><SPAN>Bytes read = 35.90 MB</SPAN></LI> </UL> <P><span class="lia-inline-image-display-wrapper lia-image-align-center" image-alt="AndreyMirskiy_0-1721136353262.png" style="width: 400px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/9603i4F2ACCA7AB84F7DD/image-size/medium?v=v2&amp;px=400" role="button" title="AndreyMirskiy_0-1721136353262.png" alt="AndreyMirskiy_0-1721136353262.png" /></span></P> <P class="lia-align-center"><SPAN>Image 5: Cold query execution with PK RELY constraints</SPAN></P> <P><SPAN>When executed for the second time (warm execution) we observed the following query execution metrics - see Image 6.</SPAN></P> <UL> <LI style="font-weight: 400;" aria-level="1"><SPAN>Total wall-clock duration = 1 s 357 ms</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><SPAN>Tasks total time = 338 ms</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><SPAN>Bytes read = 35.90 MB</SPAN></LI> </UL> <P><span class="lia-inline-image-display-wrapper lia-image-align-center" image-alt="AndreyMirskiy_1-1721136392831.png" style="width: 400px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/9604i757F6A62EEDA58A8/image-size/medium?v=v2&amp;px=400" role="button" title="AndreyMirskiy_1-1721136392831.png" alt="AndreyMirskiy_1-1721136392831.png" /></span></P> <P class="lia-align-center"><SPAN>Image 6: Warm query execution with PK RELY constraints</SPAN></P> <P><SPAN>Based on the query metrics we can clearly see that query performance is much better and it consumed less CPU (Tasks total time) and scanned less data (Bytes read).</SPAN></P> <P><SPAN>Finally, after restarting SQL Warehouse again in order to reset Disk Cache, we executed sample queries using views.</SPAN></P> <P><SPAN>When executed for the first time (cold execution) we could see the following query execution metrics - see Image 7.</SPAN></P> <UL> <LI style="font-weight: 400;" aria-level="1"><SPAN>Total wall-clock duration = 5 s 821 ms</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><SPAN>Tasks total time = 16.99 s</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><SPAN>Bytes read = 35.90 MB</SPAN></LI> </UL> <P><span class="lia-inline-image-display-wrapper lia-image-align-center" image-alt="AndreyMirskiy_2-1721136428281.png" style="width: 400px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/9605i61D92FC5BE562C8E/image-size/medium?v=v2&amp;px=400" role="button" title="AndreyMirskiy_2-1721136428281.png" alt="AndreyMirskiy_2-1721136428281.png" /></span></P> <P class="lia-align-center"><SPAN>Image 7: Cold execution with PK RELY constraints using a view</SPAN></P> <P><SPAN>When executed for the first time (cold execution) we could see the following query execution metrics - see Image 8.</SPAN></P> <UL> <LI style="font-weight: 400;" aria-level="1"><SPAN>Total wall-clock duration = 1 s 823 ms</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><SPAN>Tasks total time = 336 ms</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><SPAN>Bytes read = 35.90 MB</SPAN></LI> </UL> <P><span class="lia-inline-image-display-wrapper lia-image-align-center" image-alt="AndreyMirskiy_3-1721136467567.png" style="width: 400px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/9606iEDFF08DA02BD20B6/image-size/medium?v=v2&amp;px=400" role="button" title="AndreyMirskiy_3-1721136467567.png" alt="AndreyMirskiy_3-1721136467567.png" /></span></P> <P class="lia-align-center"><SPAN>Image 8: Warm execution with PK RELY constraints using a view</SPAN></P> <H2><SPAN>Summary Results</SPAN></H2> <P><SPAN>We have summarized test results in the table below.</SPAN></P> <P><SPAN>Table 1: Statistics on the test run with and without Primary Key RELY enabled&nbsp;</SPAN></P> <TABLE class=" lia-align-center"> <TBODY> <TR> <TD> <P><STRONG>Scenario</STRONG></P> </TD> <TD> <P><STRONG>Scenario</STRONG></P> </TD> <TD> <P><STRONG>Disk Cache</STRONG></P> </TD> <TD> <P><STRONG>Total wall clock</STRONG></P> </TD> <TD> <P><STRONG>Tasks total time</STRONG></P> </TD> <TD> <P><STRONG>Bytes Read</STRONG></P> </TD> <TD> <P><STRONG>Files read</STRONG></P> </TD> </TR> <TR> <TD> <P><SPAN>Plain query</SPAN></P> </TD> <TD> <P><SPAN>No PKs</SPAN></P> </TD> <TD> <P><SPAN>Cold</SPAN></P> </TD> <TD> <P><SPAN>5 s 723 ms</SPAN></P> </TD> <TD> <P><SPAN>54.84 s</SPAN></P> </TD> <TD> <P><SPAN>358.8 MB</SPAN></P> </TD> <TD> <P><SPAN>19</SPAN></P> </TD> </TR> <TR> <TD> <P><SPAN>Plain query</SPAN></P> </TD> <TD> <P><SPAN>No PKs</SPAN></P> </TD> <TD> <P><SPAN>Warm</SPAN></P> </TD> <TD> <P><SPAN>4 s 749 ms</SPAN></P> </TD> <TD> <P><SPAN>18.96 s</SPAN></P> </TD> <TD> <P><SPAN>358.8 MB</SPAN></P> </TD> <TD> <P><SPAN>19</SPAN></P> </TD> </TR> <TR> <TD> <P><SPAN>View</SPAN></P> </TD> <TD> <P><SPAN>No PKs</SPAN></P> </TD> <TD> <P><SPAN>Cold</SPAN></P> </TD> <TD> <P><SPAN>7 s 850 ms</SPAN></P> </TD> <TD> <P><SPAN>55.61 s</SPAN></P> </TD> <TD> <P><SPAN>358.8 MB</SPAN></P> </TD> <TD> <P><SPAN>19</SPAN></P> </TD> </TR> <TR> <TD> <P><SPAN>View</SPAN></P> </TD> <TD> <P><SPAN>No PKs</SPAN></P> </TD> <TD> <P><SPAN>Warm</SPAN></P> </TD> <TD> <P><SPAN>2 s 987 ms</SPAN></P> </TD> <TD> <P><SPAN>18.54 s</SPAN></P> </TD> <TD> <P><SPAN>358.8 MB</SPAN></P> </TD> <TD> <P><SPAN>19</SPAN></P> </TD> </TR> <TR> <TD> <P><SPAN>Plain query</SPAN></P> </TD> <TD> <P><SPAN>PK RELY</SPAN></P> </TD> <TD> <P><SPAN>Cold</SPAN></P> </TD> <TD> <P><SPAN>5 s 309 ms</SPAN></P> </TD> <TD> <P><SPAN>16.03 s</SPAN></P> </TD> <TD> <P><SPAN>35.9 MB</SPAN></P> </TD> <TD> <P><SPAN>11</SPAN></P> </TD> </TR> <TR> <TD> <P><SPAN>Plain query</SPAN></P> </TD> <TD> <P><SPAN>PK RELY</SPAN></P> </TD> <TD> <P><SPAN>Warm</SPAN></P> </TD> <TD> <P><SPAN>1 s 357 ms</SPAN></P> </TD> <TD> <P><SPAN>338 ms</SPAN></P> </TD> <TD> <P><SPAN>35.9 MB</SPAN></P> </TD> <TD> <P><SPAN>11</SPAN></P> </TD> </TR> <TR> <TD> <P><SPAN>View</SPAN></P> </TD> <TD> <P><SPAN>PK RELY</SPAN></P> </TD> <TD> <P><SPAN>Cold</SPAN></P> </TD> <TD> <P><SPAN>5 s 821 ms</SPAN></P> </TD> <TD> <P><SPAN>16.99 s</SPAN></P> </TD> <TD> <P><SPAN>35.9 MB</SPAN></P> </TD> <TD> <P><SPAN>11</SPAN></P> </TD> </TR> <TR> <TD> <P><SPAN>View</SPAN></P> </TD> <TD> <P><SPAN>PK RELY</SPAN></P> </TD> <TD> <P><SPAN>Warm</SPAN></P> </TD> <TD> <P><SPAN>1 s 823 ms</SPAN></P> </TD> <TD> <P><SPAN>336 ms</SPAN></P> </TD> <TD> <P><SPAN>35.9 MB</SPAN></P> </TD> <TD> <P><SPAN>11</SPAN></P> </TD> </TR> </TBODY> </TABLE> <P><SPAN>You can also find total wall clock duration statistics on the chart below; lower is better.</SPAN></P> <P class="lia-align-center"><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="AndreyMirskiy_4-1721136502581.png" style="width: 400px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/9607i452F9293D8E4BB34/image-size/medium?v=v2&amp;px=400" role="button" title="AndreyMirskiy_4-1721136502581.png" alt="AndreyMirskiy_4-1721136502581.png" /></span></P> <P><SPAN>We can clearly see that by leveraging primary key RELY constraints Databricks SQL builds a more efficient execution plan, scans less data from storage, and spends less CPU cycles. Which leads to better overall query performance.</SPAN></P> <P><SPAN>The reason for this performance difference becomes obvious when we compare the query profiles. The image below demonstrates the two query profiles - without PK and with PK RELY. We can see that when the query engine leverages information about the primary key the query profile is much simpler. The query optimizer excluded unnecessary joins, hence the query profile is more efficient.</SPAN></P> <P><span class="lia-inline-image-display-wrapper lia-image-align-center" image-alt="AndreyMirskiy_7-1721136692998.png" style="width: 999px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/9610iE6575DE4EBF37801/image-size/large?v=v2&amp;px=999" role="button" title="AndreyMirskiy_7-1721136692998.png" alt="AndreyMirskiy_7-1721136692998.png" /></span></P> <P class="lia-align-center"><SPAN>Image 9: Query profiles without PK and with PK RELY</SPAN></P> <H2><SPAN>JMeter test plan</SPAN></H2> <P><SPAN>To see the impact under high workload we implemented a JMeter test plan simulating 10 concurrent users each executing a sample query 100 times.</SPAN></P> <P><span class="lia-inline-image-display-wrapper lia-image-align-center" image-alt="AndreyMirskiy_10-1721136794176.png" style="width: 400px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/9612iFC5FD360B88C1308/image-size/medium?v=v2&amp;px=400" role="button" title="AndreyMirskiy_10-1721136794176.png" alt="AndreyMirskiy_10-1721136794176.png" /></span></P> <P class="lia-align-center"><SPAN>Image 10: JMeter test results - sample query when no PKs</SPAN></P> <P><span class="lia-inline-image-display-wrapper lia-image-align-center" image-alt="AndreyMirskiy_8-1721136750075.png" style="width: 400px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/9611i85EEDAAE14924B01/image-size/medium?v=v2&amp;px=400" role="button" title="AndreyMirskiy_8-1721136750075.png" alt="AndreyMirskiy_8-1721136750075.png" /></span></P> <P class="lia-align-center"><SPAN>Image 11: JMeter test results - sample query when using PK RELY</SPAN></P> <P><SPAN>The results are self-explanatory. The query performance when using PK RELY is significantly better. Not only was the average query performance more than 5 times better, but also the standard deviation was much lower. Meaning that end users will experience more consistent performance. Which is very important in high concurrency BI workloads.</SPAN></P> <P>&nbsp;</P> <H1><SPAN>Conclusion</SPAN></H1> <P><SPAN>In conclusion, leveraging primary key constraints with the RELY option in Databricks SQL can significantly enhance query performance for BI workloads. By specifying these constraints, the Databricks query optimizer can eliminate unnecessary operations such as DISTINCT aggregations and redundant joins, leading to more efficient query execution plans. Our tests demonstrated that queries with primary key constraints executed faster, consumed less CPU, and scanned less data compared to those without constraints. This optimization is particularly beneficial in high concurrency BI workloads, providing consistent and improved performance for end users. Implementing these techniques can help organizations maximize the efficiency and effectiveness of their BI workloads on Databricks SQL.</SPAN></P> <P><SPAN>For more information on Query optimization using primary key constraints, refer to the documentation (</SPAN><A href="https://learn.microsoft.com/en-us/azure/databricks/sql/user/queries/query-optimization-constraints" target="_blank" rel="noopener"><SPAN>Azure</SPAN></A><SPAN> | </SPAN><A href="https://docs.databricks.com/en/sql/user/queries/query-optimization-constraints.html" target="_blank" rel="noopener"><SPAN>AWS</SPAN></A><SPAN> | </SPAN><A href="https://docs.gcp.databricks.com/en/sql/user/queries/query-optimization-constraints.html" target="_blank" rel="noopener"><SPAN>GCP</SPAN></A><SPAN>).</SPAN></P> <P>&nbsp;</P> <H1><SPAN>Resources</SPAN></H1> <P><SPAN>The code artifacts featured in this blog are available in the following <A href="https://github.com/AMirskiy/databricks-sql-perf-demos/tree/main/5.%20Query%20optimization%20using%20PK/" target="_blank" rel="noopener">GitHub repository</A>.</SPAN></P> Tue, 16 Jul 2024 14:18:12 GMT AndreyMirskiy 2024-07-16T14:18:12Z https://community.databricks.com/t5/technical-blog/why-dbsql-is-best-for-bi-workloads-part-5-query-optimization/ba-p/78967 <P>??</P> Tue, 16 Jul 2024 14:18:12 GMT https://community.databricks.com/t5/technical-blog/why-dbsql-is-best-for-bi-workloads-part-5-query-optimization/ba-p/78967 AndreyMirskiy 2024-07-16T14:18:12Z