Registering or updating table metadata for Snowflake tables or views can sometimes perform poorly.

Performance could improve when you do either of the following:

• Limit the number of tables in a single table registration or update action.
• Enable the CLIENT_METADATA_REQUEST_USE_CONNECTION_CTX=TRUE Snowflake connection option. 

Cause

Investigation revealed that the performance bottleneck was not related to data retrieval, table size, or SAS CPU utilization. Instead, the majority of the elapsed time was spent during Snowflake metadata operations performed by the METALIB procedure, such as table and index discovery (SQLTables and SQLStatistics calls).

Recommendations

Enabling CLIENT_METADATA_REQUEST_USE_CONNECTION_CTX=TRUE allows Snowflake metadata requests to leverage the active database and schema context, reducing the overhead associated with metadata lookups.

During testing, the following Snowflake LIBNAME options provided the best observed registration performance:

SCANSTRINGS=NO
PatternMatchNoWildcard=1
CLIENT_METADATA_REQUEST_USE_CONNECTION_CTX=TRUE

Based on the tests, SAS recommends that you retain these options for Snowflake libraries that will be registered or refreshed using PROC METALIB. Note that actual performance might vary depending on the number of tables, schema complexity, and Snowflake environment.