topic Re: SAS DI Jobs - OLEDB / SQL Server - Bulk Load=Yes Negative Impact - Blocking Query in SAS Data Management

SAS DI Jobs - OLEDB / SQL Server - Bulk Load=Yes Negative Impact - Blocking Query

mrtball — Thu, 03 Dec 2015 11:27:31 GMT

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.

If bulkload=no a test job will take around 2 minutes.
If bulkload=yes a test job will run beyond 20minutes.

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.

Re: SAS DI Jobs - OLEDB / SQL Server - Bulk Load=Yes Negative Impact - Blocking Query

LinusH — Thu, 03 Dec 2015 12:15:58 GMT

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?

Re: SAS DI Jobs - OLEDB / SQL Server - Bulk Load=Yes Negative Impact - Blocking Query

mrtball — Thu, 03 Dec 2015 14:43:10 GMT

Hello Linus,

Thank you for the response, I think your points have highlighted a few things I'd not considered in particular the fact the table loader is updating the majority of the records in this instance and not inserting.

Having caught up with our SQL Server DBA team they have also highlighted that as the volumes we are processed are only 700k they don't believe BulkLoad is required anyway, so we will consider potentially creating a separate library with Bulkload turned off for this 1 table for the moment.

I just wonder if there is some efficiency issue that is caused when combining BulkLoad=Yes and Modify By Index (or Columns) on a table loader which when communicated to SQL Server via the SAS/ACCESS OLDEB engine it doesn't quite convert to an efficient method of processing data.

Thanks,

Re: SAS DI Jobs - OLEDB / SQL Server - Bulk Load=Yes Negative Impact - Blocking Query

LinusH — Thu, 03 Dec 2015 21:35:42 GMT

Use SASTRACE debug - the SQL Server SQL might give some explanation?

Re: SAS DI Jobs - OLEDB / SQL Server - Bulk Load=Yes Negative Impact - Blocking Query

Patrick — Sat, 05 Dec 2015 03:04:13 GMT

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.