SAS upload to Voyager/TRINO is very slow

[michal_1407]

Hi,

in my work I need to upload temporary table from SAS server to EAP server. Now we are using TRINO/Voyager and under SAS we can do it running the below codes, but it is very sloooooow.

Uploading 428 records from SAS (sashelp.cars) to EAP takes 13/24 minutes. I have experience with hadoop and under hadoop it takes 2-3 minutes. 

Do you know how can I speed up this step ? I tried to add “BULKLOAD=YES” option but no change. 

Best Regards

28        

29         libname libtest jdbc classpath='/opt/sas/JDBC3/'

30          class='io.trino.jdbc.TrinoDriver'

31          URL='jdbc:trino://ccc.aaa.bbb.net:8980/eap/aml?SSL=true&SSLVerification=CA&SSLTrustStorePath=/opt/sas/JDBC3/cacerts.jks&SSLTrustStorePassword=changeit'

32            user=&sysuserid.

33            password="&pass."

34         schema=my_schema

35        

36         ;

NOTE: Libref LIBTEST was successfully assigned as follows:

      Engine:        JDBC

37        

38         data libtest.sas_test_upl_presto2;

39         set sashelp.cars;

40         run;

NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.

NOTE: There were 428 observations read from the data set SASHELP.CARS.

NOTE: The data set LIBTEST.SAS_TEST_UPL_PRESTO2 has 428 observations and 15 variables.

NOTE: DATA statement used (Total process time):

      real time           13:08.07

      cpu time            0.35 seconds

[Patrick]

13 minutes for 428 rows with a few variables! Even taking some latency into account this is just "ridiculous". Don't even bother about looking into bulk loading for now.

I suggest you (your SAS admin) raise a track with SAS tech support to get to the bottom of this issue - even though it's likely not on the SAS side. 

[michal_1407]

yes, i agree with you. but know i try to find any additional SAS option to improve/solve this problem but without any success : /

[SASKiwi]

Try adding the INSERTBUFF option to your database LIBNAME. I normally set this to at least 10,000 rows as default settings are normally way too slow. You can try different values to see what gives best performance. DBCOMMIT is another option worth trying.

[michal_1407]

I added this option but the execution time was similar, without any significant change : /

[andreas_lds]

Maybe i am wrong, but

---

@michal_1407 wrote:

...

      real time           13:08.07

      cpu time            0.35 seconds

---

means, that sas needed 0.35 seconds to its job, but had to wait 13 minutes all in all so that other processes can complete their part of the task.  With the tiny dataset used, i would exclude the network connection as possible bottleneck. Which SAS version is used? Older version used older version of Java, so maybe there is a problem in with the jdbc driver combined with a elderly Java version - just guessing.

[michal_1407]

we use SAS server. Yes, real time takes 13 minutes because using my code each row is inserted to EAP by TRINO row by row. So if I have 428 rows there will be 428 insert commands to EAP by TRINO and it is stupid because i dont know how to force SAS to do it in one INSERT.  I use JDBC driver to connect. 

AUTOMATIC SYSVER 9.4
AUTOMATIC SYSVLONG 9.04.01M7P080620
AUTOMATIC SYSVLONG4 9.04.01M7P08062020

libname libtest jdbc classpath='/opt/sas/JDBC3/'
class='io.trino.jdbc.TrinoDriver'

[Patrick]

---

@michal_1407 wrote:

we use SAS server. Yes, real time takes 13 minutes because using my code each row is inserted to EAP by TRINO row by row. So if I have 428 rows there will be 428 insert commands to EAP by TRINO and it is stupid because i dont know how to force SAS to do it in one INSERT.  I use JDBC driver to connect. 

AUTOMATIC SYSVER 9.4
AUTOMATIC SYSVLONG 9.04.01M7P080620
AUTOMATIC SYSVLONG4 9.04.01M7P08062020

libname libtest jdbc classpath='/opt/sas/JDBC3/'
class='io.trino.jdbc.TrinoDriver'

---

The two libname options which allow you to insert more than one row at a time are dbcommit (set: dbcommit=0) and insertbuff

...but... for 428 rows also a insert/commit per row should take much much less than 13 minutes - unless this creates for your case every time a new connection with some latency. 

[AhmedAl_Attar]

Hi @michal_1407 

I would suggest

• Using the SASTRACE option to see what's going on while communicating with the Database

options sastrace=',,,d' sastraceloc=saslog nostsuffix; 

• Reading this paper Accessing Your Favorite Database with SAS® and JDBC to see if it offers any tips.
• Read the SAS/ACCESS Interface to JDBC Help Docs (Make sure you select the relevant SAS release docs) 
  • https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/acreldb/n0zm6fjwtgsnrzn1fegvyhl3yrwd.htm
  • https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/acreldb/p1chghbbeu5k4sn1jh2jax39cgse.htm

Hope this helps,

Ahmed