2024 Performance Improvements¶

Expands top-K pruning to include queries that contain aggregate functions.

Search Optimization Update: Support for join queries. (General Availability)

Improves the performance of join queries that have a small number of distinct values on the build side of the join.

Reduces the time spent in the SECONDARY_UPLOADING_INVENTORY, PRIMARY_UPLOADING_METADATA, and SECONDARY_DOWNLOADING_METADATA phases of a replication refresh by optimizing serverless compute allocation. This improvement targets refreshes with larger metadata sizes.

Reduces compilation and execution time for queries that use a LIMIT clause to return n rows from a table. This optimization shrinks the partitions that are scanned to cover only the first n rows.

Improved warehouse utilization for queries that scan only a small amount of micro-partitions when compared to the compute resources that are available to the virtual warehouse.

Faster execution for queries with expensive operations when scanning data from a small number of micro-partitions, which is common in BI and dashboard use cases.

Improved query processing that:

• Pushes down LIMIT clauses into aggregation nodes that do not contain any aggregations besides the ANY_VALUE function.
• Eliminates redundant grouping keys when PRIMARY KEY or UNIQUE constraints are enforced by validation, or when the RELY constraint property is used.

• Reduces query execution time and improves scan performance for queries that access columns that contain NULL values.
• Provides better scan performance by decoding numbers more efficiently when reading data from remote storage.

Reduces the time spent in the SECONDARY_UPLOADING_INVENTORY and SECONDARY_DOWNLOADING_METADATA phases of a refresh operation by optimizing the synchronization of some objects and the authorization mechanism for replication operations.

Reduced the latency for loading most Parquet files by up to 50% when the file format option, USE_VECTORIZED_SCANNER, is set to TRUE.

The vectorized scanner is well suited for the columnar format of a Parquet (https://parquet.apache.org/docs/file-format/) file and reduces the ingestion latency by downloading only relevant sections of the Parquet file into memory, such as the subset of selected columns.

Reduces query execution time for complex queries with aggregations by reducing the amount of data that needs to be processed at the earliest point possible.

Reduces execution time for top-k queries due to fewer scanned files and file header reads. Expands existing top-k improvements to include STRING/BINARY support in ORDER BY columns. Further increases pruning efficiency by sorting the scan set in order of largest/smallest files with respect to the value domain.

Improves performance for Streamlit in Snowflake apps (including Streamlit apps within a Snowflake Native App), Python worksheets, Python UDFs, and stored procedures in Python.

Reduces query execution times by improving scan performance in a variety of scenarios such as highly concurrent queries running on a warehouse.

Reduces the time it takes for query results to appear when run in Snowsight. Improvements are most noticeable for queries that return result sets larger than 10,000 rows.

Reduces the time spent in the PRIMARY_UPLOADING_METADATA, SECONDARY_DOWNLOADING_METADATA, and SECONDARY_UPLOADING_INVENTORY phases for metadata.

Reduces the time spent in the PRIMARY_UPLOADING_METADATA, SECONDARY_DOWNLOADING_METADATA, and SECONDARY_UPLOADING_INVENTORY phases of a refresh operation by optimizing portions of the snapshot operation and the way some objects are added to the replication inventory.

Support for the upper and lower collation specifications added to some functions.

Ability to set the upper and lower collation specifications for some functions. The upper and lower collation specifications perform better than the ci specification for some use cases. The upper and lower collation specifications are now supported for the following functions: CHARINDEX, CONTAINS, ENDSWITH, POSITION, SPLIT, SPLIT_PART, and STARTSWITH. For more information, see Differences between `ci` and `upper` / `lower`.

Reduces execution time for queries that use a count of 0 with LIMIT, which is often used by applications to return column headings and data types for query results.

General Availability of larger warehouses (5X-LARGE and 6X-LARGE) in Microsoft Azure regions, excluding Azure Government regions.

Ability to use larger compute resources for memory-intensive queries compared to smaller warehouses.