コンテンツへスキップ
  • AT SNOWFLAKE
  • Industry solutions
  • Partner & Customer Value
  • Product & Technology
  • Strategy & Insights
Languages
  • Italiano
  • Español
  • Deutsch
  • Français
  • Português
  • 日本語
  • English
  • 한국어
  • Italiano
  • Español
  • Deutsch
  • Français
  • Português
  • 日本語
  • English
  • 한국어
  • AT SNOWFLAKE
  • Industry solutions
  • Partner & Customer Value
  • Product & Technology
  • Strategy & Insights
  • Italiano
  • Español
  • Deutsch
  • Français
  • Português
  • 日本語
  • English
  • 한국어
  • 概要
    • Snowflakeを選ぶ理由
    • カスタマーストーリー
    • パートナー
    • サービス内容
  • 概要
    • プラットフォームの概要
    • Snowflakeマーケットプレイス
    • Powered by Snowflake
    • ライブデモ
  • ワークロード
    • コラボレーション
    • データサイエンス&機械学習
    • サイバーセキュリティ
    • アプリケーション
    • データウェアハウス
    • データレイク
    • データエンジニアリング
    • ユニストア
  • 価格
    • 料金体系
  • 業界
    • 広告・メディア・エンターテインメント
    • 金融サービス
    • ヘルスケア・ライフサイエンス
    • マーケティングアナリティクス
    • 官公庁・公的機関
    • 小売・消費財
    • テクノロジー
  • 詳しく見る
    • リソースライブラリー
    • 資料
    • ハンズオンラボ
    • トレーニング
  • Connect
    • ブログ
    • コミュニティ
    • イベント
    • ウェビナー
    • ポッドキャスト
  • 概要
    • Snowflakeについて
    • 投資家情報
    • 経営陣と取締役会
    • 採用情報
Share
Subscribe
2016年5月19日

Automatic Query Optimization. No Tuning!

  • 製品 & テクノロジー
    • データエンジニアリング
  • パートナー & カスタマーバリュー
    • カスタマーストーリー
Automatic Query Optimization. No Tuning!

Hopefully you had a chance to read all our previous top 10 posts. We now conclude the series with a final deep dive into my #1 Cool Feature from Snowflake:

#1 Automatic Query Optimization. No Tuning!

As a long-time data architect, and not a DBA, this is my favorite part of Snowflake. I do not have to worry about query performance at all. It is all handled “auto-magically” via a dynamic query optimization engine in our cloud services layer. I just model, load, and query the data.

Cloud Services

Snowflake is an MPP, columnar store thus designed for high speed analytic queries by definition. Our cloud services layer does all the query planning and query optimization based on data profiles that are collected automatically as the data is loaded. It automatically collects and maintains the required statistics to determine how to most effectively distribute the data and queries across the available compute nodes.

So, no indexes, no need to figure out partitions and partition keys, no need to pre-shard any data for even distribution, and no need to remember to update statistics.

Another key point to remember is that not only does this dynamic optimization auto-tune queries that work against traditional structured data, it also works against semi-structured data (like JSON, Avro, or XML). When semi-structured data is loaded into our VARIANT data type, as mentioned in an earlier blog post, the data is profiled and columnarized behind the scenes. With this approach, querying the data (using our JSON or XML extensions) is optimized just like for structured relational data, without having to spend the time and effort to split it out into relational tables and columns. Because of this unique approach to handling semi-structured data, Snowflake not only supports joining structured and semi-structured data with SQL, it optimizes those joins too.

Let go Luke – Trust the Force!

As you can imagine, there are some folks out there who do not like change and want us to add more knobs so they can manually tune their queries.

Why? Well, they say that they know their workload better than Snowflake and therefore can optimize it better than Snowflake software. Others say that they’ve already done all the work to optimize another platform for their workload and don’t want to “waste” all that time and effort.

Since I come from a traditional data warehouse background, and know many expert data warehouse architects who are world class at tuning specific databases to do specific types of queries,  I definitely understand and can sympathize with this point of view.

But the world of data has changed! The way we build, deploy, and use data (of all kinds) is continually evolving and we need to evolve our approach to to be successful.

