Truncate Table CAS Action

[#1]

Sometimes it feels like there's a CAS action for everything ("There's a CAS action for that!"). However, they're not always named or formed like you might expect. For example, to create a match code you might need to use the DATA Step CAS action, and, to load a CAS table into a database, you might need to use the SAVE action.

If you're a database guy, you might expect CAS to have an explicitly named "Truncate" action but it does not. Starting back in 2021.1.2, CAS supports table trunctation with the DeleteRows action. To trigger truncation you specify a WHERE clause with an (always) TRUE condition, i.e. WHERE = '1'. ("Delete Rows Where True." Since the WHERE clause always resolves to TRUE, every record gets deleted. Get it?) So truncating a CAS table looks like this:

proc cas;
table.deleterows / 
  table={name="test" caslib="DM" where='1'};
run;

How is this different than just deleting all of the table's rows? You could do that before with the DeleteRows action. The difference boils down to performance. Like any other data processing task, deleting rows from a table can be process intensive. CAS has to locate the records according to WHERE conditions and mark each as logically deleted. Truncation minimizes the impact on system resources by manipulating the table definition instead of the table data. Using CAS' internal table management mechanisms, truncation marks the table as empty and frees the storage space instead of processing the table records. Thus truncation can be orders of magnitude faster than deleting all of the rows.

How much faster is truncating versus explicitly deleting all a CAS table's records? Let's look...

In this example, we have an ~80 million row CAS table. First we'll delete all of the rows with a data value subsetting WHERE clause and then we'll truncate the table. As you can see from the screen shots, deleting all of the rows takes around 6 minutes while truncating the table takes around 6 seconds.

Data Value Driven Row Deletion

Select any image to see a larger version.
Mobile users: To view the images, select the "Full" version at the bottom of the page.

WHERE=1 Clause Triggering Truncation

And, why do we care about table truncation? For near-real time CAS table updates of course, namely using the Truncate and Append method.