BookmarkSubscribeRSS Feed
Anotherdream
Quartz | Level 8

Hello all. I have numerous pieces of code that write data into a sql server database from SAS. I usually do this by using simple proc sql steps to either create a table, or insert into a table.

It was recently brought to my attention that when sas does this, it is actually inserting data row by row, and this is why this process is so slow. They instead suggested the bulk load facility within SAS/Access that writes data to a flat file, and then loads the data into sql server from this flat file. However when reading online I am very confused between the different between this process, and using the bulkload=yes option in a libname statment? 

Is there a more efficient way to load data into a sql server table (from a sas licence running on a local machine in the same geographical location as the sql server server) then specifying the bulkload=yes option in the libname, as below?

libname consent odbc dsn=my_defined_odbc_connection schema=dbo bulkload=yes DBMAX_TEXT=32000;

proc sql;

create table consent.mytable as

select *


from sasworktable

quit;

run;

Is the dataset option bulkload a more efficient way to load data into the server? (as my second example?), or are thees inherently doing the same thing?

libname consent odbc dsn=my_defined_odbc_connection schema=dbo  DBMAX_TEXT=32000;

data consent.mytable(bulkload=yes);

set sasworktable;

run;

THanks for all your help!

Thanks for your help!

Brandon

8 REPLIES 8
SASKiwi
PROC Star

A suggest you also look at trying the DBCOMMIT =  and INSERTBUFF = options on your LIBNAME statement. These can be used instead of BULKLOAD. The default for DBCOMMIT is 1000 which typically is not very efficient. Try 10,000 and 100,000 and see if there is a significant performance improvement.

TomKari
Onyx | Level 15

Although it is impossible to say with certainty that bulk loading will be significantly faster, as it depends on cases, in some testing I did with another DBMS it was a dramatic improvement. It's certainly worth testing.

Tom

OS2Rules
Obsidian | Level 7

We usually run a truncate on the DBMS data and then a PROC APPEND to reload the entire table content.  Of course this would depend on the size of the database.  I find that this runs a lot faster for the databases that we deal with which aren't too large.

Anotherdream
Quartz | Level 8

So I am dealing with data that is only a few thousand rows, however there are over 80 tables, and each table has about 200-300 columns, of which the average length is > 3,000 varchar text length. These are the tables I would like to load in a much quicker fashion.

WHen using proc append to the library, do I need to define bulkload on the libname statment?

Also SAS Kiki and Tom. The issue I have is why would any of these method be inherently more efficient? I'd like to understand how bulk-loading works when defined in the Libname statement compared to the other options.

Ex:

LIbname mylib odbc dsn='mydsn' bulkload=yes;

Does this line attempt to use the databases bulk load tool? I do not believe it does, because we are not creating a flat file from with the bulk load can read from. Or am I mistaken in this?

Tom
Super User Tom
Super User

I have no experience with SQL server, but for Oracle and Teradata SAS will insulate you from the details of how it performs the bulkload.  It can generate the text files and call the bulkloading utility from the database vendor.  I suggest doing some testing using your actual data and server.  You might also want to look at other settings such as block size.

TomKari
Onyx | Level 15

If you don't use buk loading, SAS will execute an INSERT SQL statement for every record it loads. This is almost always the slowest way to add large quantities of data.

In the case where I tested bulk loading with another DBMS, SAS did indeed unload the data to a flat file, and then run the DBMS's bulk loading facility. I was able to watch the flat file grow in one of the SAS utility libraries.

It is very much worth giving a try.

Tom

Anotherdream
Quartz | Level 8

So I am using bulkload=yes in my libname statement (as defined below)

libname consent odbc dsn=my_defined_odbc_connection schema=dbo bulkload=yes DBMAX_TEXT=32000;

Is there a way to determine definitively if my system is using the systems bulkload facility? Aka how would I find SAS utility library that it would load the flat file too, and then load into the system? I guess my main question is how to be sure that the bulkload is actually doing a bulkload (what if the sql server database doesn't have the bulkload facility set up, or if it has permission issues that I don't have access too, etc..).

TomKari
Onyx | Level 15

Two things you can do:

1. Check out the SASTRACE system option. It will log the calls that SAS makes to the DBMS. So, try it without the bulk load option, and fiddle with it 'till you see a bunch of INSERT statements. Then, when you're using the bulk loader, you shouldn't see them any more. I'm not sure if bulk loader SQL is written to this or not.

2. Run the following statement:

libname work list; run;

You'll see something like this:

NOTE: Libref=   WORK

      Scope=    IOM ROOT COMP ENV

      Engine=   V9

      Access=   TEMP

      Physical Name= C:\Users\user\AppData\Local\Temp\SEG2116\SAS Temporary Files\stuff\Prc2

      Filename= C:\Users\user\AppData\Local\Temp\SEG2116\SAS Temporary Files\stuff\Prc2

Somewhere around that directory is probably where SAS will write the flat file that it will feed to the loader. If you transfer a big enough file, you'll probably be able to see it growing. Note that you need to do the "libname work list" in the same SAS session as you do the bulk load; this directory changes with every SAS session.

Good luck,

  Tom

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 12465 views
  • 1 like
  • 5 in conversation