BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
strsljen
Obsidian | Level 7

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
1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

@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

5 REPLIES 5
Patrick
Opal | Level 21

@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
Obsidian | Level 7

Adding INSERTBUFF=16000 helped a lot. 

I also set BUFNO to 50.

That reduced load time from 40min to 1min25sec.

 

Thanks!

 

Best regards,

 

--
Mario
Patrick
Opal | Level 21

@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
Obsidian | Level 7

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

 

--
Mario
Patrick
Opal | Level 21

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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