Table Loader transformation (SAS DI Studio on SAS 9.4) - slow on loading to Oracle

[strsljen]

Hi,

I have 50 million obs output data set to be written back to Oracle table. 

Using Table Loader transformation and can't ignore the fact that it is incredibly slow.

Table has 5 attributes, indexes dropped.

Using default proc append.

If someone can suggest some tuning tips for Table Loader, I would really apretiate it.

Thanks!

[Patrick · Accepted solution]

@strsljen

First thing I'd check: Option settings for INSERTBUFF and DBCOMMIT

If changing these options to something better than the installation default doesn't help: Consider using bulk load (also managed via options).

The following discussion might also be useful for you (as well as the link to a tuning paper in there).

https://communities.sas.com/t5/SAS-Data-Management/The-SQL-tuning-checklist/m-p/438922#M13536

[strsljen]

Adding INSERTBUFF=16000 helped a lot. 

I also set BUFNO to 50.

That reduced load time from 40min to 1min25sec.

Thanks!

Best regards,

[Patrick]

@strsljen

If there isn't any special reason to do otherwise then I normally set buffno to zero. This way there's only a single commit once all the data got loaded.

[strsljen]

Thanks Patrick! Will set it to MIN (default) and re-run the load to see if the load time is not significally worse.

[Patrick]

@strsljen

Not to min but literally to zero: dbcommit=0

Besides of performance this also ensures that you'll never end up with a partially loaded table but it's either all or nothing - which in my opinion makes it easier to fix things and rerun as you don't have to care about backing out partially loaded data.