SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

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

Accepted Solution Solved
Reply
Contributor
Posts: 27
Accepted Solution

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

[ Edited ]

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!

 

--
Mario

Accepted Solutions
Solution
‎03-19-2018 08:15 AM
Respected Advisor
Posts: 4,679

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

[ Edited ]

@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

 

View solution in original post


All Replies
Solution
‎03-19-2018 08:15 AM
Respected Advisor
Posts: 4,679

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

[ Edited ]

@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

 

Contributor
Posts: 27

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

Adding INSERTBUFF=16000 helped a lot. 

I also set BUFNO to 50.

That reduced load time from 40min to 1min25sec.

 

Thanks!

 

Best regards,

 

--
Mario
Respected Advisor
Posts: 4,679

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

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

Contributor
Posts: 27

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

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

 

--
Mario
Respected Advisor
Posts: 4,679

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

[ Edited ]

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 173 views
  • 0 likes
  • 2 in conversation