page38
18
this optional clause is used for aggregations only.
GROUP BY
is used only with aggregate queries.
HAVING Clause
A
HAVING
clause is permissible only in conjunction with a
GROUP BY
clause, to filter
results. A common use case is to filter by a specific count to identify duplicate records.
this optional clause is used for filtering aggregated data.
HAVING
is used only with aggregate queries.
SELECT Clause
With your subset of objects, attributes, and data identified, any single row or aggregate
expressions within the
SELECT
statement are computed.
The optimal number of attributes for
SELECT
statements is 10 or fewer. This figure
was disclosed during a discussion with Snowflake staff. Wide tables with many rows do
not perform particularly well, and the use of temporary tables was also suggested as an
appropriate intermediate step.
Through hands-on experience, you found
SELECT *
performance to be suboptimal
across a wide range of queries migrated from legacy RDBMS. I therefore strongly
recommend explicit attribute references.
always refactor
SELECT *
to reference explicit attribute names.
One exception is that I have found
SELECT *
with an appropriate
LIMIT
acceptable
when sampling data as a pre-cursor to assist the development process.
As with everything performance related, test, test, and test again. Don’t rely upon
what you read either here or elsewhere, but instead prove it empirically. Your experience
using real-world conditions is what matters.
ChaptEr 1
tuning thE SnowFlakE Data ClouD