Because of that, there are a few things to consider with regard to tuning:

  1. Your workloads will change.
  2. The sheer number of different workloads is going to grow faster than your bandwidth to manually tune (so someone has to prioritize who wins).
  3. What happens if your expert query tuner decides to sell real estate instead?

Relying on yourself, and other expert DBA tuning staff, is ultimately going to result in a performance tuning bottleneck, which will in turn lead to increased contention for resources in the data warehouse. No matter how good you are, in the end you will not be able to keep up. So why not use a data warehouse service to handle that work for you so you can focus on getting the business people access to all this data in a form they can use to make better business decisions? In short, people are the hardest thing to scale in any data analytics solution. Thus any solution that allows you to scale without bottlenecks on people is a winner.

And if the query is still not fast enough, with Snowflake you always have the option to resize your virtual warehouse to add additional compute resources, as we described in the post about elasticity. Without the benefit of a dynamic optimization engine, you could not change the compute configuration on the fly and see immediate improvements. In a many other environments, if you changed the number of compute nodes you would have to redistribute the data, or do some other manual tuning, in order to get the improved performance. That would not be elastic at all.

For all these reasons, our patent-pending dynamic optimization is, to me, one of the most important features when it comes to making the Snowflake Elastic Data Warehouse a true SaaS offering and enabling agile, high-speed analytics at any scale. As part of our vision, we developed an offering that helps customers concentrate on understanding and deriving value from their data rather than spending time refining, managing , and optimizing the environment. Business users and data scientists can easily analyze their data by connecting natively to Snowflake through various tools, and not have to worry about tuning and query optimization.

As I said before, as an architect and data warehouse designer, I really like this model!

End of this trail…more to come

Well, that is the last of my Top 10 list of cool features in Snowflake.  I want to thank Saqib Mustafa for all his help collaborating on this series of posts.

There are of course many other cool features in Snowflake, and more to come in the future. So keep your eyes on this blog for future posts with more details on the Snowflake Elastic Data Warehouse, the cloud-native data warehouse as a service.

As always, keep an eye on our Snowflake-related Twitter feeds (@SnowflakeDB), (@kentgraziano), and (@cloudsommelier) for continuing updates on all the action and activities here at Snowflake Computing.

Additional Links

  • Understanding Snowflake Utilization: Query Profiling
Share

Cloud Data Lakes for Dummies

Get insights fast from all your data.

Read More

10 Best Practices Every Snowflake Admin Can Do to Optimize Resources

There are a few optimizations every Snowflake account admin should make and some advanced methods they should consider as...

Delve into the details
全文を読む

Custom SQL Now Supported in the Snowflake Connector for Power BI!

Learn how Custom SQL Support unlocks time savings, flexibility, and various new capabilities for Power BI and Snowflake...

More
全文を読む

Why Legacy SIEM Fails with Large Data Sets

Using modern SIEM solutions offers a significant competitive advantage over legacy solutions that haven’t kept pace with today’s rapidly evolving cybersecurity landscape.

Have a look
全文を読む

Accelerating the Pace of Digital Transformation in Banking

Let’s explore digital transformation in banking and see how today’s financial institutions are leveraging cloud technologies to meet a range of challenges.

Expand your knowledge
全文を読む
Snowflake Inc.
  • プラットフォーム
    • メディア&エンターテインメント
    • アーキテクチャ
    • 価格
    • Snowflakeデータマーケットプレイス
  • ソリューション
    • 医療・ライフサイエンスのためのSnowflake
    • 金融サービスのためのSnowflake
    • マーケティング分析のためのSnowflake
    • 小売業のためのSnowflake
    • 教育のためのSnowflake
  • リソース
    • リソースライブラリー
    • ウェビナー
    • 資料
    • コミュニティ
    • 法務
  • もっと知る
    • ニュース
    • ブログ
    • トレンド
  • 企業情報
    • Snowflakeについて
    • 経営陣と取締役会
    • パートナー
    • 求人
    • お問い合わせ

Sign up for Snowflake Communications

Thanks for signing up!

  • Privacy Notice
  • Site Terms
  • Cookie Settings

© 2023 Snowflake Inc. All Rights Reserved