Hello,
We have recently been re-investigating the performance of our SAS ETL processes and have observered that when usign a Modify by Index table loader to load data this performs quicker when Bulk Load=No.
From oberserving activity within SQL Server when bulk load is enabled the query looks to do some processing but then get blocked by another query that is part of the same job in the format...
<?query --
(@Param000004 int,@Param000005 int,@Param000006...
This blocking query does not start when Bulk Load is turned off on for the library within SAS.
So far i can only see bulkload having a negative impact, however I was wondering if optimisation settings such as Block Insert Buffer or anyother settings need to be optimised to ensure that the bulkload works correctly. I've run tests varying/increasing Block Insert Buffer but this behaviour still happens. Exisiting ETL's were setup to use bulk load=yes so just trying to clarifiy as to why this would have a negative impact? And why would it be set to yes if it does.
This is currently SAS 9.3 as we are in the middle of migrating DI Studio ETL processes to SAS 9.4.
Thank you for any responses in advance.
Frankly, I haven't given this situation much thought.
I believed in some kind of magic way bulk-load wqsn't used for update operations.
Clearly, bulkload has it's benefits when it comes to append/insert operations.
If you don't fins any other convenient way, setting bulkload=yes as a data set option may be an alternative?
I believe "bulkload=yes" is a dataset option not a library option. Not sure why you would need to create a separate library only to avoid bulkloading. It's something you can set directly in the DIS transformations as well.
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.