topic Re: Get data into sql server more efficiently? in SAS Programming

Get data into sql server more efficiently?

Anotherdream — Tue, 04 Jun 2013 23:24:18 GMT

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

Re: Get data into sql server more efficiently?

SASKiwi — Wed, 05 Jun 2013 00:56:03 GMT

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.

Re: Get data into sql server more efficiently?

TomKari — Wed, 05 Jun 2013 15:06:36 GMT

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

Re: Get data into sql server more efficiently?

OS2Rules — Wed, 05 Jun 2013 15:20:04 GMT

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.

Re: Get data into sql server more efficiently?

Anotherdream — Fri, 07 Jun 2013 18:18:27 GMT

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?

Re: Get data into sql server more efficiently?

Tom — Fri, 07 Jun 2013 18:49:39 GMT

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.

Re: Get data into sql server more efficiently?

TomKari — Fri, 07 Jun 2013 19:38:05 GMT

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

Re: Get data into sql server more efficiently?

Anotherdream — Fri, 07 Jun 2013 20:49:53 GMT

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..).

Re: Get data into sql server more efficiently?

TomKari — Fri, 07 Jun 2013 22:12:22 GMT

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