page44
25
© Andrew Carruthers 2024
A. Carruthers, Tuning the Snowflake Data Cloud, https://doi.org/10.1007/979-8-8688-0379-6_2
CHAPTER 2
The Query Optimizer
Query optimizers reduce the cost of queries while retaining the original intended
functionality. Furthermore, query optimization seeks to reduce the volume of data
accessed, further reducing costs.
A lot happens within the Snowflake query optimizer, and not every detail is known to
the wider user community. I have drawn upon available sources to piece together what
can be shared, although the optimizer behavior might have changed by the time you
read this as a natural consequence of development and maintenance. However, there is
value in reading this chapter as I hope it will help shape your thinking when designing
SQL statements.
Building upon the information presented in Chapter 1, in this chapter I discuss
various aspects of the query optimizer beginning with the lifecycle of a query. I then
move on to discussing what happens within the planner and optimizer, a fascinating
subject in its own right.
The key message for this chapter is to adopt the KISS principle, better articulated
here:
https://en.wikipedia.org/wiki/KISS_principle
. The same principle is equally
well stated by Tony Robbins: “Complexity is the enemy of execution” (
https://www.
youtube.com/watch?v=o0PweQFmJpI
). We will return to this theme frequently.
A guiding principle I use for determining the quality of code is to see how cleanly
written and laid out each SQL statement appears. If the SQL looks good, is well
formatted, and is readable, then it is most likely the developer has taken great care to
ensure optimal execution performance. You should not lose sight of how long it takes to
refactor code; all good developers hate cleaning up other people’s mess.
For those just starting out: help those who support your code by delivering high-
quality, easily understood, and well-documented artifacts.