topic Re: Table Loader transformation (SAS DI Studio on SAS 9.4) - slow on loading to Oracle in SAS Data Management

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

strsljen — Fri, 16 Mar 2018 12:51:57 GMT

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!

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

Patrick — Fri, 16 Mar 2018 23:58:02 GMT

@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

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

strsljen — Mon, 19 Mar 2018 12:16:42 GMT

Adding INSERTBUFF=16000 helped a lot.

I also set BUFNO to 50.

That reduced load time from 40min to 1min25sec.

Thanks!

Best regards,

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

Patrick — Tue, 20 Mar 2018 05:53:55 GMT

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

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

strsljen — Tue, 20 Mar 2018 07:29:05 GMT

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

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

Patrick — Tue, 20 Mar 2018 07:45:40 GMT

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