CODEWITHSUNDEEP
sas
Raven
Raven

Reputation: 859

Reading large MSSQL tables from SAS efficiently

I'm working with some rather large tables in SQL Server (over 47,000 MB) and have explored ways to import the data into SAS quicker.

I started off with the following code which takes a long time:

  LIBNAME SQL ODBC DSN='SQL Server' user=EMTCED pw=HdPS1eMtc9 schema=dbo; 

  data ED_TAT;
  set sql.ED_TAT_Master;
  run;

In doing a little bit of research, I found that (BULKLOAD=yes BL_METHOD=CLILOAD) options should provide some relief; however, when adding this to my code, I get the following error. 

  LIBNAME SQL ODBC DSN='SQL Server' user=EMTCED pw=HdPS1eMtc9 schema=dbo; 

  data ED_TAT (BULKLOAD=yes BL_METHOD=CLILOAD);
  set sql.ED_TAT_Master;
  run;


  ERROR 22-7: Invalid option name BULKLOAD.

Is there something I'm missing or another option that will work better? Thanks!

Upvotes: 2

Views: 937

Answers (1)

Nickolay
Nickolay

Reputation: 32063

BULKLOAD=yes is for uploading data from SAS. Reading data into SAS should not require special flags.

47 GB in two hours seems slow, but I'm not sure if you'll find someone who's willing to help you troubleshoot it in an online forum. Did you try to find the bottleneck? Do you have enough network bandwidth? Does querying the same table in MSSQL work faster? Do you see the SAS process max out the CPU or wait for I/O?

If we treat the transfer speed as a given, you can try reducing the amount of data downloaded:

  • filter the rows with where as suggested by @Reeza
  • keep only the columns you need
  • or use the SQL Pass-through facility to process the data without reading it into SAS or to pre-process data on the SQL Server and further reduce the amount of data you have to download.

You may see bad performance if you have lots of varchar columns: SAS character columns are fixed length, so the SAS process will end up writing lots of null bytes unless you set COMPRESS=yes on your output data set. I'm not sure, but it might have effect on the network too, so you could try using substring() in the SQL pass-though to limit the amount of data SAS will have to process.

Upvotes: 2

Related Questions