How to load data to volatile table in teradata?

[nickspencer]

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

[SASKiwi]

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=p00l...

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

[nickspencer]

Does insertbuff work with teradata ?

[SASKiwi]

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

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

[nickspencer]

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 ?

[SASKiwi]

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

[nickspencer]

@SASKiwi, I am using the proc append

[SASKiwi]

@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.

[Tom]

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

Then you can use FASTLOAD/TPT options.

[nickspencer]

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?

[Tom]

---

@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;

[ChrisNZ]

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.