BookmarkSubscribeRSS Feed
mrtball
Obsidian | Level 7

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.

4 REPLIES 4
LinusH
Tourmaline | Level 20

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?

Data never sleeps
mrtball
Obsidian | Level 7
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,
Patrick
Opal | Level 21

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. 

LinusH
Tourmaline | Level 20
Use SASTRACE debug - the SQL Server SQL might give some explanation?
Data never sleeps

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
  • 4 replies
  • 1227 views
  • 1 like
  • 3 in conversation