12-03-2015 06:27 AM
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...
(@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.
12-03-2015 07:15 AM
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?
12-03-2015 09:43 AM
12-04-2015 10:04 PM
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.