Juletip #14 - Run SAS Studio Flows in-database by changing location for temporary tables

[jonaslie]

Do you want your flows to run as fast as Santa’s sledge?  

In the latest release of Viya, 2023.11, SAS released a functionality for changing the location the temporary tables in a flow to a database schema instead of the default SAS Work location. This feature makes heterogenous joins that joins data from two different databases extremely much faster.

Quite often you want to join a small new dataset with a bigger existing dataset, and you don’t won’t to explicit store the new data into the database. The two most important reasons are:

1. In Data Integration Studio, users usually never named temporary tables explicit, and you don’t won’t to go into all your migrated jobs and add the tables to make them run fast
2. You just want to keep you flow slim and clean in order to save development time and make your flow more flexible

So how does this work? Let us take an example having a table in snowflake with a bit more than 1 million rows and want to join them with a new smaller dataset extracted from another database as illustrated below.  

Here in this example, we need to read all the rows of the id column from snowflake to compute to execute the join and write the output dataset to SAS work. Execution time 1 minute and 28 seconds.

Let us see how this goes after enabling the new feature for temporary tables, storing them in the tmp schema in snowflake. You configure this under options for the flow on the right side of the screen.

The new execution time is 5:76 seconds.

I encourage you all to use this feature when migrating your SAS code and jobs to Viya and your new cloud database. This works for all supported databases, both traditional ones like Oracle, MS SQL, DB2 and PostgreSQL and newer ones like Snowflake, Singlestore, BigQuery and Synapse.

If you want the tables to be temporary and deleted when your session finish, use the temporary table option on the TMP libname as illustrated below. Doing this, the table is only visible in the flow, not in the schema from another session/connection.

libname SNOWFL snow server="saspartner.snowflakecomputing.com"  connection=global .. more option

libname TMP snow server="saspartner.snowflakecomputing.com"  connection=global dbmstemp=yes .. more options

See the sas documentation for a full example of the use of temporary tables: SAS Documentation

Good luck with speeding up your Viya flows! We will release similar functionality for SAS code (proc sql/fedsql and datastep) sometime next year.