{"id":10381,"date":"2025-04-11T13:12:52","date_gmt":"2025-04-11T13:12:52","guid":{"rendered":"https:\/\/www.aegissofttech.com\/insights\/?p=10381"},"modified":"2026-03-18T11:56:37","modified_gmt":"2026-03-18T11:56:37","slug":"query-performance-in-snowflake","status":"publish","type":"post","link":"https:\/\/www.aegissofttech.com\/insights\/query-performance-in-snowflake\/","title":{"rendered":"9 Ways to Improve Query Performance in Snowflake"},"content":{"rendered":"\n<p>Snowflake is designed to transform and store your heavy volume data and perform analytical queries on your heavily loaded data.<\/p>\n\n\n\n<p>Many times, we face performance issues while querying this massive data, and it&#8217;s time-consuming. It comes with the cost of waiting for your query to run longer.<\/p>\n\n\n\n<p>Managing your setup internally or working with a Snowflake consulting partner or <a href=\"https:\/\/www.aegissofttech.com\/data-warehouse-services\/consulting\" target=\"_blank\" rel=\"noreferrer noopener\">data warehouse consulting<\/a> experts requires optimizing query performance in Snowflake to enhance efficiency and reduce costs.<\/p>\n\n\n\n<p>Here are eight effective techniques you should try for performance improvement in Snowflake.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">1 Query performance in Snowflake<\/h2>\n\n\n\n<p>Query optimization in Snowflake should be the very first approach to improve your query performance. The way queries are structured plays a very important role in performance. Follow these best practices and rewrite the query:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Select only required columns<\/strong>: Specify the columns you require in your output, avoid \u2018Select *\u2019<\/li>\n\n\n\n<li><strong>Use a WHERE clause to limit the result set<\/strong>: This will help in not fetching the unnecessary data, hence helping with faster performance.<\/li>\n\n\n\n<li><strong>Choose appropriate joins<\/strong>: Avoid unnecessary joins and use INNER JOIN where possible.<\/li>\n\n\n\n<li><strong>Use EXISTS instead of IN condition<\/strong>: EXISTS is more efficient than IN condition, as EXISTS returns true or false and breaks the loop; however, IN retrieves all the values in the subquery<\/li>\n<\/ul>\n\n\n\n<p><strong>Using IN Condition<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT Employee_ID, Employee_name \nFROM Employee\nWHERE Employee_ID IN (SELECT manager_id from Manager)<\/code><\/pre>\n\n\n\n<p><strong>Using EXISTS Condition<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT Employee_ID, Employee_name FROM \nEmployee E\nWHERE EXISTS IN (SELECT 1 FROM Manager M where M.manager_ID = E.Employee_ID)<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">2) Use Query Profile<\/h2>\n\n\n\n<p>To check a query profile, one should first understand how to use a query profile. The Query Profile in Snowflake&#8217;s user interface provides detailed insights into how a query was executed. It provides a visual representation of the query plan, displaying each node and its connections. You can view execution details and performance statistics for both individual nodes and full queries.<\/p>\n\n\n\n<p>If you are stuck with \u2018How to improve query in snowflake\u2019 then this will help you to understand loopholes in your query and help in recognizing improvement areas.<\/p>\n\n\n\n<p>Query profile is accessible from snowsight -&gt; Monitoring -&gt; Query History -&gt; Query Profile<\/p>\n\n\n\n<figure class=\"wp-block-image aligncenter size-full\"><img fetchpriority=\"high\" decoding=\"async\" width=\"484\" height=\"355\" src=\"https:\/\/www.aegissofttech.com\/insights\/wp-content\/uploads\/2025\/04\/Snowflake_query_documentation.jpg\" alt=\"Snowflake Query Documentation\" class=\"wp-image-10384\" title=\"Snowflake Query Documentation\" srcset=\"https:\/\/www.aegissofttech.com\/insights\/wp-content\/uploads\/2025\/04\/Snowflake_query_documentation.jpg 484w, https:\/\/www.aegissofttech.com\/insights\/wp-content\/uploads\/2025\/04\/Snowflake_query_documentation-300x220.jpg 300w\" sizes=\"(max-width: 484px) 100vw, 484px\" \/><\/figure>\n\n\n\n<div class=\"wp-block-cover aligncenter\"><img decoding=\"async\" width=\"443\" height=\"535\" class=\"wp-block-cover__image-background wp-image-10385\" alt=\"Snowflake nodes\" src=\"https:\/\/www.aegissofttech.com\/insights\/wp-content\/uploads\/2025\/04\/Snowflake_expensive_nodes.jpg\" data-object-fit=\"cover\" title=\"\" srcset=\"https:\/\/www.aegissofttech.com\/insights\/wp-content\/uploads\/2025\/04\/Snowflake_expensive_nodes.jpg 443w, https:\/\/www.aegissofttech.com\/insights\/wp-content\/uploads\/2025\/04\/Snowflake_expensive_nodes-248x300.jpg 248w\" sizes=\"(max-width: 443px) 100vw, 443px\" \/><span aria-hidden=\"true\" class=\"wp-block-cover__background has-background-dim\"><\/span><div class=\"wp-block-cover__inner-container is-layout-flow wp-block-cover-is-layout-flow\">\n<p class=\"has-text-align-center has-large-font-size\"><\/p>\n<\/div><\/div>\n\n\n\n<h2 class=\"wp-block-heading\">3) Materialized view in Snowflake<\/h2>\n\n\n\n<p>Materialized view stores the precomputed results in the cache. Hence, allowing faster query execution by retrieving cached data improves the performance.<\/p>\n\n\n\n<p>Whenever you have tables like type tables whose data doesn\u2019t change frequently, you can easily make use of a materialized view.<\/p>\n\n\n\n<p>Make a note that materialized views come with their own cost. So, whenever you think that query performance is costing more than maintaining a materialized view, you can always choose a materialized view.<\/p>\n\n\n\n<p><strong>Example:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE MATERIALIZED VIEW  AS\nSELECT employee_ID, COUNT(*) as employee_count\nFROM training_tbl\nGROUP BY employee_ID;<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">4) Make use of the result cache<\/h2>\n\n\n\n<p>Snowflake is very efficient in storing the result of your query in the result cache for upto 24 hours.<\/p>\n\n\n\n<p>If you have such datasets where the underlying data is not changed frequently, you can always make use of the result cache. This can significantly speed up recurring queries.<\/p>\n\n\n\n<p><strong>&#8212; First Execution (Caches Results, Execution Time \u2013 15 seconds)<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT count(*) from employee where region = \u2018North\u2019;<\/code><\/pre>\n\n\n\n<p><strong>&#8212; Subsequent Executions (Uses Cached Results, Execution Time \u2013 10 milliseconds)<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT count(*) from employee where region = \u2018North\u2019;<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">5) Try clustering the table<\/h2>\n\n\n\n<p>Clustering defines the partitioning in Snowflake, which helps us to store similar patterns of data in the same micro partition. Hence, it takes very minimal time to retrieve data from a clustered table. This is beneficial for large tables frequently queried on specific columns. Hence, whenever possible, try clustering your table to improve performance.<\/p>\n\n\n\n<p>You can cluster tables by using the query below.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER TABLE employee CLUSTER BY (employee_ID);<\/code><\/pre>\n\n\n\n<p>This improves performance when filtering by employee_ID, as Snowflake will scan very few partitions.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">6) Use Search Optimization Service (SOS)<\/h2>\n\n\n\n<p>If you have a query where you are filtering based on a column multiple times and a column with high cardinality values, then you can go for SOS. You should implement this if you have a non-clustered table with filters.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER TABLE employee ADD SEARCH OPTIMIZATION;<\/code><\/pre>\n\n\n\n<p>SOS follows the search access path, which helps in keeping track of values likely present in which micro partition. So, while scanning the table, it skips the unwanted partitions and hence produces your results faster. This is an extra service on top of Snowflake\u2019s regular strategy to track the minimum and maximum values present in each micro-partition for filtering the results.<\/p>\n\n\n\n<p>SOS is a cost-effective service that saves your processing time and cost.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">7) Use Query Acceleration Service (QAS)<\/h2>\n\n\n\n<p>Query Acceleration service is all about providing additional clusters to your long-running query.<\/p>\n\n\n\n<p>There is an eligibility criterion to check if your query is compatible with QAS. If yes, then QAS settings can be enabled on the warehouse level.<\/p>\n\n\n\n<p>If your query falls under the following criteria, then it is eligible to avail of QAS service.<\/p>\n\n\n\n<p>1 Query has filters or aggregations<br>2 Filters in query scan many rows.<\/p>\n\n\n\n<p><strong>How to check if your is eligible for QAS:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Select PARSE_JSON (system$estimate_query_acceleration('8cd54bf0-1651-5b1c-ac9c-6a9582ebd20f'));<\/code><\/pre>\n\n\n\n<p><strong>How to enable QAS:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER SESSION SET USE_QUERY_ACCELERATION = TRUE;<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">8) Alter warehouse settings<\/h2>\n\n\n\n<p>If you see slack in your query performance, you can check your warehouse settings. Follow the best practices to set your warehouse settings.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Increase warehouse size for complex queries.<\/li>\n\n\n\n<li>Use auto-suspend and auto-resume to optimize resource consumption.<\/li>\n\n\n\n<li>Enable multi-cluster warehouses for handling concurrent workloads.<\/li>\n<\/ul>\n\n\n\n<p>You can either do it using UI or with a query.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER WAREHOUSE warehouse_name SET WAREHOUSE_SIZE = \u2018LARGE\u2019;<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">9) Try altering the data structures<\/h2>\n\n\n\n<p>Choose data types wisely to improve query performance.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Use INTEGERs instead of STRINGs for categorical data.<\/li>\n\n\n\n<li>Normalize large tables where applicable.<\/li>\n\n\n\n<li>Replace VARCHAR with BOOLEAN or numeric values for flags.<\/li>\n<\/ul>\n\n\n\n<p>This reduces storage size and speeds up filtering operations.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>-- Instead of storing 'Male'\/'Female' as VARCHAR\nALTER TABLE users ADD COLUMN gender_flag BOOLEAN;\n-- Use 1 for Male, 0 for Female<\/code><\/pre>\n\n\n\n<p>Professional Snowflake implementation services can help you configure and optimize your <a href=\"https:\/\/www.aegissofttech.com\/data-warehouse-services\">data warehouse<\/a> from scratch, ensuring faster query performance.<\/p>\n\n\n\n<p>See more:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"https:\/\/www.aegissofttech.com\/insights\/advanced-snowflake-features\/\">Top Advanced Snowflake Features Explained<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/www.aegissofttech.com\/insights\/snowflake-data-types\/\">Explore Snowflake Data Types With Examples<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/www.aegissofttech.com\/insights\/snowflake-data-metric-functions\/\">Snowflake Data Metric Functions: Quick Learn<\/a><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":" ","protected":false},"author":4,"featured_media":10454,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[493],"tags":[1323,1322],"class_list":["post-10381","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-snowflake","tag-query-optimization-in-snowflake","tag-query-performance-in-snowflake"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.aegissofttech.com\/insights\/wp-json\/wp\/v2\/posts\/10381","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.aegissofttech.com\/insights\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.aegissofttech.com\/insights\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.aegissofttech.com\/insights\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/www.aegissofttech.com\/insights\/wp-json\/wp\/v2\/comments?post=10381"}],"version-history":[{"count":14,"href":"https:\/\/www.aegissofttech.com\/insights\/wp-json\/wp\/v2\/posts\/10381\/revisions"}],"predecessor-version":[{"id":18425,"href":"https:\/\/www.aegissofttech.com\/insights\/wp-json\/wp\/v2\/posts\/10381\/revisions\/18425"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.aegissofttech.com\/insights\/wp-json\/wp\/v2\/media\/10454"}],"wp:attachment":[{"href":"https:\/\/www.aegissofttech.com\/insights\/wp-json\/wp\/v2\/media?parent=10381"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.aegissofttech.com\/insights\/wp-json\/wp\/v2\/categories?post=10381"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.aegissofttech.com\/insights\/wp-json\/wp\/v2\/tags?post=10381"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}