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!
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
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
Adding INSERTBUFF=16000 helped a lot.
I also set BUFNO to 50.
That reduced load time from 40min to 1min25sec.
Thanks!
Best regards,
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.
Thanks Patrick! Will set it to MIN (default) and re-run the load to see if the load time is not significally worse.
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.