Bulk loading

[Wiki-Ben]

Hi,

We are looking to improve data inserts in a SQL Server database (ODBC). In the SAS server connection, we have the advanced option to set 'Bulk Load' to Yes:

but we can also set this advanced option in the ODBC setup.

Do we have to set it in both?

Thx.

[SASKiwi]

You should also try setting the INSERTBUFF option as the default value is not optimal. Try INSERTBUFF = 10000 as a starting point but feel free to experiment with higher or lower values.

[LinusH]

I don't have the "Bulk Load" tab in my advanced settings, probably because we are using SAS/ACCESS to SQL Server, not ODBC. I'm on 9.4M8 Linux.
Not sure what you refer to when you say "advanced option in ODBC set up". Do you mean ODBC Manager (Ican't fins an advanced option here)?
I think it should be enough to set it on the libname, the important thing is to generate the BULKLOAD=YES when your LIBNAME statement is being generated.

[Patrick · Accepted solution]

@Wiki-Ben The screen shot looks like DI Studio. Correct?

I've asked Copilot 365 about performance when using bulkload=yes with SAS Access to ODBC and the answer was interesting (though don't know if correct):

So just based on the Copilot answers, it's likely better to go with what @SASKiwi proposed (increase the insertbuff value, set dbcommit=0).
If you are using SAS Access to SQL Server then of course bulkload=yes could might make a difference, especially when loading into an empty table.

Bulkload=yes can be a libname or even a dataset option and though I would assume setting it in DIS will be sufficient on any level.
 

[Wiki-Ben]

Thank you all for your input,

@Patrick Used with SAS code (Libname/proc sql/data xxx) SAS Help Center: INSERTBUFF= Data Set Option

I guess test and trial will be the key here.

Thank you again,

Yvan