When you use SAS/ACCESS Interface to PC Files in the UNIX environment with the PC Files Server to populate a Microsoft Access file with a large SAS data set, performance can be greatly affected.

The problem occurs because, by default, the DBCOMMIT= option in the LIBNAME statement is set to 1000, which means that a COMMIT (a permanent writing of the data to the DBMS) occurs after every every 1000 records are processed and written to the Access file. If the data set is very large, many COMMITs have to be performed, with each one taking longer than the previous one. As a result, performance becomes very poor.

The solution is to add DBCOMMIT=0 to the LIBNAME statement so that only a single COMMIT is executed after all of the data has been added. This will greatly reduce the amount of time required for the job to complete.

The LIBNAME statement will be similar to the following:

libname test pcfiles server=pc-files-server-host port=port-number path="c:\temp\test.mdb" dbcommit=0;

data test.ms-access-table-to-be-created;
   set sas_data_set;
run;

You can also use the DBCOMMIT= option in a data set option or as an option in the EXPORT procedure.

One caveat: Performance can also be adversely affected when you move large amounts of data across the network and the network is slow. This issue does not occur on faster networks (for example, those with 1 GBps speed), but it might be an issue on older and slower 10BaseT networks with only 10 MBps speed. Performance degradation caused be a slow network will probably not be as dramatic as the much slower performance caused by the COMMIT issue (minutes slower as opposed to hours slower), but it is still something to consider. It is important to understand that SAS has no control over this.

You can also further improve performance by using the INSERTBUFF= option in conjunction with DBCOMMIT=0 in your LIBNAME statement. The optimal setting for the INSERTBUFF= option is site-specific. You can keep increasing it from the default setting of 1 by incements of 100 until performance begins to level out. However, you need to be careful not to increase it so high that it taxes available memory, at which point performance will fall precipitously as it becomes necessary to swap out performance for memory.

Operating System and Release Information