topic Re: Load data to volatile table in teradata in SAS Programming

How to load data to volatile table in teradata?

nickspencer — Fri, 04 Jun 2021 20:53:42 GMT

I have a sas dataset with around 80k records. I have to join/process this dataset with few teradata tables. But these teradata tables are huge with about 40 million records in each table.
Since I do not have access to create tables in teradata, I am creating volatile table and appending this dataset to volatile table but the problem is, it is taking too long to load/append this dataset to the volatile table. There are about 80k records and 30 columns. It takes about 2 hour in night but when I run in daytime it never completes.
I am using multistmt=yes tpt=no option (fastload/mutliload does not work with volatile table) but it is not helping at all. Please advice on what might be the right approach ?

Thanks,
Nick

Re: Load data to volatile table in teradata

SASKiwi — Mon, 09 Nov 2020 06:42:01 GMT

Usually you need to tune database load options to get good loading performance.

The two to try are DBCOMMIT: https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.5&docsetId=acreldb&docsetTarget=p00lgy3xwh61b8n16kffwq3veagu.htm&locale=en

and INSERTBUFF: https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.5&docsetId=acreldb&docsetTarget=p06u7eb5ienq5in1wq9injrz8vh5.htm&locale=en

Re: Load data to volatile table in teradata

nickspencer — Mon, 09 Nov 2020 16:29:19 GMT

Does insertbuff work with teradata ?

Re: Load data to volatile table in teradata

SASKiwi — Mon, 09 Nov 2020 20:06:54 GMT

Here is the Teradata-specific doc: https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.5&docsetId=acreldb&docsetTarget=p0gy4y5g1dy090n18jq1riw5g8d5.htm&locale=en

It looks like INSERTBUFF isn't available but DBCOMMIT definitely is.

Re: Load data to volatile table in teradata

nickspencer — Mon, 09 Nov 2020 21:02:54 GMT

I tried dbcommit=0 but it has not helped at all. It takes forever to run the append step.
Is there any other way to speed up the append process ?

Re: Load data to volatile table in teradata

Tom — Mon, 09 Nov 2020 21:06:42 GMT

Upload the data to a real table and then copy it to the volatile table.

Then you can use FASTLOAD/TPT options.

Re: Load data to volatile table in teradata

nickspencer — Mon, 09 Nov 2020 21:32:30 GMT

Tom, we do not have the ability to use the real table in production. I tried multistmt=yes tpt=no but it is not helping much. Are there any other ways loading to volatile table can be made faster?

Re: Load data to volatile table in teradata

SASKiwi — Tue, 10 Nov 2020 00:42:46 GMT

Try DBCOMMIT = 1000, 2000, 3000 etc. What method are you using? PROC APPEND?

Re: Load data to volatile table in teradata

nickspencer — Tue, 10 Nov 2020 03:39:39 GMT

@SASKiwi, I am using the proc append

Re: Load data to volatile table in teradata

SASKiwi — Tue, 10 Nov 2020 03:54:53 GMT

@nickspencer - The DBCOMMIT default for PROC APPEND is 0 so there is no point trying that value. As already stated, try 1,000, 2,000, 3,000 etc to see if it helps.

Failing that I suggest trying Tech Support.

Re: Load data to volatile table in teradata

ChrisNZ — Tue, 10 Nov 2020 05:01:01 GMT

1. Do you use the ODBC driver or the native SAS/Access product?

2. I think the maximum query length is 1MB, so depending on the key length, maybe just add the 80,000 values to the query to only bring back the records you need.

Re: Load data to volatile table in teradata

Tom — Tue, 10 Nov 2020 05:10:24 GMT

@nickspencer wrote:
Tom, we do not have the ability to use the real table in production. I tried multistmt=yes tpt=no but it is not helping much. Are there any other ways loading to volatile table can be made faster?

Don't load it to THE real table. Load it to A real table. If you don't have a database you can write into ask your DBA to create one. Either a personal database or a team database.

Something like this:

libname &permlib teradata .... ;
proc append data=&in
  base=&permlib..&permname (fastload=yes dbcreate_table_opts='no primary index')
;
run;
proc sql ;
  connect using &permlib ;
  execute by &permlib
   (create multiset volatile table "&out" as
       (select * from "&permdb"."&permname")
        with data
        on commit preserve rows
   );
  execute by &permlib
    ( drop table "&permdb"."&permname" )
  ;
quit;