Testing query performance in SAS

In our organization we had two SAS deployments, one is on premise and another is in AWS. We saw a slow performance of queries against the on premise database. We try to improve the performance by using several SAS options like READBUFF. But still we see there is a significant slower performance when compared to our on premise.

In one setup database is in on on-premise and SAS is in AWS, in another set up both SAS and Oracle database is in on premise. The issue reported is with the sas server in the AWS. We saw like for like query performing significantly slower than the on-premises server.

Below I am going to explain the process of testing which we took to find out the issue.

Testing method
I did my testing by running the query from Linux command line using the SQL plus utility. I created csv output using Oracle query and the sas query created the regular SAS dataset.

We run query against a table from our on premise database which had 5 million records and 200 plus column.

Full volume test
We used SAS to extract the full table and the READBUFF was set to be 100,000. The resulting data set was with the size of 9 gig and contains 5 million observation and 212 columns and observation length of 2000.

Results
We saw the queries from AWS environment are consistently slower than those from the on premise.
Increasing the buffer makes significant improvement in both the environment.

Oracle utility results
At worst case SAS AWS environment was 15 times slower than the on premise.
The worst result happened during that time were there are greater number of fetches.

SAS results
Tweaking the SAS options gave better performance than the default settings.
Even with similar settings AWS SAS services performed slow.


Script used:

On-Premises Server
Set Oracle Environment (setenv)

#!/bin/bash

export ORACLE_HOME=/app/oracle/product/11.2.0/client
export LD_LIBRARY_PATH=/app/oracle/product/11.2.0/client/lib:$LD_LIBRARY_PATH
export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
export PATH=$PATH:$ORACLE_HOME/bin



Test Oracle Script (test1.sql)

SET COLSEP ,
SET HEADSEP OFF
SET TRIMSPOOL ON
SET PAGESIZE 0
SET NUMWIDTH 12
SET LINESIZE 50
SET TERMOUT OFF;

SPOOL /sas/saswork/test1.csv;

SELECT POST_TOWN, NUM_CARDS, BALANCE, GENDER
  FROM DAILY_APPLICATION; 

SPOOL OFF
SET TERMOUT ON;
QUIT;
/

AWS Server

Set Oracle Environment (setenv)

#!/bin/bash
export ORACLE_HOME=/app/oracle/product/11.2.0/client_1
export LD_LIBRARY_PATH=/app/oracle/product/11.2.0/client_1/lib:$LD_LIBRARY_PATH
export TNS_ADMIN=/tvssas/app/ora
export PATH=$ORACLE_HOME/bin:$PATH
export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1


Test Oracle Script (test1.sql)
SET COLSEP ,
SET HEADSEP OFF
SET TRIMSPOOL ON
SET PAGESIZE 0
SET NUMWIDTH 12
SET LINESIZE 50
SET TERMOUT OFF;

SPOOL /saswork/ora/test1.csv;
SELECT POST_TOWN, NUM_CARDS, BALANCE, GENDER

  FROM DAILY_APPLICATION;
SPOOL OFF
SET TERMOUT ON;
QUIT;

/

Run Oracle Tests

Testing command line for SAS 9.2:
time sqlplus SAS_AMART/<<<password>>>@PMEGAMART.in.tvsmotor.com @test1.sql

Testing command line for SAS 9.4:
time sqlplus SAS_AMART/<<<password>>>@XMEGAMART.in.tvsmotor.com @test1.sql

The above will time the run, with the SQL statements in the file test1.sql.

Test SAS Script (test1.sas)
OPTIONS msglevel=i fullstimer;
OPTIONS sastrace=',,t,dsab' sastraceloc=saslog nostsuffix;

data timer;
   format tm1 time9.;
   tm1 = timepart( datetime() );
run;

 data test;
   set EDELWEISS.DAILY_APPLICATION( keep=POST_TOWN NUM_CARDS BALANCE GENDER
                                     readbuff=100000 );
run;

data _NULL_;
   set timer;
   format tm2 time9.;
   tm2 = timepart( datetime() );
   duration = tm2 - tm1;
   put duration=;
run;